RE: [coldbox:11947] SQL DeadLock

So if I understand that then, if I apply what you are suggesting then it will not update until select has finished.

I’m not sure where you’re going with that, but yes that is true by virtue of the fact that SQL Server always processes one statement at a time.

Would the select and update happen in one transaction?

You control that, but yes you will want to wrap the select AND the update in the same transaction. If they were on their own, that would defeat the purpose of the update lock since it is held until the end of the transaction.

I am worried that just before the update another request comes in and does a select, then I am right back to square one. But I am assuming that from what you are saying this will fix that issue.

Correct. And to be clear, an update lock will not block another shared lock. i.e., another process will be able to select those records from the table with a normal select statement, but no other process will be able to obtain another update lock on them. Obviously, the onus is on you to use an update lock everywhere you select these records AND also intent to immediately update them.

Also, for what it’s worth, this is a little off-topic but I was slightly off earlier when I said range locks are only used if you use the <, >, or BETWEEN operators. SELECT col FROM table WHERE col = ‘mary’ would create two range locks if performed with a serializable isolation level. Assuming col has a non-unique index, there would be a range lock on the record with a value of ‘mary’ as well as the record holding the “next” value (Such as “Nancy”). That being said, I guess there can be a valid reason for you using serializable if you don’t want any other records inserted from a second process that would match the same where clause you are selecting with.

~Brad