November 2, 2014 at 1:34 pm
A co-worker ran this script:
Declare @numupdated int = 1;
While @numupdated > 0
BEGIN
UPDATE TOP(10) MyTable ...;
SET @numupdated = @@ROWCOUNT;
WAITFOR DELAY ’00:00:01’
END
It ended properly when it got done. But...
It left an IX lock on MyTable, I can see it in the sys.dm_tran_locks. It resulted in other sessions to get stuck in locking waits.
Why wasn't the lock released at the end of the loop?
November 2, 2014 at 2:35 pm
Did he start a transaction before running that and not commit it?
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
November 2, 2014 at 2:38 pm
I don't think so. He ran it in SSMS.
November 2, 2014 at 2:57 pm
Running it in SSMS doesn't mean he didn't use a transaction. Check for open transactions.
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
November 3, 2014 at 12:39 am
I killed that session as soon as I realized it was the culprit in holding other sessions up, so I can't tell now if there was a transaction open. But the co-worker said he indeed was playing around with transactions at one point, so there is a chance there was a transaction open.
November 3, 2014 at 9:59 am
Open transaction would be the reason the locks were held. locks taken by data modifications are held until the transaction completes
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
November 3, 2014 at 11:43 am
Yes, I confirmed that this was the case, thank you!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply