CODEWITHSUNDEEP
mysqlrollback
Pss
Pss

Reputation: 73

rollback to SAVEPOINT

I was trying below statements in phpmyadmin. Database: mysql. 

INSERT into cust values(5,'srk'); 
commit; 
UPDATE cust set cname='sk' where cid=5; 
savepoint A;

These statements are executed successfully.

But when i execute 

rollback to A;

Error:

#1305 - SAVEPOINT A does not exist

error is coming.

If i execute only rollback; It executes successfully but result is actually not rolled back.

Upvotes: 1

Views: 5609

Answers (2)

Sagar Ghodake
Sagar Ghodake

Reputation: 11

You must have to set,

set autocommit=0;

screenshot

Upvotes: 0

Drew
Drew

Reputation: 24949

First off, you weren't even in a transaction. And even once in one for a rollback to a savepoint, you have to commit to make it seen. You simply have to play with it. This should help I hope.

One starts a transaction with start transaction;

create table cust
(   id int auto_increment primary key,
    theValue int not null,
    theText varchar(50) not null,
    cname varchar(50) not null,
    cid int not null
);

INSERT into cust (theValue,theText,cname,cid) values(111,'aaa','a',1); 


start transaction;
    savepoint B1;
    INSERT into cust (theValue,theText,cname,cid) values(666,'aaa','a',1); 
    savepoint B2;
    INSERT into cust (theValue,theText,cname,cid) values(777,'aaa','a',1); 
    ROLLBACK to B2;
    -- at this moment, this connection can see 2 rows, other connections see 1 (id=1)
    select * from cust; -- visible to you but not others, that is,
commit;
-- at this moment all connections can see 2 rows. Give it a try with another connection open

.

select * from cust;
+----+----------+---------+-------+-----+
| id | theValue | theText | cname | cid |
+----+----------+---------+-------+-----+
|  1 |      111 | aaa     | a     |   1 |
|  2 |      666 | aaa     | a     |   1 |
+----+----------+---------+-------+-----+

From the Manual page SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Syntax

The ROLLBACK TO SAVEPOINT statement rolls back a transaction to the named savepoint without terminating the transaction.

It is important to know that in your code, line 2, the commit, you were never in a transaction. You never started one. There was nothing to commit.

Your line 1, the insert, considering it is not in a transaction, is a mini implicit transaction. It just happens. When your line 2 came around, the server was thinking, commit what?

Upvotes: 2

Related Questions