1

I am working on e-commerce application, I have three tables in DB

  1. Customer
  2. Order
  3. Order Details

Order Details information about individual products and OrderId that is primary key of Order and I have 2 Stored Procedures

  1. InsertIntoOrderAndCustomer
  2. InsertIntoOrderDetails

as InsertIntoOrderAndCustomer is called from ADO.Net it will generate autoid OrderId in Order and I need to use that id in as a parameter to InsertIntoOrderDetails but I am worried if multiple orders are being placed then parameter value may be wrong. Can I lock C# code so that only one order will be placed at a time? If yes, then how?

I am worried about

2 Persons placing order

Execution could be,

Person A places order

  1. Insert data into Orders table for Person A - It will generate id automatically IDENTITY (of course only once).

Mean while Person B places order then

  1. Insert data into Orders table for Person B - It will generate id automatically IDENTITY (different one).

then insert data into product details table with AutoId IDENTITY in Orders table.

While inserting into product details I am getting auto id using MAX of SQL Server and this will give me 101, as in above scenario.

Is there possibility that second insert put IDENTITY of Order of Person B (i.e 101) and other data of Person A ? it worries me.

Imad
  • 7,126
  • 12
  • 55
  • 112
  • 1
    Why would the parameter value be wrong? – Marc Gravell Jun 13 '14 at 07:19
  • You can use a `lock` in C# like explained [here](http://stackoverflow.com/q/6029804/2132796). However, you should reconsider doing this. As @MarcGravell mentioned, why would it be wrong? – Stefan Over Jun 13 '14 at 07:26
  • If 2 persons are placing order, and 2 sp are being called for both orders. Person A put the request and suppose its auto generated OrderId 100, at the same time person B put another and its OrderId is 101. To call second SP, OrderId of `A' can be passed in `B`'s second sp because of I/O waiting and all, I am not sure, can it be? I am just worried of future bugs, so... – Imad Jun 13 '14 at 07:26
  • 1
    @Herdo `lock` is usually useless when talking about databases, as they are *usually* accessed by multiple proceses – Marc Gravell Jun 13 '14 at 07:27
  • @SMI why would it auto-generate OrderId 100 twice at the same time? What "autoid" are you using here? If this is SQL Server `IDENTITY`, it will never generate the same id twice. Frankly, unless you are rolling your own (and buggy) auto-id, it should work fine. So: how is your "autoid" working here? – Marc Gravell Jun 13 '14 at 07:28
  • @MarcGravell I know. But he asked for a C# lock. And as mentioned, he should reconsider doing so. – Stefan Over Jun 13 '14 at 07:28
  • @Herdo which to me would mean "lock the table"; frankly, a regular sql transaction should work fine here even if doing everything manually, if the isolation level is high enough (serializable would take x-range locks; might need some UPDLOCK to avoid deadlock, though). But frankly the real "fix" here is: use the database's inbuilt automatic id generation. – Marc Gravell Jun 13 '14 at 07:30
  • @SMI what is the database here, and how are you implementing this auto-id? – Marc Gravell Jun 13 '14 at 08:04
  • May be my poor explanation not helping me to clarify, yet I made last attempt and edited my question. – Imad Jun 13 '14 at 09:02

1 Answers1

1

I think the potential for mixup is coming from "I am getting auto id using MAX of Sql Server". If you are calling both stored procedures from C# code and are calling them independently, and the second sproc (InsertIntoOrderDetails) relies on MAX to get the last auto-generated id from Orders, then yes, this is subject to getting the Id from user A or B mixed up in the OrderDetails table.

I can suggest three approaches. There might be others:

  1. Return the auto-generated id from Orders as a Return or Output parameter of InsertIntoOrderAndCustomer. using scope_identity() Use it as an input parameter when calling the second sproc InsertIntoOrderDetails. The c# code should be aware of which customer and orders details are in context.

  2. Have the InsertIntoOrderAndCustomer sproc capture the auto-generated id (using scope_identity and then also call the InsertIntoOrderDetails directly itself.

  3. I don't recommend this, but since you asked. Your c# code could use a EventWaitHandle to allow the procedure code to be called only once at a time. This is not scalable, and it also does not prohibit mixup using your existing approach if the procedures are called outside of the c# world.

mdisibio
  • 3,148
  • 31
  • 47