ROLLBACK
ROLLBACK#
Syntax#
rollback ::=#
Description#
ROLLBACK (TO SAVEPOINT)#
This statement is used to roll back the current transaction, either partially (to a previously defined savepoint) or completely.
FORCE global_tx_id#
In an XA environment, this option is used to forcefully roll back a transaction that is in an "in-doubt" state.
global_tx_id is a character string that consists of the format identifier, the global transaction ID, and the branch qualifier of the global transaction.
Precaution#
This statement cannot be used in AUTOCOMMIT mode.
Example#
iSQL> AUTOCOMMIT OFF;
Set autocommit off success.
iSQL> UPDATE employees SET salary = 2300 WHERE eno = 3;
1 row updated.
iSQL> SAVEPOINT emp3_sal;
Savepoint success.
iSQL> DELETE FROM employees WHERE eno = 19;
1 row deleted.
iSQL> SAVEPOINT emp19_ret;
Savepoint success.
iSQL> INSERT INTO employees(eno, e_lastname, e_firstname, salary, sex) VALUES(21, 'Templeton', 'Kimmie', 3000, 'F');
1 row inserted.
iSQL> SAVEPOINT emp21_join;
Savepoint success.
iSQL> UPDATE employees SET salary = 2200 WHERE eno=18;
1 row updated.
iSQL> SELECT eno, e_lastname, e_firstname, salary FROM employees WHERE eno in (3, 18, 19, 21);
ENO E_LASTNAME E_FIRSTNAME SALARY
-------------------------------------------------------------------------
3 Kobain Ken 2300
18 Huxley John 2200
21 Templeton Kimmie 3000
3 rows selected.
The transaction is rolled back to the time point at which the savepoint emp21_join was defined.
iSQL> ROLLBACK TO SAVEPOINT emp21_join;
Rollback success.
iSQL> SELECT eno, e_lastname, e_firstname, salary FROM employees WHERE eno in (3, 18, 19, 21);
ENO E_LASTNAME E_FIRSTNAME SALARY
-------------------------------------------------------------------------
3 Kobain Ken 2300
18 Huxley John 1900
21 Templeton Kimmie 3000
3 rows selected.
The transaction is rolled back to the time point at which the savepoint emp19_ret was defined.
iSQL> ROLLBACK TO SAVEPOINT emp19_ret;
Rollback success.
iSQL> SELECT eno, e_lastname, e_firstname, salary FROM employees WHERE eno in (3, 18, 19, 21);
ENO E_LASTNAME E_FIRSTNAME SALARY
-------------------------------------------------------------------------
3 Kobain Ken 2300
18 Huxley John 1900
2 rows selected.
All of the changes made by the first UPDATE statement, the first DELETE statement and the last DML statement (the second INSERT statement) are committed. All of the other SQL DML statements were rolled back before the COMMIT statement was executed, and are thus lost. Additionally, the emp21_join savepoint is no longer available.
iSQL> ROLLBACK TO SAVEPOINT emp21_join;
[ERR-11016 : Savepoint not found]
iSQL> INSERT INTO employees(eno, e_lastname, e_firstname, sex, join_date) VALUES(22, 'Chow', 'May', 'F', TO_DATE('2011-11-19 00:00:00', 'YYYY-MM-DD HH:MI:SS'));
1 row inserted.
iSQL> COMMIT;
Commit success.
iSQL> SELECT eno, e_lastname, e_firstname, salary FROM employees;
ENO E_LASTNAME E_FIRSTNAME SALARY
-------------------------------------------------------------------------
1 Moon Chan-seung
2 Davenport Susan 1500
4 Foster Aaron 1800
5 Ghorbani Farhad 2500
6 Momoi Ryu 1700
7 Fleischer Gottlieb 500
8 Wang Xiong
9 Diaz Curtis 1200
10 Bae Elizabeth 4000
11 Liu Zhen 2750
12 Hammond Sandra 1890
13 Jones Mitch 980
14 Miura Yuu 2003
15 Davenport Jason 1000
16 Chen Wei-Wei 2300
17 Fubuki Takahiro 1400
18 Huxley John 1900
20 Blake William
3 Kobain Ken 2300
22 Chow May 0
20 rows selected.
iSQL> COMMIT;
Commit success.