January 21, 2014 at 1:20 pm
what kind of locks hold inside a transaction for a select statement
begin tran
select 1 from tableX -- retrun only one value table have one record
insert/delete /update other tables
commit tran
in my application i have noticed a executive lock until the transaction is completed. the lock held on tablex until entire transaction is completed which is causing blocking other sessions to acquire access on tableX.
so if i do Data declarations and initializations before transaction will that help .
January 21, 2014 at 3:40 pm
In the default isolation level shared locks (from selects) are held no longer than the end of the statement. Exclusive locks (from data modifications) are held until the end of the transaction
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
January 21, 2014 at 3:44 pm
Thanks Gail ! but in my case just i am selecting a records(select statement) which is holding an intent exclusive lock until the end of the transaction
January 21, 2014 at 4:11 pm
Selects do not take exclusive locks unless there's a locking hint and they do not hold locks until the end of the transaction unless there's a locking hint.
Sure it's not a trigger, check constraint or foreign key being checked/run?
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply