Debian Codegraph @

doc/sql.extensions/README.savepoints

raw
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