Table locks

Question

Table locks

zulmanclock

SSC Eights!

Points: 968
More actions
April 29, 2014 at 1:47 pm

#286327

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?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

zulmanclock SSC Eights! Points: 968 More actions · Answer 2

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

zulmanclock SSC Eights! Points: 968 More actions · Answer 4

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass