CODEWITHSUNDEEP
sql-servercommitrollback
jabir jazz
jabir jazz

Reputation: 187

commit and rollback conflict

I faced one problem in this query execution on Microsoft SQL Server Management Studio

CREATE DATABASE myDB
USE myDB

CREATE TABLE udata(uid INT PRIMARY KEY identity(101, 2),
    uname VARCHAR(25), unum INT UNIQUE)

CREATE TABLE usalary(sid INT PRIMARY KEY identity(1, 1),
    salary NUMERIC(18, 0), FKuid INT REFERENCES udata(uid))

INSERT INTO udata VALUES ('yogesh', 99)
INSERT INTO udata VALUES ('tejas',  88)

INSERT INTO usalary VALUES (15000, 103)


BEGIN TRANSACTION
SAVE TRANSACTION SP1
DELETE FROM udata WHERE uid = 1
COMMIT;
ROLLBACK to SP1
SELECT * FROM udata WHERE uid=1

BEGIN TRANSACTION
SAVE TRANSACTION SP2
TRUNCATE TABLE usalary
COMMIT
ROLLBACK to SP2
SELECT * FROM usalary

here when we commit the transaction it should be saved in database but after rollback the data will come back.How's that possible??

Here my question is simple. in SQL documentation it is mentioned that after commiting any query or transaction we can not rollback.(we can not get our previous state of database.) like if we create savepoint a and perform delete query on our database and explicitly give commit. the documentation say that we can't rollback from this state but if i execute rollback command here I get my data back.

The whole series of command(query) is mentioned here for ease who wants to help from create database command to rollback command.

Upvotes: 0

Views: 1445

Answers (1)

qbik
qbik

Reputation: 5908

For a query like this:

BEGIN TRANSACTION
SAVE TRAN t1
DELETE FROM udata;
COMMIT;
ROLLBACK TRANSACTION t1

You will get an error: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.. Sure, no rollback after a commit.

But if you try to wrap it in another transaction:

BEGIN TRANSACTION
    BEGIN TRANSACTION
    SAVE TRAN t1
    DELETE FROM udata;
    COMMIT;
    ROLLBACK TRANSACTION t1
COMMIT

This will work fine. Why? documentation on Nesting Transactions says:

Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction.

My guess here is: either your not posting whole query here and there is a BEGIN TRANSACTION statement somewhere else or you have been testing transactions inside Management Studio and somewhere along the way you executed BEGIN TRANSACTION without matching ROLLBACK or COMMIT. In the later case, try to execute single ROLLBACK statement until you get an error. You can also just restart Management Studio.

Upvotes: 2

Related Questions