April 29, 2014 at 1:47 pm
Hello all i have a question regarding SQL locks. If you have a stored proc which does something similar to below:
Update Table A
Where field1 = bla
And later in the proc it does
Delete Table B
Where field1 = bla
am i correct in my assumption that the locks acquired on those tables is not release until the proc completes, so if another proc is called that is attempting to update/delete/insert into either of those tables they could encounter a block or even a deadlock?
April 29, 2014 at 2:25 pm
Nothing to do with a procedure.
Locks taken by data modifications are released when the transaction finishes (commit/rollback). If you aren't using an explicit transaction, that means the locks are released at the end of the statement.
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
April 29, 2014 at 2:35 pm
GilaMonster (4/29/2014)
Nothing to do with a procedure.Locks taken by data modifications are released when the transaction finishes (commit/rollback). If you aren't using an explicit transaction, that means the locks are released at the end of the statement.
So if the data modification statement is not wrapped inside a BEGIN TRANSACTION the modification, INSERT/UPDATE/DELETE is committed once the last record effected by it is modified?
April 29, 2014 at 2:38 pm
Once the statement completes and any triggers on the table have fired and have completed.
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
April 29, 2014 at 2:40 pm
GilaMonster (4/29/2014)
Once the statement completes and any triggers on the table have fired and have completed.
Thank you very much for your answer. Now this places me back at the start of my why are we getting so many deadlocks riddle lol. Was thinking had to do with the way the proc was written. we have one proc that does a series of calls to other delete proc and this main one seems to always be a victim in deadlocks and i have been tasked in attempting to resolve this...fun times.
April 29, 2014 at 2:46 pm
Shameless self-promotion: https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply