I am trying to create unique document numbers for my transactions. I have an order that has an order segement and a segement number. The document is in the following form: "segment number/segment".
I have a deadlock issue that I can not seem to get around when there are multiple users who need to generate a document numnber. Here is a high level design of what happens:
stored procedure 1 For 25 transactions exec spBuildDocNumber spBuildDocNumber BEGIN TRANSACTION with UPDLock and RowLock, get the sequence number. set @docNumber = order segment + '/' + the sequence number. COMMIT TRANSACTION return @docNumber stored procedure 1 continued... UPDATE TableA Set DocumentNumber = @docNumber WHERE OrderNo = @OrderNumber
The last update statement is where I am getting the deadlock. I do this by running 3 instances of query analyzer for 3 orders that need 25 document numbers. Why is the deadlock happening since I am using a row lock?
This was first published in August 2004