Correct Code for Locking

[EriRobert]

Greeting Everyone

I just want to confirm that my code for locking is sound. I thought it was, but after a couple of multi-user related problems I am now not so sure.

I leave most locking issues to SQL itself, but have a table containing next transaction numbers, where I recognise that locking hint is required. After taking the next transaction number I increment it. My current coding is as below:


-----------------------------------
BEGIN TRANSACTION

SET @intTID = (SELECT N_Number
FROM tblNextNumbers With (UPDLOCK)
WHERE N_Reference = 1)

UPDATE tblNextNumbers
SET N_Number = N_Number+1
WHERE N_Reference=1

... more updates using @intTID

COMMIT TRANSACTION

-----------------------------------

I thought this code was OK, but 2 users have been assigned the same number in recent times.

Where is my problem?

Thanks in advance.

Robert

 

[NoCoolHandle]

An update lock doesn't prevent read, only modification..

Not sure if this is your problem, but....

You might want to try going to an Exclusive lock instead.

 

[zhavic]

You must lock the record before you get the value from it:

BEGIN TRANSACTION

/* this locks the record */
UPDATE tblNextNumbers
SET N_Number = N_Number -- don't change the value
WHERE N_Reference=1

/* get the number from locked record */
SET @intTID = (SELECT N_Number
FROM tblNextNumbers
WHERE N_Reference = 1)

/* store new number */
UPDATE tblNextNumbers
SET N_Number = N_Number+1
WHERE N_Reference=1

... more updates using @intTID

COMMIT TRANSACTION


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[NoCoolHandle]

Zhavic,

Not true, when you specify a locking option it will lock the record on the select using the locking strategy specified in the optimizer hint DBCC TRACEON (1200,3604) will show this... However -- the lock will be released as soon as the statement that used it is finished.

As an UpdateLock, it doesn't stop 2 people from seeing the same record and updateing it to the new value..

2 possible solutions
1. set transaction isolation level
2. use an exclusive lock in the optimizer hint and prevent the second read..

e.g.

Option 1
BEGIN TRANSACTION
[red]set transaction level serializable[/red]
SET @intTID = (SELECT N_Number
FROM tblNextNumbers With (UPDLOCK)
WHERE N_Reference = 1)

UPDATE tblNextNumbers
SET N_Number = N_Number+1
WHERE N_Reference=1

... more updates using @intTID

Or OPTION 2

BEGIN TRANSACTION

SET @intTID = (SELECT N_Number
FROM tblNextNumbers With [red](XLOCK)[/red]
WHERE N_Reference = 1)

UPDATE tblNextNumbers
SET N_Number = N_Number+1
WHERE N_Reference=1

... more updates using @intTID


I would tend to go with the second option as the first will keep the record locked until the entire transaction completes, and will still not prevent another connection seeing the old value, just stop it from changing it once read..

My 2c

Rob

 

[svetlana]

to my knowledge the following code should not cause conflicts in multi-user environment

UPDATE tblNextNumbers SET @intTID = N_Number = N_Number+ 1
WHERE N_Reference = 1

 

[EriRobert]

Thanks for these answers so you seem to say that my original code:

BEGIN TRANSACTION

SET @intTID = (SELECT N_Number
FROM tblNextNumbers With (UPDLOCK)
WHERE N_Reference = 1)

UPDATE tblNextNumbers
SET N_Number = N_Number+1
WHERE N_Reference=1

... more updates using @intTID

COMMIT TRANSACTION

is OK. But what happens if the Select fails to get a update (or exclusive) lock (because it is in use). Does it wait or does it fail? If it waits OK, if it fails then I have a problem and must check and loop on some failure status.