August 29, 2013 at 2:48 pm
It has happened to me for the 2nd time, on two different servers but both were 2008 R2. I tried to do a little research and find out why this happened but there was nothing to be read.
So, I start a transaction by
Begin Tran
do my stuff
check the affected tables
decide what to do, usually by running
COMMIT
After a while of doing so, sql gets timeouts and the system gets locked. I'm confused as I find out that I still have several uncommitted transactions.
The result of running commit was "Command completed successfully".
Can anyone tell me what was happening? Why these transactions did not commit even though commit completed successfully?
August 29, 2013 at 3:03 pm
danka_6786978 (8/29/2013)
It has happened to me for the 2nd time, on two different servers but both were 2008 R2. I tried to do a little research and find out why this happened but there was nothing to be read.So, I start a transaction by
Begin Trando my stuff
check the affected tables
decide what to do, usually by running
COMMITAfter a while of doing so, sql gets timeouts and the system gets locked. I'm confused as I find out that I still have several uncommitted transactions.
The result of running commit was "Command completed successfully".
Can anyone tell me what was happening? Why these transactions did not commit even though commit completed successfully?
Pretty hard to offer much help based on not enough information. Are you running these in SSMS? Are they all in the same window? How do you know that you have uncommitted transactions?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2013 at 3:13 pm
Thanks, and I only just now figured it out.
Yes, it was ssms, trying to remove a record from a table that is referenced many times by many tables. We don't have the option to do a cascade here so -- silly me -- I started begin tran and my deletion statement. On the error on that statement, I grab the table that's referencing the key, and remove the records there with begin tran/commit. I did not rollback the error-ed first statement. I ran the first query again, it points me to the next table with FK. and on and on.
I end up with 15 open transactions (which showed up when I tried to close ssms).
So, I'm changing my question then: if my statement that I started with begin Tran errors out, will that transaction stay open till I do rollback or commit? What's the best thing to do there, rollback or commit?
Thanks
August 29, 2013 at 3:19 pm
danka_6786978 (8/29/2013)
Thanks, and I only just now figured it out.Yes, it was ssms, trying to remove a record from a table that is referenced many times by many tables. We don't have the option to do a cascade here so -- silly me -- I started begin tran and my deletion statement. On the error on that statement, I grab the table that's referencing the key, and remove the records there with begin tran/commit. I did not rollback the error-ed first statement. I ran the first query again, it points me to the next table with FK. and on and on.
I end up with 15 open transactions (which showed up when I tried to close ssms).
So, I'm changing my question then: if my statement that I started with begin Tran errors out, will that transaction stay open till I do rollback or commit? What's the best thing to do there, rollback or commit?
Thanks
Still not much to go on here but in general if you have a transaction and one of the steps errors you probably want to rollback. That would kind of be the purpose of a transaction, so you can attempt multiple queries and rollback if something goes wrong. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2013 at 3:26 pm
if my statement that I started with begin Tran errors out, will that transaction stay open till I do rollback or commit?
Yes. Once a transaction begins, it must either ultimately be committed or rolled back. [At a certain point, SQL might forcibly roll it back.]
What's the best thing to do there, rollback or commit?
Almost always a rollback is done, although specifics of any given app might allow a commit to be used.
Best if available is to use a CATCH / TRY, and rollback if needed.
For example:
BEGIN TRY
BEGIN TRANSACTION
...
COMMIT
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK
--or, if your app data allows for it:
--IF XACT_STATE() = 1 COMMIT TRANSACTION
--ELSE IF XACT_STATE() <> 0 ROLLBACK TRANSACTION
END CATCH
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 29, 2013 at 3:27 pm
Yup, it seems quite obvious when I look at it now, though my logic first told me, hey, if there was an error, there was nothing to perform transaction on. I assumed the transaction was not noted. Well, they do exist until you close them by rollback or commit.
begin tran
delete from Tbl
where ID = 4
/error happens, cannot delete coz of FK /
begin tran
delete from TBL_with_FK
where FK_ID = 4
commit
/that transaction was committed as it was successful. However, my first transaction is still open./
/I try again: /
begin tran
delete from Tbl
where ID = 4
/error... another table with FK... If I move on to delete from that table, I'll have 2 open transactions/
Yes, thank you! I'll be more careful from now on
August 29, 2013 at 4:07 pm
danka_6786978 (8/29/2013)
So, I'm changing my question then: if my statement that I started with begin Tran errors out, will that transaction stay open till I do rollback or commit? What's the best thing to do there, rollback or commit?
What happens with the transaction when you get an error depends on the exact error you get. This is an area where SQL Server is extremely confusing.
Some errors terminates only the current statement. There is no rollback.
Some errors terminates the current scope. There is no rollback.
Some errors terminates the entire batch. They roll back the transaction. (In SQL 2008. In SQL 2012 there are errors that abort the batch without rolling back.)
TRY-CATCH was mentioned. However TRY-CATCH in scripts will not catch errors like misspelled table names. You should also use SET XACT_ABORT ON. When this setting is in effect, most errors abort the batch and rollback the transaction. (There are a few ones that does not: RAISERROR, syntax errors in dynamic SQL, dropping a non-existing table.)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 29, 2013 at 8:35 pm
I'd like to ephasize what Erland wrote. There will always be the rare exception but get into the habit of using SET XACT_ABORT ON even if you don't use TRY/CATCH.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply