Perform an update but skip lock

Question

Perform an update but skip lock

laser_steve

SSC-Addicted

Points: 481
More actions
March 7, 2014 at 11:29 am

#283078

I've tried all sorts of ways to make this happen but I'm completely stumped. Hours pass I'm getting nowhere can anyone help!
I open query analyser and on one tab I update a record in a transaction and hold it.
begin tran
update customers set territory = 'x' where customer = 'A00001'
--rollback tran
In a second tab I attempt to update all records in the table
update customers set carrier = ''
Clear this fails because of the lock placed during the first script and this is fine.
However, is there a way to get the 2nd script to ignore the locked rows and just update as many as it can?
The obvious answer seemed to be the READPAST hint like follows…
update customers with (READPAST) set carrier = ''
…but this is still blocked by the original lock. I’ve tried combining it with all sorts of other table hints but all seem to get blocked.
The following does work, ignoring the lock and not returning the data
Select * from customers with (READPAST) where customer = 'A00001'
I’ve tried combining this with the update like so…
update customers
set carrier = ''
from customers with (READPAST)
where customer = 'A00001'
..but this is blocked too.
I’m so desperate I tried moving the update into a cursor and update one row at a time. Nothing worked. I thought I might be able to do something like this
If (Select count(*)
from customers with (READPAST)
where customer = 'A00001') > 0
--then perform update
..but this returns a value of 1 even though the following returns no rows.
Select * from customers with (READPAST) where customer = 'A00001'
Please, please, please, can anyone help? I thought I was pretty good with sql, starting to think not.
Of course the original update lock, held for ages, should never happen. But I’m working with an ERP system and I cannot control this lock.

Viewing 15 posts - 1 through 15 (of 16 total)

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

When SQL Server takes an exclusive lock, which it must have in order to maintain data integrity when modifying data, there is no way for any other process to get through that lock. Running any other type of update that would need to get at that lock is going to be blocked until that lock clears. There's no way around this at all.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

laser_steve SSC-Addicted Points: 481 More actions · Answer 2

My question is how to skip past the locked record. Not how to update it. I know you cannot update it but I don't the whole process stopped because of one locked record.

If the table has 100 rows and 1 is locked I want to update the other 99.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

First thing you may want to do is to look at the locks that are being taken in your testing. That may help you to answer your question.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

The only way you can do that is to somehow filter that out of your UPDATE query using the WHERE clause. There's nothing in the system that lets you update everything except the stuff that's currently locked.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SumLasty SSCommitted Points: 1923 More actions · Answer 5

This may help

http://www.sqlnotes.info/2012/10/10/update-with-updlock/

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

suneel kamavaram (3/7/2014)
This may help
http://www.sqlnotes.info/2012/10/10/update-with-updlock/

So, correct me where I'm wrong, but doesn't that just address deadlocks? The situation we're in here is not deadlocks, but blocks. Totally different. Unless I'm missing something.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

laser_steve SSC-Addicted Points: 481 More actions · Answer 7

You are correct, there is no deadlocking to worry about.

I just need a way to check for locks BEFORE updating.

It sounds really simple but I'm lost.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 8

Well, it's not really simple. The issue is, you shouldn't be seeing locks held like that. It means the system is improperly put together. Not helpful I realize, but, there it is. The issue is even deeper than you know. You're pretty clearly seeing a row lock, but what happens when a page lock is taken out for the update. That's 8K worth of rows (or values in an index key) that you won't be able to access either. Trying to identify this stuff ahead of trying to write an UPDATE statement in order to avoid them is going to be very problematic.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

laser_steve SSC-Addicted Points: 481 More actions · Answer 9

Thanks all for the replies. I know the system performing the locks is bad, why they couldn't have gone for optimistic locking I'll never know. But I cannot control that element, only my own code.

I are correct of course. I expect SQL to control the locks and this may end up locking more than just one record. But even then I would have been fine if I could have just updated the unlocked ones.

It's a shame because the readpast hint sounded so promising, but doesn't seem to 'readpast' with updates.

I've been using SQL for over 10 years and I can't believe I've not come across this before. Not sure what to do now. The forums were my last hope.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 10

Did you check what was the Lock Mode and the type of lock acquired (granularity) ?

--

SQLBuddy

laser_steve SSC-Addicted Points: 481 More actions · Answer 11

OK, I have a 'solution' of sorts. It's really bad though.

Basically I'm looping through the records in a cursor, so it starts bad... and gets worse.

I've set the following

SET XACT_ABORT OFF

SET LOCK_TIMEOUT 250

I've put the update into a try catch statement so basically when a perform an update if the record is locked we skip to the catch and then move onto the next record in the cursor. The XACT_ABOUT stops the whole thing failing.

It's not nice but it appears to work!

Let the abuse begin...

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 12

Yeah, Got it. Also could you check the Lock Mode (Update or Exclusive) and the type of lock acquired (granularity) ( RID,Page or Table) and the Isolation Level ?

--

SQLBuddy

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 13

This is easy to do: upgrade to SQL Server 2014 and use a "Hekaton" in-memory table. 🙂 No locks/latches are taken there. I note that you need to code your application to REALLY understand all that comes along with that though!! :w00t:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 14

laser_steve (3/7/2014)
Thanks all for the replies. I know the system performing the locks is bad, why they couldn't have gone for optimistic locking I'll never know. But I cannot control that element, only my own code.
I are correct of course. I expect SQL to control the locks and this may end up locking more than just one record. But even then I would have been fine if I could have just updated the unlocked ones.
It's a shame because the readpast hint sounded so promising, but doesn't seem to 'readpast' with updates.
I've been using SQL for over 10 years and I can't believe I've not come across this before. Not sure what to do now. The forums were my last hope.

I don't trust optimistic locking. It's a good way to lose data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)