June 9, 2014 at 1:28 pm
Hello All,
I had a need to execute a query. As a standard practice, I issued a BEGIN TRAN statement prior to that. I then issued my update query. The process was taking an abnormally long time. I was told that only 4500 records were to be updated.
15 mins later, the update statement was still running. I then clicked on the red stop button on the management studio, it took another another 15 or so mins before the process completed. At that point, I typed in ROLLBACK
Question: Based on what I did, were there any updates that actually happened ? In other words, if you stop the query mid-point, do updates still occur, or do they rollback on their own
Thank you !!
June 9, 2014 at 2:18 pm
Note: My understanding of this may be off in some places; if anyone has corrections for the following, please supply them! 🙂
From what I understand, when you hit the Stop button in SSMS, it halts the execution of the query wherever it was at the time, and rolls back the modifications done in that segment of it.
So, if you had a decently-complex UPDATE statement, and that was the only thing in the query window, it would've stopped running the UPDATE and then rolled back all of the work it did.
However, since you started the process with a BEGIN TRAN with no COMMIT or ROLLBACK, that left your transaction hanging. Issuing a COMMIT would have committed any changes that made it through the SSMS Stop, while issuing a ROLLBACK (which you did) would have rolled back any changes for the whole thing.
I'd suggest not leaving a dangling transaction, as such; there's quite a few pitfalls that could result from doing so, with forgetting to COMMIT or ROLLBACK being a pretty big one, since whatever modifications you made won't be done, and (I believe) the transaction will sit and hold locks on what it's attempting to modify until it's explicitly finished or killed.
EDIT: Incorrect on my part! Tested it just now; issuing BEGIN TRAN and then not doing a COMMIT or ROLLBACK won't hold locks. Tested by creating a simple table, issuing a BEGIN TRAN UPDATE on one row with nothing else, and trying to SELECT the modified row in a separate query window. The change hadn't gone through, and I was allowed to complete the SELECT.
EDIT2: Ignore me, I'm a doofus. As Gail states below, the hanging transaction will definitely hold locks on everything it was trying to modify; I just wasn't seeing it because I was using a database with READ COMMITTED SNAPSHOT enabled.
- 😀
June 9, 2014 at 2:38 pm
Andrew Kernodle (6/9/2014)
EDIT: Incorrect on my part! Tested it just now; issuing BEGIN TRAN and then not doing a COMMIT or ROLLBACK won't hold locks.
It will hold locks. Any locks taken by a data modification are held until the transaction is committed or rolled back.
Window 1:
USE tempdb
go
CREATE TABLE Test (
SomeCol INT
)
INSERT INTO Test VALUES (1)
GO
BEGIN TRANSACTION
UPDATE Test SET SomeCol = 2 WHERE SomeCol=1
Window 2:
USE tempdb
GO
SELECT * FROM dbo.Test AS t
The select is blocked and will run forever or until you stop it or commit/rollback the update
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
June 9, 2014 at 3:01 pm
GilaMonster (6/9/2014)
Andrew Kernodle (6/9/2014)
EDIT: Incorrect on my part! Tested it just now; issuing BEGIN TRAN and then not doing a COMMIT or ROLLBACK won't hold locks.It will hold locks. Any locks taken by a data modification are held until the transaction is committed or rolled back.
Window 1:
USE tempdb
go
CREATE TABLE Test (
SomeCol INT
)
INSERT INTO Test VALUES (1)
GO
BEGIN TRANSACTION
UPDATE Test SET SomeCol = 2 WHERE SomeCol=1
Window 2:
USE tempdbGO
SELECT * FROM dbo.Test AS t
The select is blocked and will run forever or until you stop it or commit/rollback the update
How bizarre! At the risk of derailing the thread a bit, I'd like to ask a bit about this.
I basically ran the same statements (just with a CHAR(1) column instead of an INT), though I ran them in our company's DBA database. I could run the select without any problems, even though I had a hanging transaction.
In fact, I simply copy-pasted your queries and redirected the USE statements, and got the same result! I double-checked our isolation level in sys.dm_exec_sessions, and it's a 2, for READ COMMITTED.
My gut feeling told me that the locks should be held, but after the testing, I thought I was mistaken. Instead, I'm just plain confused :-P. So, in an attempt to clear that confusion... In what cases should this be possible?
- 😀
June 10, 2014 at 2:29 am
Does your database have read_committed_snapshot enabled?
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
June 10, 2014 at 7:35 am
... *Facepalm* Yep, it certainly does. For some reason, I was thinking that READ COMMITTED SNAPSHOT was also part of the isolation levels in sys.dm_exec_sessions. That certainly wasn't the case :-P. Now to go read up on isolation levels a little more to get the inaccuracy out of my head 🙂
- 😀
June 10, 2014 at 7:43 am
Andrew Kernodle (6/10/2014)
... *Facepalm* Yep, it certainly does.
So writers don't block readers, instead the read query fetches the previous version out of the version store. The update still holds the lock until the transaction is committed or rolled back.
I was thinking that READ COMMITTED SNAPSHOT was also part of the isolation levels in sys.dm_exec_sessions.
It's not a separate isolation level. It's READ COMMITTED implemented using row versions.
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
June 10, 2014 at 11:05 am
Gotcha :-). Misstep on my part for not looking into whether READ COMMITTED SNAPSHOT was present first. This database is pretty much just a stored procedure container for the business, so it didn't occur to me that the snapshot setting would be on. Should've looked first to be absolutely sure, though!
- 😀
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply