Ask the Expert

Deadlock on creating unique document numbers

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?

    Requires Free Membership to View

The deadlock is happening because the processes are all going after the same piece of data. This is a code construct that should also be removed immediately from your code. It is a construct that used to be used 7+ years ago, but it has been proven thousands of times to not scale at all, let alone having the deadlock issues you are seeing. Look up "identity" in Books Online. This is all that you need to accomplish what you are after with zero deadlock issues, no contention, and perfect scalability.

This was first published in August 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: