Inside Savepoints
Dmitri Yemanov
General information
A savepoint is an internal mechanism of the database, which binds any changes in the database to a specific point of time during a transaction, and in case of necessity, allows a user to cancel all changes, which were made after setting this particular savepoint. This process is also known as rolling back to savepoint.
Also server uses savepoint mechanism to implement transaction handling This mechanism helps either to commit or to cancel all changes made during a transaction. For those purposes, the server uses the global savepoint.
Such a savepoint is set automatically when a transaction starts, and it is the first savepoint in the transaction context. When transaction rollback is initiated, all changes made within its context are cancelled using the transaction global savepoint. After that, the transaction is commited (!) within the Transaction Inventory Page (TIP). This is necessary in order to avoid housekeeping operations in the future.
However, if the number of changes in transaction context becomes too big (approx.10000 - 1000000 records), then the storing of rollback lists becomes expensive, and the server deletes the transaction global savepoint, switching to the standard TIP mechanism to mark the transaction as dead.
In addition to the use of savepoints for rollbacks, the server also uses them for exception handling. Each SQL and/or PSQL operator is enclosed in a savepoint frame, which allows to rollback this particular operator, keeping the previous ones unchanged. This guarantees either successful execution of the operator or automatic cancellation of all changes made, and a corresponding error will be initiated.
Tip:
If you expect that during transaction many changes are to be made, then
it makes sense to specify the isc_tpb_no_auto_undo transaction parameter, which disables usage of the global savepoint for rollback. In some cases, it allows to increase server’s performance during batch operations.
For exception handling in PSQL, each BEGIN…END block is also enclosed in a savepoint frame, which allows you to cancel all changes made by this block. Let’s consider some details of how savepoints work.
Savepoints in action
A savepoint is a data structure, which is located in the server’s dynamic storage transaction pool) and has a unique numerical ID. A list of activities made within the savepoint context is associated with this savepoint. Such a list is called an “undo log.” Savepoints form a stack within a transaction, and that is the reason why only sequential rollback of savepoints is possible.
Undo log fragments are distributed across savepoints that store the history. A savepoint, which is active when a record is being changed, is called “cur rent”. Information about record changes is stored in the current savepoint undo log. If a rollback to the savepoint is per formed, the undo log is unwound, and records are reconstructed. As a result, the record becomes as it was at the moment this savepoint was set.
In case there are no exception handlers available, the records may be reconstructed down to the global savepoint, providing complete transaction rollback. After reconstruction of all modified records, the savepoint is usually deleted from the transaction context.
Releasing savepoints
In addition to the rollback to savepoint operation, there is an operation of regular deletion (release) of a savepoint. In case a savepoint is deleted, its undo log is merged with the undo log of the previous one, in the savepoint stack. In this sense, each savepoint is nested.
It is clear, that regular deletion of all savepoints located “deeper” than the global one, would lead to the transfer of all changes to the transaction global savepoint.
Tip:
As is illustrated below, each custom SQL-query is a set of savepoints within a transaction.
Thus, the combination of all changes, which were successfully executed during transaction, is stored in undo logs. That is why, when the automatic undo log is enabled, the server can cancel all performed changes, and in the TIP this transaction would be marked as committed, and not as dead.
Figure 2 Releasing a savepoint: undo data moved to upward savepoint
When specifying the isc_tpb_no_auto_undo parameter at transaction start, a global savepoint is not created, and if regular deletion of the current stack is performed, the combined undo log is simply deleted, and transaction is marked dead, and all changes are to be cleared (garbage collection).
Tip:
It is necessary to note that the isc_tpb_no_auto_undo parameter does not disable the savepoints mechanism (this is impossible due to the atomicity guarantee for SQL-operators ). It only disables the creation
of a transaction undo log as a single whole.
Savepoints and exception handling
There are several events, which make server create system-defined (i.e. user-uncontrollable) savepoints:
1. Execution of any user SQL-query. As has been said above, this done in order to guarantee atomicity of a query. That is to say, if any exception occurs during query execution, the changes made in the database will always be canceled. After the query is executed, the savepoint will be
2. Execution of the BEGIN…END block in PSQL (stored procedure or a trigger) in case the block contains an error han dler (WHEN-block). In that case, each BEGIN operator sets a savepoint, a corresponding END operator deletes it. This enables to provide error han dling in the PSQL-block.
3. Execution of an SQL-operator in context of a “BEGIN…END” block, which contains an error handler (WHEN-block). That is, if the block con tains an error handler, any SQL-opera tor in this block is framed by a save point frame.
Exception handling
Let’s consider the errors handling process on the server. When an exception occurs, automatic rollback to the last set savepoint is performed. As a result, all operations performed by an invalid SQL-query would be canceled. Then, in case of a PSQL-block, it is checked, if there is a custom WHEN handler. If it does exist, control is trans- ferred to it, and after exiting, savepoint is deleted.
Then the process repeats recursively, until embedded handlers end. Thus, if a stored procedure doesn’t contain a handler which would be able to handle this error, the whole procedure will be considered as a single SQL-operator, and canceled.
Let’s make a summary
Firstly, if there is no WHEN-handler, any PSQL-block (including stored procedure and trigger) becomes atomic, and would be canceled entirely, if an error occurs.
Secondly, in the presence of a WHEN-handler, the occurrence of an error leads a rollback of the only operator, and after that the process is managed by the handler. That is to say, there are obvious differences in the server’s reactions, which depend on the presence of a WHEN-handler.
It is worth considering a known anomaly, which does not fit the scheme described above. The scheme works as follows: the paragraph 3 (enclosing of each SQL-operator inside a PSQL- block by its own savepoint frame) is true for SQL-operators only (moreover, not for all of them). In other words, for example, an assignment operator will not be framed by a savepoint frame.
As a result of an error in assignment, execution leads to a normal rollback to the previous savepoint, which is… exactly! - the block’s savepoint.
That is to say, even if there is a WHEN-handler, the error may cause the rollback of the whole block before control to the error handler.
I consider this situation as a serious flaw in the server’s exceptions handling logic. Below is the full list of operators, for which the savepoint frame is created: INSERT, UPDATE, DELETE, EXCEP- TION, EXECUTE STATEMENT [INTO].
To understand the reasons of this anomaly, it is necessary to take into account the following 2 facts:
- If an error occurs, rollback to the last savepoint is performed unconditionally.
- A block’s savepoint deletion (or rollback to it) is performed together with the verification of the savepoint’s identifier
This means that when an error occurs in operators not included to the above list i.e. not enclosed in a savepoint frame), that actually the wrong (block’s) savepoint is deleted. But the block itself is tol- erant to that fact, since it only deletes its own savepoint (in case it has one). This is the reason why the described server error does not cause fatal consequences.
Let us illustrate such a situation, using the description of the [428903] Exception Handling Bug error. To clarify this, we provide these examples with comments about show the server deals with savepoints.
Example 1
A procedure with an error handler and error generation in the assignment operator:
CREATE PROCEDURE PROC1 AS DECLARE VARIABLE X INT; -- start savepoint #1 BEGIN -- start savepoint #2 INSERT INTO TAB (COL) VALUES (01); -- end savepoint #2 X = 1 / 0; WHEN ANY DO EXIT; -- end savepoint #1 END
In this case, since savepoint #1 is the nearest to the erratic operator (savepoint #2 was deleted right before assignment execution), INSERT will be canceled. Therefore, a rollback of the whole BEGIN…END block will be performed before entering the handler.
Below is the same procedure with an explicit exception call, in the block, which contains WHEN-handler:
CREATE PROCEDURE PROC2 AS DECLARE VARIABLE X INT; -- start savepoint #1 BEGIN -- start savepoint #2 INSERT INTO TAB (COL) VALUES (23); -- end savepoint #2 BEGIN X = 1 / 0; END WHEN ANY DO EXIT; -- end savepoint #1 END
As you see, no savepoint frame near assignment operator was created. Therefore, the result would be similar to the previous one. Below is the same procedure with an explicit exception call, in the block, which contains WHEN-handler:
CREATE PROCEDURE PROC3 AS DECLARE VARIABLE X INT; -- start savepoint #1 BEGIN -- start savepoint #2 INSERT INTO TAB (COL) VALUES (45); -- end savepoint #2 -- start savepoint #3 BEGIN X = 1 / 0; WHEN ANY DO EXIT; -- end savepoint #3 END WHEN ANY DO EXIT; -- end savepoint #1 END
Here we see a created savepoint frame. As a result, rollback is performed only for the nearest BEGIN level (savepoint #3), and the INSERT operator remained executed.
Example 2
procedure with error handler and explicit exception call:
CREATE PROCEDURE PROC4 AS -- start savepoint #1 BEGIN -- start savepoint #2 INSERT INTO TAB (COL) VALUES (67); -- end savepoint #2 -- start savepoint #3 EXCEPTION E; -- end savepoint #3 WHEN ANY DO EXIT; -- end savepoint #1 END
In this case, INSERT will not be canceled, due to the fact that the exception initiation of E results in rollback to savepoint #3 and subsequent transfer of control to the error handler. In order to cancel the INSERT operator in this case, you should inhibit the execution of the deletion handler:
CREATE PROCEDURE PROC5 AS BEGIN INSERT INTO TAB (COL) VALUES EXCEPTION E; END
In addition, a few words about exception handling adequacy regarding the SQL-standard.
The standard allows three types of handlers in PSQL: CONTINUE, EXIT, and UNDO. With a CONTINUE-handler, the server must rollback the erroneous operator, execute the handler code, and then continue the execution of the block, beginning with the operator next to the which one caused the error.
An EXIT-handler requires finishing of the execution of the block right after exiting the handler code.
An UNDO-handler requires a rollback of all actions of the block before entering the handler.
Current versions of the server (InterBase as well as Firebird) do not support the explicit specification of the handler type, and work according to the EXIT principle (however, there is a possibility of UNDO-behavior due to the anomaly described above).
I suppose that in the future, it would be desirable to provide an alternative to choose between UNDO- and EXIT- behavior of a handler, and repair the described anomaly.
Custom savepoints
In addition to the internal implementation of savepoints at transaction (and operaftor/block) levels, the latest versions of servers (Inter- Base 7.1, Firebird 1.5, and Yaffil 1.1) provide an SQL- interface, developed for this mechanism.
Note: savepoints’ syntax and semantics are declared in the SQL- 99 standard (see section 4.37.1 of the specification).
Custom savepoints (also known as nested transactions) provide a convenient business logic error handling method, with no need to rollback the whole transaction.
Note: rollback to a savepoint is also sometimes called “partial transaction rollback.”
New SQL operator (SAVEPOINT) was added to define a savepoint in the transaction context, to which a rollback can be performed later on:
SAVEPOINT <name>;
<name> - the string identifier of a savepoint. As soon as a savepoint is created, you can either continue transaction, commit (or cancel) the whole transaction, or perform a rollback to a particular savepoint. Savepoints’ names (identifiers) must be unique in the context of a transaction. If you attempt to create two savepoints with similar names, the first savepoint is deleted, and the specified name is given to the second one.
For rollback to a savepoint, the following operator is used:
ROLLBACK [WORK] TO [SAVEPOINT]
Note: the SAVEPOINT keyword is obligatory in InterBase 7.1.
During execution of this operator, the following actions are performed:
- Rollback of all changes made after the savepoint was set;
- All savepoints set after this one are deleted. The current savepoint remains unchanged, and thus you can perform several rollbacks to a savepoint. Previous savepoints remain unchanged as well.
Note: Performing a rollback to savepoint in InterBase 7.1 deletes the selected savepoint.
- All explicit and implicit write locks, occupied after the savepoint was set, are released. At that, other transactions, which requested an access to the records blocked by the transaction after the savepoint was set, continue waiting for the current transaction to be finished. Transactions, which did not request access to the records, may continue and get access to them.
Note: This behavior refers to Firebird 1.5 and can be changed in higher versions.
Since each savepoint uses certain system resources, and also clogs the namespace, it makes sense to release (delete) savepoints when they are no longer necessary. This can be accom plished using the following operator:
RELEASE SAVEPOINT <name> [ONLY];
This command deletes the selected (and all following) savepoints from transaction context. The “ONLY” option is a switch to delete the selected savepoint only; at that, all following savepoints will be saved. If a savepoint was not released explicitly, it will be automatically deleted soon as the transaction is finished.
Note: The “ONLY” option is non-standard extension, and not supported by InterBase 7.1.
A simple example of working with savepoints is given below:
create table test (id int); commit; insert into test (id) values (1); commit; insert into test (id) values (2); savepoint y; delete from test; select * from test; -- returns empty set rollback to y; select * from test; -- returns two records rollback; select * from test; -- returns one record
A custom savepoint
Now let us consider an example of how savepoints can be used in business logic. Assume there is an operation of mass document handling in the application, and is necessary to display error messages (or save them for future presentation as a list), and let this bulk operation continue. Since the document handling operation is not atomic, on the client’s side it is better not to use regular exception hadling since we cannot continue the transaction if we know that an exception performed a rollback of only half of the operation.
Such a dilemma can be resolved by handling each document sequentially in a separate transaction. Nevertheless, this does increase the consumption of internal server resources (maximum number of records in TIP, transaction counter increment), and is therefore not the best alternative.
In addition, if there is a need to fix a set of documents during the handling process for example, by changing the transaction isolation mode or explicit blocking of the SELECT … WITH LOCK type), it would require using only one transaction for the delta packet. Using a savepoint, the following algorithm would be used (in pseudocode):
START TRANSACTION; OPEN C FOR ( SELECT ... ); FOR ( C ) DO LOOP TRY SAVEPOINT DOC; <...> ///single document handling commands EXCEPT ROLLBACK TO SAVEPOINT DOC; <...> //either log the error or display it END END CLOSE C; COMMIT;
Note: The use of savepoints in loops has an additional advantage: you do not need to call RELEASE each time, since resetting a savepoint automatical deletes the previous savepoint with the same name.
Another example is, undoubtedly, audit. For example, you need to pro vide a log record for each activity, and the same time, if an error occurs, the record should remain in the audit log with a corresponding note):
START TRANSACTION; INSERT INTO AUDIT_LOG (ID, EVENT, STATUS) VALUES (:ID, :EVENT, SAVEPOINT OPER; TRY <...> // operations on database EXCEPT ROLLBACK TO SAVEPOINT OPER; UPDATE AUDIT_LOG SET STATUS = 0 WHERE ID = :ID; END COMMIT;
Savepoints in stored procedures and triggers
Now let us consider the usage of custom savepoints in procedures and triggers.
At first glance, it looks very attractive and useful. Originally this functionality is declared in InterBase 7.1. Let’s consider the capabilities in detail.
First of all, savepoints must not break the atomicity of SQL-operators. This means that none of the commands can be canceled partially. Remember that EXECUTE PROCEDURE is a legal SQL-operator, and any operators updates may lead to trigger execution. Generally speaking, any “simple” operator, such as INSERT or UPDATE may result in launching of a whole chain of triggers and procedures. That is why we are to examine the scope of a savepoint.
t is obvious that in order to meet the atomicity requirement, savepoint affected instructions with in a procedure should not have an access to the transaction savepoint (set through the SAVEPOINT global operator). In addition, procedure savepoints must be local and their scope must be defined by the procedure. That is to say, there can be a savepoint named “S1″ both in the transaction and in procedures and triggers executed in the context of this transaction. At that, such savepoints will be isolated from each other. Note that this very method is used in InterBase 7.1.
A question emerges: how would custom savepoints coexist with internal savepoints managed by the server?
Some theory
Let us consider a simple example of savepoint usage in PSQL, suggested by Borland in the InterBase 7.1 server documentation:
CREATE PROCEDURE ADD_EMP_PROJ2 ( EMP_NO SMALLINT, EMP_NAME VARCHAR(20), PROJ_ID CHAR(5) ) AS BEGIN BEGIN SAVEPOINT EMP_PROJ_INSERT; INSERT INTO EMPLOYEE_PROJECT (EMP_NO, PROJ_ID) VALUES (:EMP_NO, :PROJ_ID); WHEN SQLCODE -530 DO BEGIN ROLLBACK TO SAVEPOINT EMP_PROJ_INSERT; EXCEPTION UNKNOWN_EMP_ID; END END END
This example demonstrates how exceptional situations are handled when using savepoints. That is to say, when an exception with code -530 occurs (the violation of reference integrity by a foreign key) we cancel the insert operation and initiate a user exception. Actually, this example is absolutely useless, since we do not need a savepoint here:
BEGIN INSERT INTO ... WHEN SQLCODE -530 DO EXCEPTION unknown_emp_id; END
INSERT operation when an exception during it’s execution. Let’s consider a more complicated example:
FOR SELECT ID, ... INTO :REC_ID, ... BEGIN SAVEPOINT S1; INSERT INTO TABLE1 ... INSERT INTO TABLE2 ... INSERT INTO TABLE3 ... EXECUTE PROCEDURE ... ... WHEN ANY DO BEGIN ROLLBACK TO SAVEPOINT S1; ERROR = REC_ID; SUSPEND; END END
Here we try to handle all documents, but the program does not stop in of failure, it only returns all unsuccessful attempts at the end of the procedure. Server standard logic would cancel error operator and control to the dler, which in its turn cancels actions the whole block. Thus, we can turn the EXIT-handler to UNDO in if necessary. Of course, this functionali ty can be obtained by standard means as well:
FOR SELECT ID, ... INTO :REC_ID, ... BEGIN BEGIN INSERT INTO TABLE1 ... INSERT INTO TABLE2 ... INSERT INTO TABLE3 ... EXECUTE PROCEDURE ... ... END WHEN ANY DO BEGIN ERROR = REC_ID; SUSPEND; END END
In this case, all operators within the loop will be automatically canceled, in the event that an exception occurs, since the operators are located in the atomic block by which the savepoint frame for SQL-operators mechanism was enabled. After that, the server will go through the chain of embedded blocks, and will switch to the handler.
Therefore, in virtually any case, one can realize the same semantics using the server’s standard mechanisms, i.e. using system savepoints instead of cus tom ones, at the cost of relatively unhandy source code. Thus, savepoints in PSQL are nothing but an easy and comprehensive alternative for the explic it usage of BEGIN…WHEN…END blocks.
A bit of practice
tice and test this reasoning in InterBase 7.1. The result is quite depressing: none (!!!) of the given examples work, and error messages appear:
Statement failed, SQLCODE = - 504 Savepoint <name> unknown.
Even the first example, which was taken from the Release Notes (!), is not work ing properly. At the same time, the most primitive examples, such as:
SAVEPOINT S1; INSERT ... ROLLBACK TO SAVEPOINT S1;
work correctly. So what’s the matter? If we investigate the situation more careful ly, the reason becomes obvious. Remem ber the two facts described above:
1. savepoints constitute a stack, and can be canceled sequentially only
2. each block of PSQL-code with an exception handler is enclosed in a frame Thus we arrive at a conclusion that any code area of the following type:
SAVEPOINT S1; ... BEGIN ... ROLLBACK TO SAVEPOINT S1; ... WHEN
is definitely invalid, since to perform a rollback to savepoint S1, it would be necessary to delete the system savepoint, created by the server for excep- tion handling in the “BEGIN…END” block. This would destroy the internal undo log, and may corrupt the database.
Thank God, the InterBase developers did not create such cardinal realization, and server attempts to cancel the previous (last) savepoint directly, only if its name matches. Since system savepoints are unnamed, in this case such an attempt would fail. This is proven by the above mentioned error message.The above makes us arrive to the conclusion that working with savepoints in PSQL is limited by the nesting level, in case we are dealing with blocks with a WHEN-handler.
However, it turned out that the most interesting thing is yet to come. The server’s reaction to the error initiated by the ROLLBACK TO SAVEPOINT or RELEASE SAVEPOINT operator is amusing. Let’s illustrate this using an example:
BEGIN INSERT INTO TABLE1 ... ROLLBACK TO SAVEPOINT S1; INSERT INTO TABLE2 ... END
This is an emulation of an error, which usually occurs if the required save point cannot be found within a single code block. As one would expect, the execution of the procedure returns the same error. But!!! Procedure execution does not stop at this point. Instead, the second INSERT is executed (which you can easily verify by substituting INSERT with an operator of EXCEP TION E_TEST type). The question is, why? It turns out that this error cannot be handled in the procedure, i.e. the code:
INSERT INTO TABLE1 ... BEGIN ROLLBACK TO SAVEPOINT S1; WHEN ANY DO EXCEPTION E_TEST; END
does not throw the E_TEST exception, as one might expect. Even though the code after ROLLBACK TO SAVE POINT is executed, nothing really happens. Which means, that in case the described error occurs in a proce dure, all changes made by this proce dure will be unconditionally (!) can celed. This happens regardless of which code was executed before or after the command. It would be inter esting to find out how InterBase devel opers explain this phenomenon
Summary
There are some peculiarities in savepoints logic, which prevent realization of their complete support by PSQL. The analysis of InterBase 7.1 behavior proves the point. The rationale of that is the presence of system savepoints, which interaction with custom ones is limited, due to data integrity requirements. That’s why this functionality is neither available in Firebird, nor in Yaffil.
Note: As far as I understand it, the same reasons prevent from using commit/roll- back retaining in PSQL, since in that case the savepoint-frame of a procedure would be destroyed.
Savepoint in distributed transactions
InterBase 7.1 introduces the option to work with savepoints in coordinated transactions. For this purpose, three new API functions are introduced:
SC_STATUS isc_start_transaction(ISC_STATUS* status, isc_tr_handle* trans, char* name); ISC_STATUS isc_release_transaction(ISC_STATUS* status, isc_tr_handle* trans, char* name); ISC_STATUS isc_rollback_transaction(ISC_STATUS* status, isc_tr_handle* trans, char* name, short option);
As you see, these functions do not have a connection descriptor (database handle) which means that corresponding SQL-commands are issued to As you see, these functions do not have a connection descriptor (database handle) which means that corresponding SQL-commands are issued to
/* Connect to the database */ isc_attach_database(status, 0, database1, &db1, 0, NULL); ; isc_attach_database(status, 0, database2, &db2, 0, NULL); /* Begin coordinated transaction */ isc_start_transaction(status, &trans, 2, &db1, 0, NULL, &db2, 0, NULL); /* Create savepoint */ isc_start_savepoint(status, &trans, \"A\"); /* Executing database operations */ isc_dsql_execute_immediate(status, &db1, &trans, 0, \"DELETE FROM TABLE1\", 1, NULL); isc_dsql_execute_immediate(status, &db2, &trans, 0, \"DELETE FROM TABLE2\", 1, NULL); /* Delete the savepoint explicitly, through the second connection descriptor */ isc_dsql_execute_immediate(status, &db2, &trans, 0, \"RELEASE SAVEPOINT A\", 1, NULL); /* Rollback to savepoint */ isc_rollback_savepoint(status, &trans, \"A\", 0); /* Commit coordinated transaction */ isc_commit_transaction (status, &trans); /* Disconnecting the database */ isc_detach_database(status, &db1); isc_detach_database(status, &db2);
As a result of a rollback to savepoint, I expect rollback to be performed in both databases I work with. Then I commit the transaction, and after that I need to see all data on their places, since DELETE operators were can- celed.
And that would be that way, but for the manually executed “RELEASE SAVEPOINT A.”
At first, savepoint rollback was performed for the first connection and all changes were canceled.
Then the same operation was accomplished for second connection, while… oops! … there is no savepoint any- more. As a result, the client receives an error message. But rollback of one of the DELETE operators was success- ful (!) This is a situation, when the coordinated operation disintegrates itself, and makes correct handling of the case impossible.
The two-phase fixation of transaction mechanism, which should bar from such cases, simply cannot deal with savepoints.
That is to say, the new InterBase 7.1 functions create appropriate SQL- commands, and then cyclically exe- cute them for databases involved in this process.
Even if only one of them fails, an error returns. Generally, this error does not characterize the current situation, in terms of correctness of the operation as a single whole.
Of course, one might say that only one method of working with savepoints should be applied - either through SQL, or through API.
Now you know what it leads to. However, for working with savepoints in InterBase 7.1, a new API can be completely substituted by server’s standard means.
The server should either suppress possibility to control savepoints in certain connections in cases of coordinated transactions, or refuse to declare their workability.
It is necessary to note that Firebird’s and Yaffil’s developers have chosen this way, preferring not to provide users with such an ambiguous feature.
