June 6, 2016 at 4:01 am
Morning All,
Hope you're all well and had good weekends.
Quick question regards locking, particularly surrounding a delete.
If a delete statement has a complex where clause; and say that where clause takes about 3-4 minutes to return. And say the delete itself takes 1 minute to perform.
Does a lock blocking lock exist for 5 minutes, or just for the duration of the delete (1 minute) itself.
take this snippet:
delete from tab1
where x=y
and a=b
and assume the where clauses are big.
I am trying to shorten the blocking on this table, and don't know if attacking the where the clause is going to help - its specifically deletes on this table that cause blocking...
Also, the query is parameterized so i can't recreate the where clause accurately 🙁
Cheers
Alex
June 6, 2016 at 4:40 am
Delete uses exclusive locks. Definitely tuning your where clause will help.
The dynamic SQL may give you a tough time though.
June 6, 2016 at 5:12 am
Cheers Roshan;
So the locking DOES start from the beginning of the search? Or only after it has located the rows?
June 6, 2016 at 5:54 am
You can try it for yourself. Set up an extended events session, capturing the lock_acquired event, then run your delete statement. You can then inspect the captured events to see what locks were taken. Make sure you either do this on an instance of SQL Server that's not doing anything else or put a filter into your EE session so that you don't have to wade through lots of irrelevant data.
John
June 6, 2016 at 6:15 am
alex.sqldba (6/6/2016)
So the locking DOES start from the beginning of the search? Or only after it has located the rows?
A session must have a lock to access a row. So to locate the rows it needs to have locked them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply