March 7, 2014 at 11:29 am
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.
March 7, 2014 at 11:53 am
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
March 7, 2014 at 12:07 pm
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.
March 7, 2014 at 12:16 pm
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.
March 7, 2014 at 12:16 pm
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
March 7, 2014 at 12:19 pm
March 7, 2014 at 12:28 pm
suneel kamavaram (3/7/2014)
This may help
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
March 7, 2014 at 12:36 pm
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.
March 7, 2014 at 12:47 pm
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
March 7, 2014 at 1:27 pm
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.
March 7, 2014 at 1:38 pm
Did you check what was the Lock Mode and the type of lock acquired (granularity) ?
--
SQLBuddy
March 7, 2014 at 1:48 pm
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...
March 7, 2014 at 2:18 pm
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
March 8, 2014 at 2:08 pm
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
March 8, 2014 at 11:52 pm
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply