Transaction COMMIT
-
Hi
I have this query:
BEGIN TRANSACTION
INSERT CUSTOMER(NAME, CITY, STATE)
VALUES('John C', 'Chicago', 'IL')
COMMIT TRANSACTION
BEGIN TRANSACTION
INSERT CUSTOMER(NAME, CITY, STATE)
VALUES('Bubba C', 'Austin', 'TX')
ROLLBACK TRANSACTION
SELECT * FROM CUSTOMER
Now when I execute the first query, it addds John to my table as it's suppose to, and when I execute the second query it doesn't add Bubba as it's suppose to, but it also delete John who's added in the first query.
Why is it so?
-
When I run the query John is still in the table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
I agree with Koen. The only way to get the behavior you describe is if you are running under a nested transaction.
BEGIN TRANSACTIONBEGIN TRANSACTION
INSERT CUSTOMER(NAME, CITY, STATE)
VALUES('John C', 'Chicago', 'IL')
COMMIT TRANSACTION
BEGIN TRANSACTION
INSERT CUSTOMER(NAME, CITY, STATE)
VALUES('Bubba C', 'Austin', 'TX')
ROLLBACK TRANSACTION
SELECT * FROM CUSTOMER
-
Check If code is inside an nested transaction
-
Agreed - you have an earlier BEGIN TRANSACTION without a commit or rollback earlier in the code. When you have more than 1 BEGIN TRAN that hasn't been committed or rolled back, a single rollback with rollback all of them, even if you have "committed" them.
Here's a script I wrote a while back to experiment with this scenario:
CREATE TABLE dbo.NestedTranTest
(Col1 Int NULL,
Col2 Int NULL,
Col3 Int NULL,
Col4 Int NULL)
GO
Begin Tran
INSERT NestedTranTest
Values(1,null,null,null)
GO
Begin Tran
update NestedTranTest
set Col2 = 2
GO
Begin Tran
update NestedTranTest
set Col3 = 3
GO
Begin Tran
update NestedTranTest
set Col4 = 4
GO
commit tran
GO
commit tran
GO
rollback tran
GO
Commit Tran
select * from NestedTranTest
/*
delete NestedTranTest
*/
-
Hi
Yes there is actually nested query:
EGIN TRANSACTION [new_Account]
INSERT CUSTOMER(NAME, CITY, STATE)
VALUES ('Izetta','Greenville', 'AL')
IF EXISTS (SELECT * FROM CUSTOMER WHERE NAME = 'Izetta')
BEGIN
BEGIN TRANSACTION
INSERT BALANCES(AVERAGE_BAL, CURRENT_BAL)
VALUES(1250.76, 1431.26)
END
ELSE ROLLBACK TRANSACTION
COMMIT
BEGIN TRANSACTION
INSERT CUSTOMER(NAME, CITY, STATE)
VALUES('John C', 'Chicago', 'IL')
COMMIT TRANSACTION
BEGIN TRANSACTION
INSERT CUSTOMER(NAME, CITY, STATE)
VALUES('Bubba C', 'Austin', 'TX')
ROLLBACK TRANSACTION
select * from Customer
Then when I execute the last query then it get rid of everything else before it.
-
hoseam (10/9/2013)
HiYes there is actually nested query:
BEGIN TRANSACTION [new_Account]
INSERT CUSTOMER(NAME, CITY, STATE)
VALUES ('Izetta','Greenville', 'AL')
IF EXISTS (SELECT * FROM CUSTOMER WHERE NAME = 'Izetta')
BEGIN
BEGIN TRANSACTION
INSERT BALANCES(AVERAGE_BAL, CURRENT_BAL)
VALUES(1250.76, 1431.26)
END
ELSE ROLLBACK TRANSACTION
COMMIT
BEGIN TRANSACTION
INSERT CUSTOMER(NAME, CITY, STATE)
VALUES('John C', 'Chicago', 'IL')
COMMIT TRANSACTION
BEGIN TRANSACTION
INSERT CUSTOMER(NAME, CITY, STATE)
VALUES('Bubba C', 'Austin', 'TX')
ROLLBACK TRANSACTION
select * from Customer
Then when I execute the last query then it get rid of everything else before it.
Yup. Those are nested transactions. If you rollback one, you roll them all back.
You might avoid this if you properly commit/rollback all previous transactions you started. Right now you leave some transactions open.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Yes, it will. ROLLBACK will undo all data modifications right back to the outer BEGIN TRANACTION (as per documentation)
Nested transactions are a lie, they don't actually exist. The second, third, etc BEGIN TRAN does nothing other than increment an open transaction counter, it doesn't start a sub-transaction. COMMIT does nothing until that transaction counter is 1. When it is, COMMIT will actually commit the transaction (and decrement the tran counter). ROLLBACK anywhere will ROLLBACK the open transaction, that is everything back to the initial BEGIN TRAN
Don't play with nested transactions until you really, really, really understand how they behave, otherwise you're just asking for problems.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply