Transaction COMMIT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Transaction COMMIT

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-10-09 : 06:09:34

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-09 : 06:59:25

Nope its working as its upposed to when i tried. I tried running both together as well as separate. In both the cases it deleted only Bubba and Johns record remains in table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-10-09 : 08:06:06

Hi

The whole query has nest,

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-09 : 08:40:40

Nested transactions behave in a "funny" way in SQL Server. The outermost commit is what will stick. And, any rollback will roll it back. Take a look at this page - they explain it much better than I can, with examples and all: http://technet.microsoft.com/en-us/library/ms189336(v=sql.105).aspx

The behavior you are seeing is expected behavior If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources