| 1 |
SQL Language Extension: support for SQL99 savepoints |
| 2 |
|
| 3 |
Syntax |
| 4 |
======== |
| 5 |
|
| 6 |
SAVEPOINT <identifier>; |
| 7 |
|
| 8 |
Use the SAVEPOINT statement to identify a point in a transaction |
| 9 |
to which you can later roll back. <identifier> specifies the |
| 10 |
name of a savepoint to be created. Savepoint names must be |
| 11 |
distinct within a given transaction. If you create a second |
| 12 |
savepoint with the same identifer as an earlier savepoint, |
| 13 |
the earlier savepoint is erased. After a savepoint has been |
| 14 |
created, you can either continue processing, commit your work, |
| 15 |
roll back the entire transaction, or roll back to the savepoint. |
| 16 |
|
| 17 |
ROLLBACK [WORK] TO [SAVEPOINT] <identifier>; |
| 18 |
|
| 19 |
This statement performs the following operations: |
| 20 |
- Rolls back changes performed in the transaction after the savepoint |
| 21 |
- Erases all savepoints created after that savepoint. The named |
| 22 |
savepoint is retained, so you can roll back to the same savepoint |
| 23 |
multiple times. Prior savepoints are also retained. |
| 24 |
- Releases all implicit and explicit record locks acquired since the |
| 25 |
savepoint. Other transactions that have requested access to rows |
| 26 |
locked after the savepoint must continue to wait until the transaction |
| 27 |
is committed or rolled back. Other transactions that have not already |
| 28 |
requested the rows can request and access the rows immediately. |
| 29 |
Note: this behaviour may change in the future product versions. |
| 30 |
|
| 31 |
RELEASE SAVEPOINT <identifier> [ONLY]; |
| 32 |
|
| 33 |
Use the RELEASE SAVEPOINT statement to erase savepoint <identifer> |
| 34 |
from the transaction context. Unless you specify ONLY clause all savepoints |
| 35 |
established since the savepoint <identifier> are erased too. |
| 36 |
|
| 37 |
Author: |
| 38 |
Nickolay Samofatov <[email protected]> |
| 39 |
(original implementation of user savepoints) |
| 40 |
Dmitry Yemanov <[email protected]> |
| 41 |
(refactored DSQL layer and BLR for better SQL99 and JDBC compliance) |
| 42 |
|
| 43 |
N O T E S |
| 44 |
========= |
| 45 |
|
| 46 |
1. Using savepoints (alternate name is "nested transactions") is a very |
| 47 |
convenient method to handle business logic errors without rolling back |
| 48 |
the transaction. |
| 49 |
|
| 50 |
Common pattern for this (Java) is: |
| 51 |
stmt.executeUpdate("SAVEPOINT MyClass$do_some_work"); |
| 52 |
try { |
| 53 |
MyClass.do_some_work(); |
| 54 |
} catch(Exception ex) { |
| 55 |
stmt.executeUpdate("ROLLBACK TO MyClass$do_some_work"); |
| 56 |
throw; |
| 57 |
} |
| 58 |
stmt.executeUpdate("RELEASE SAVEPOINT MyClass$do_some_work"); |
| 59 |
|
| 60 |
2. User savepoints are not supported in PSQL. Use traditional PSQL |
| 61 |
exception handling to undo changes performed in stored procedures |
| 62 |
and triggers. Support of user savepoints in PSQL layer would break |
| 63 |
the concept of statement atomicity (including procedure call statements). |
| 64 |
Each SQL/PSQL statement is executed under automatic system savepoint |
| 65 |
and either complete successfully or ALL its changes are rolled |
| 66 |
back and exception is raised. Each PSQL exception handling block is |
| 67 |
also bounded by automatic system savepoints. |
| 68 |
|
| 69 |
3. Savepoint undo log may consume significant amounts of server |
| 70 |
memory exceptionally if you update the same records in the |
| 71 |
same transaction multiple times. Use RELEASE SAVEPOINT statement |
| 72 |
to release system resources required for savepoint maintenance. |
| 73 |
|
| 74 |
4. By default, engine uses automatic transaction-level system |
| 75 |
savepoint to perform transaction rollback. When you issue |
| 76 |
ROLLBACK statement all changes performed in this transaction |
| 77 |
are backed out via transaction-level savepoint and transaction |
| 78 |
is committed then. This logic is used to reduce amount of garbage |
| 79 |
collection caused by rolled back transactions. When amount of |
| 80 |
changes performed under transaction-level savepoint is getting large |
| 81 |
(10^4-10^6 records affected) engine releases transaction-level |
| 82 |
savepoint and uses TIP mechanism to roll back the transaction if |
| 83 |
needed. You can use isc_tpb_no_auto_undo TPB flag to avoid creating |
| 84 |
transaction-level savepoint if you expect large amount of changes |
| 85 |
in your transaction. |
| 86 |
|
| 87 |
Example |
| 88 |
======= |
| 89 |
|
| 90 |
create table test (id integer); |
| 91 |
commit; |
| 92 |
insert into test values (1); |
| 93 |
commit; |
| 94 |
insert into test values (2); |
| 95 |
savepoint y; |
| 96 |
delete from test; |
| 97 |
select * from test; -- returns no rows |
| 98 |
rollback to y; |
| 99 |
select * from test; -- returns two rows |
| 100 |
rollback; |
| 101 |
select * from test; -- returns one row |