2

I am using SQL Server 2008 R2 Express with VS2010 ultimate c#. It's a client server architecture software of registering patients. there are 4 clients where 4 users are registering the patients. When 2 users are registering 2 separate patients and they hit the save button, then I am doing the following steps,

  1. system pulls the next registration no.
  2. saves the record with a that registration no. into SQL Server table
  3. again pulls the next registration no. for the next patient

If both users press the save button exactly at the same time, then SQL Server should put them in a queue regardless of the face that both users have hit the save button at the same time, SQL Server should execute the insert command of first patient, then generate next number and second insert command should execute just next to the first. so that the second query can pull the next registration no and then save the record. but it is saving two patient with the same registration no. I have even used BeginTransaction, Commit and Rollback as well but still I am in a fix. Any help will be appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2831374
  • 21
  • 1
  • Can you add your registration number generator code and the insert ? – sino Sep 30 '13 at 14:10
  • 3
    Use an Identity field, or GUIDs on the clients if you need a more extreme solution. – Chris Sep 30 '13 at 14:10
  • 3
    I agree with @Chris - anything you roll yourself will not work under concurrent loads like this. Use an Identity column - stop trying to re-invent the wheel! – Bridge Sep 30 '13 at 14:12
  • i am using a field in a separate table with the name RegNo and ByDefault i stored 1 in this. when i save the record i read this field and increment it by 1 and save the record and update this field as well. – user2831374 Sep 30 '13 at 14:14
  • Purpose of using a separate field in a separate table and not using Identity column is that i cannot reset the identity to 1 after a financial year gets to an end so that is why i am using a field with the name RegNo in Patient Registration Table and i have created a separate table with the name system and field RegNo. System table contains only 1 field i.e. RegNo and i update it after saving each record by giving it increment by 1 – user2831374 Sep 30 '13 at 14:18
  • If you reset the number to 1 at the financial year end, aren't you going to get a bunch of duplicate key on insert errors? Won;t any records you insert clash with ones you inserted in the previous financial year? – DaveH Sep 30 '13 at 14:21
  • When you say ' then SQL Server should put them in a queue ' do you mean the application has been designed for this to be the case, or is that your expectation of how databases should work? If it's the latter then I'm afraid your assumption is wrong. – Rikalous Sep 30 '13 at 14:24
  • See [Remus Rusanu's answer here](http://stackoverflow.com/questions/5083846/sql-server-2005-using-generated-sequences-instead-of-identity-columns) that outlines exactly **how** you must proceed to properly and safely handle "manual" sequences. – marc_s Sep 30 '13 at 14:39

1 Answers1

3

Isn't the problem that you are reading the next registration number in two separate threads of execution. I don't know much about sql server, but it appears that the read does not apply any form of lock, so the two threads both get the same value.

One approach is to do the following

  • start a transaction
  • update the next registration number ( by adding 1 to it )
  • read back the value
  • use it in the insert
  • commit

The initial update locks the row that contains the next registration number, stopping any other thread from reading it. The "other" thread will wait for the lock to be released ( forming the queue you mention in your post ), and then continue, allocating the next registration number when it does its insert.

DaveH
  • 7,187
  • 5
  • 32
  • 53
  • I agree with this answer, this topic is also discussed in length here: http://stackoverflow.com/questions/1683829/sql-server-race-condition-question – Damon Sep 30 '13 at 14:20
  • Thanks dave. i am using the same flow that you have mentioned in your post. i start transaction, select the RegNo field, increment it by 1, save the patient record, then commit it. but still it duplicates the number. i have no idea why is it doing so. actually it shouldn't – user2831374 Sep 30 '13 at 14:23
  • Don't read it and then update it - the read does not lock the row. Update it first, then read back the value. The update locks the row, meaning that the "other" thread can't read it until the transaction is committed. – DaveH Sep 30 '13 at 14:25
  • ok let me try this one, i will update it to the next no. then i will read it. just make me sure one more thing, if 2 users do that simultaneously, then sql will queue them for sure. is that so? – user2831374 Sep 30 '13 at 14:36
  • if two users do it simultaneously, one of them will acquire the lock, the other will wait for the lock to be released. It's not usually referred to as a queue, but that is effectively what is happening – DaveH Sep 30 '13 at 14:41
  • i really appreciate your assistance Dave. GOD BLESS YOU MY SON ;p – user2831374 Sep 30 '13 at 14:54