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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy