SQL Server Performance Forum – Threads Archive
locking/blocking – sql server 2000
i inherited a stored procedure that has aboout 15-20 separate insert/update/deletes statements in it. does sql server hold locks on each resource until the sp finishes?or
does it release locks as it completes each insert, etc as it goes along in the sp.
Check it yourself by running or run PROFILER during the process and check whether any blocking persists. When SQL Server 2000 cannot grant a lock to a transaction on a resource because another transaction already owns a conflicting lock on that resource, the first transaction becomes blocked waiting on that resource. If this causes a deadlock, SQL Server terminates one of the participating transactions (with no time-out involved). If there is no deadlock, the transaction requesting the lock is blocked until the other transaction releases the lock. By default, there is no mandatory time-out period, and no way to test if a resource is locked before locking it, except to attempt to access the data (and potentially get blocked indefinitely). Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
It depends. Sql server holds locks unitill transaction is completed. If you don’t have explicit begin transaction each insert/update/delete is separate transaction so locks will be held on resources involved until statement completes. If there is explicit transaction locks are held on all resources involved in all queries between begin transaction and commit/rollback. It means that concurent updates on the same rows (could be even pages or tables depending on lock escalation) will have to wait untill this transaction commits or rollbacks. If higher isolation level is used (or locks are escalated to table level) even inserts will have to wait.
]]>