Row Locking Issue

Question

Row Locking Issue

hefterr-972543

SSC Journeyman

Points: 91
More actions
October 6, 2009 at 3:42 pm

#139233

Hi,
I need to read a 1 row control table to get the next account number to assign (MS SQL Server 2005). I then will insert a row to a Customer table using the next available account number.
The problem is if 2 threads/transactions read the Control table at the same time. The 1st to do the insert will be OK but the next will fail. I'm not sure how locking works when you definie a TRANSACTION in a SP
I found 2 solutions
1) T-SQL Update Intent :
It seems you can read a row with update intent :
SELECT * from CONTROLTBL (UPDLOCK)
--
Insert your row
--
UPDATE CONTROLTBL
SET Counter = counter +1
2) There is T-SQL to set your "isolation level" but I'm not sure if this does the trick?
Any suggestions?
Thanks in advance,
hefterr

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

Instead of doing a SELECT and then an UPDATE, do an UPDATE with an OUTPUT clause so that everything happens in one statement.

George M Parker SSCertifiable Points: 5412 More actions · Answer 2

It sounds like your control table is a bottleneck in the application design as each connection has to wait for the previous select and update to complete before it can get the next value. I belive you have a few options to deal with this and I've implemented all three of them at one point or another.

One would be to set the transaction isolation level to serializable. That will serialize all access to the 1 row in the table and cause any other connections to be blocked until the first connection completes the select to get the current value and the update to set it to the next value. If you have a large number of connections though, you'll experience significant blocking because of this as the connections wait for the preceeding connections to clear. And if the code to handle the select and the update is mixed with code to do other DML operations, the blocking will likely be severe.

Another option is to perform the select and update to the control table in a separate transaction and commit that transaction as soon as possible without waiting for any other DML activity that needs to be handled in a transaction. If you can keep the transactions that maintain the "counter" as small and fast as possible, that will minimize the amount of blocking. You run the risk here though of other transactions failing once the account number range has been incremented and you'd need to deal with that in the code.

The last one is to redesign the process that assigns the next account number. I've seen it where the application reads a range of next available values and assigns the next number in the sequence based on that range. When it runs out of values, it then re-queries the control table to get the next available range of numbers to assign.

hefterr-972543 SSC Journeyman Points: 91 More actions · Answer 3

Hi Michael,

I'm not familiar with an

"..do an UPDATE with an OUTPUT clause"

Can you provide a simple example?

Thanks again,

hefterr

hefterr-972543 SSC Journeyman Points: 91 More actions · Answer 4

Hi George,

Thanks for your reply.

There will not be a heavy volume of new accounts. But in any case, I was not sure what you meant by

".. You run the risk here though of other transactions failing once the account number range has been incremented and you'd need to deal with that in the code."

Could you clearify. It sounds like the core of my original problem?

Thanks again,

hefterr

George M Parker SSCertifiable Points: 5412 More actions · Answer 5

If you place the select and update from the control table in a separate transaction (let's say it returns 1001 and updates the next value to 1002) and commit that transaction, then you attempt to insert that 1001 into the accounts table in a separate transaction and that fails for some reason, you could end up with a missing account number of 1001. In this case, the insert of 1001 into the accounts table failed and someone else got the next account number in line 1002.

If the volume of new accounts being created is low, I would likely try the "isolation level serializable" option. But I'd really need to know more about what's included in the transaction that updates the control table first.

hefterr-972543 SSC Journeyman Points: 91 More actions · Answer 6

Hi George,

Thanks for the clarification.

Gaps in the account numbers will not be a problem.

In short, all I am trying to do is generate a unique account number for new costomers. In general an acending sequential number would be best. Any "gaps" would not be a problem. I am shying away from using an IDENTITY column for the primary Account table as there are support issues with dealing with IDENTITY columns. So I'm using the "control table" approach (my own identity generator). I'm just having trouble ensuring single threading through the assignment of the new number.

That's all their is to it. I've worked with DB2 before and you can "Select for Update" so DB2 locks the rows/pages selected. I'm not familar with SQL Server locking. I did read about a "table hint" that does the same thing (I think):

SELECT nextkey from CONTROLTBL (UPDLOCK)

where key = 1;

Thanks,

hefterr

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

hefterr-972543 (10/6/2009)
Hi Michael,
I'm not familiar with an
"..do an UPDATE with an OUTPUT clause"
Can you provide a simple example?
Thanks again,
hefterr

DECLARE @Ctr table( Counter int NOT NULL)

UPDATE CONTROLTBL

SET

Counter = Counter +1

OUTPUT

INSERTED.Counter

INTO

@Ctr

-- Return the new Counter

SELECT Counter FROM @Ctr