Transaction Handling and Database Security
This chapter explains transaction principles, transaction control statements, logging and protecting against data loss.
A transaction is an atomic operation which may not be divided into smaller operations.
Three transaction phases exist: build-up, during which the database operations are requested; prepare, during which the transaction is validated; commitment, during which the operations performed in the transaction are written to disk.
Read-only transactions have only two phases: build-up and prepare.
Transaction build-up may be started explicitly or implicitly, see Starting Transactions; prepare and commitment are both initiated explicitly through a request to commit the transaction (using COMMIT).
In interactive application programs, build-up takes place typically over a time period determined by the user, while prepare and commitment are part of the internal process of committing a transaction, which occurs on a time-scale determined by machine operations.
Optimistic Concurrency Control
Since Mimer SQL uses optimistic concurrency control (OCC), deadlocks never occur, see Locking for a further discussion of deadlocks. How optimistic concurrency control works in Mimer SQL is described below.
The transaction begins by taking a snapshot of the database in a consistent state. During build-up, changes requested to the contents of the database are kept in a write-set and are not visible to other users of the system. This allows the database to remain fully accessible to all users. The application program in which build-up occurs will see the database as though the changes had already been applied. Changes requested during transaction build-up become visible to other users when the transaction is successfully committed.
During build-up, a read-set records the state of the database as seen at the time of each operation (including intended changes). If the state of the database at commitment is inconsistent with the read-set, a conflict is reported and the transaction is rolled back (i.e. the write-set is erased and no changes are made to the database). This can happen if, for instance, a transaction updates a row which gets deleted by another user after build-up has started but before the transaction is committed. The application program is responsible for taking appropriate action if a transaction conflict occurs.
Concurrency Control Guidelines
Because of the nature of this concurrency control protocol, it is important that some of the implications are understood.
A transaction that exists for a long elapsed time has a greater chance of conflicting with changes made by other users than a transaction with a short elapsed time.
At the other extreme, an application that immediately commits every executed SQL statement will seldom meet any conflicts, but will incur unnecessary overhead.
In general:
•keep transactions as short as is reasonably possible
•keep interactive user dialogs outside of transactions
A common situation that can generate unnecessarily large read-sets is the following: an application program reads through the rows in a table in a loop construct, with a conditional exit to update a row on user intervention.
It is tempting to simply place a COMMIT after the update statement, for example:
EXEC SQL DECLARE c_1 CURSOR FOR SELECT…
loop
EXEC SQL FETCH c_1
INTO :VAR1, :VAR2, ..., :VARn;
display VAR1, VAR2, ..., VARn;
prompt "Update row?";
exit when ANSWER = "YES";
end loop;
EXEC SQL UPDATE table
SET ...
WHERE CURRENT OF c_1;
EXEC SQL COMMIT;
However, the FETCH loop can create a large read-set while waiting for the user update request, risking transaction conflict at the UPDATE.
A tempting solution for this problem might be:
EXEC SQL DECLARE c_1 CURSOR FOR SELECT…
loop
EXEC SQL FETCH c_1
INTO :VAR1, :VAR2, ..., :VARn;
display VAR1, VAR2, ..., VARn;
prompt "Update row?";
exit when ANSWER = "YES";
EXEC SQL ROLLBACK;
end loop;
EXEC SQL UPDATE table
SET ...
WHERE CURRENT OF c_1;
EXEC SQL COMMIT;
But since ROLLBACK closes all cursors, this will not work.
Instead, something like the following is a better approach:
EXEC SQL DECLARE c_1 CURSOR FOR SELECT…
EXEC SQL SET TRANSACTION READ ONLY;
loop
EXEC SQL FETCH c_1
INTO :VAR1, :VAR2, ..., :VARn;
display VAR1, VAR2, ..., VARn;
prompt "Update row?";
exit when ANSWER = "YES";
end loop;
EXEC SQL ROLLBACK;
EXEC SQL UPDATE table
SET ...
WHERE col1 = :VAR1,
col2 = :VAR2, ...
EXEC SQL COMMIT;
The risk of a transaction conflict in the final transaction is minimal, because both the size and duration of the transaction is minimized. The use of a read-only transaction can significantly improve the performance of the FETCH statements.
A number of changes are necessary if we want to loop over FETCH, UPDATE and COMMIT.
EXEC SQL DECLARE c_1 CURSOR WITH HOLD FOR SELECT…
loop
EXEC SQL FETCH c_1
INTO :VAR1, :VAR2, ..., :VARn;
display VAR1, VAR2, ..., VARn;
prompt "Update row?";
if ANSWER = "YES" then
EXEC SQL COMMIT;
EXEC SQL UPDATE table
SET ...
WHERE col1 = :VAR1,
col2 = :VAR2, ...
EXEC SQL COMMIT;
end if;
end loop;
The cursor is declared WITH HOLD in order to remain open and positioned after COMMIT.
COMMIT is used instead of ROLLBACK, since holdable cursors does not remain open after ROLLBACK.
The SET TRANSACTION statement is removed, because the existence of an open holdable cursor prohibits a change of transaction mode. The cursor cannot be accessed both in READ ONLY and in READ WRITE mode.
Deadlock situations, which can be relatively common in some database management systems where records are locked during transaction build-up, can not occur in Mimer SQL.
In Mimer SQL it is impossible for two processes to be waiting for a record locked by the other process. In some other database management systems this situation may require operator intervention to resolve the problem.
In any database system, at some stage in a transaction, the data records must be locked to prevent access by other processes and to ensure that the transaction is not interrupted. In the Mimer SQL system, no change is made to the database contents during the transaction build-up and no records are locked. This means that the database can be freely accessed (and updated) by any other process; the data accessed by the transaction is only locked during the commit phase. In this way, locks are held only for a very short period of time.
The problems associated with locking are further reduced since only those records that are actually to be updated are locked. Other data in the same table continues to be accessible to other transactions.
Changes made to a database may be logged, to provide back-up protection in the event of hardware failure, provided that the changes occur within a transaction and that the databanks involved have the LOG option. Transaction handling is, therefore, important even in standalone environments where concurrency control issues do not arise.
Options
Transaction control and logging is determined at the databank level by options set when the databank is defined. The options are:
All operations on the databank are performed under transaction control. All transactions are logged.
All operations on the databank are performed under transaction control. No transactions are logged.
All operations on the databank are performed without transaction control (even if they are requested within a transaction), and are not logged. Sets of operations (DELETE, UPDATE or INSERT on several rows) that are interrupted will not be rolled back.
Only read operations are allowed, i.e no UPDATE, DELETE or INSERT can be performed.
Note:All important databanks should be defined with LOG option, so that valuable data is not lost by any system failure.
The following sections discuss how system interruptions and hardware failure are handled.
System Interruptions
If a transaction build-up is interrupted by a system failure or a program termination (deliberate or otherwise) the transaction is aborted and none of the requested changes are made to the database.
Transactions which are interrupted after the request to commit, but before all operations in the transaction have been executed on the database, are completed by the automatic recovery functionality when the databank involved is next accessed. There is no possibility of transaction conflict in such an automatic completion, since no other process can access the affected data as long as an incomplete transaction is pending.
In the event of a system failure that interrupts one or more application programs, it may be necessary to manually examine the database contents to determine which transactions failed to commit before the interruption.
Hardware Failure
A databank that is damaged by hardware failure (e.g. a disk crash) may be recovered using back-up copies and the transaction log (LOGDB), provided that all write operations on the databank have been logged (and that the back-up copies and the LOGDB databank are intact).
Backup and restore facilities are described in the Mimer SQL System Management Handbook.
Transaction Control Statements
The following sections explain how to start, end and optimize transactions. You can also read about consistency and exception diagnostics in transactions. Further, transaction options, cursors and error handling are discussed.
Transaction start may be set to EXPLICIT or IMPLICIT.
The default transaction start setting is IMPLICIT, which means a transaction will be started automatically whenever one is needed.
To set the transaction start mode, use the statements:
SET TRANSACTION START EXPLICIT;
SET TRANSACTION START IMPLICIT;
Different database connections can use different transaction start options.
The START statement can always be used to explicitly start a transaction. This is useful if a number of related updates are to be performed and it is desirable that all the updates succeed or fail together to maintain consistency.
You cannot start a transaction while a transaction is already active.
With this setting, transactions are never automatically started. All transactions must be explicitly started by executing the START statement.
Any update operation (INSERT, UPDATE or DELETE) involving a table in a databank with the TRANS or LOG option must occur within a transaction. An error will be raised if such an update is attempted without first starting a transaction.
All the statements issued after the START statement and before the transaction is concluded are grouped together within that single transaction.
A transaction is concluded by executing a COMMIT or ROLLBACK statement.
With this setting, a transaction is started automatically (if one is not already active) by a reference to an object stored in a databank with the TRANS or LOG option (i.e. if none of the objects referenced are stored in a databank with the TRANS or LOG option, no transaction is required and therefore one is not started).
The START statement may be used to explicitly start a transaction if required, typically to allow several updates to be grouped together within a single transaction for consistency, as already described.
An automatically started transaction is concluded by executing a COMMIT or ROLLBACK statement.
All the statements issued after the initiating update and before the concluding COMMIT or ROLLBACK statement are grouped together within that single transaction.
Transactions must be ended with the COMMIT or ROLLBACK statement.
•COMMIT
This statement requests that the operations in the write-set are executed on the database, making the changes permanent and visible to other users. The SQLSTATE value returned when a COMMIT statement is executed indicates either that the transaction commitment was successful (SQLSTATE = '00000') or that a transaction conflict occurred (SQLSTATE <> '00000').
•ROLLBACK
This statement abandons the transaction. The read-set and write-set are dropped and no changes are made to the database. ROLLBACK is always successful.
Note:A transaction in Mimer SQL is never physically rolled-back in the sense of undoing changes made to the database, since changes are not actually effected until a successful COMMIT is performed. However, the ROLLBACK statement may free internal resources.
Transactions that are not successfully committed due to a transaction conflict do not have to be explicitly rolled back. The ROLLBACK statement is most commonly used in exception routines for handling error situations that are detected by the application during transaction build-up.
If a connection or program is terminated without requesting a COMMIT or ROLLBACK for the current transaction, the system will abort the transaction. None of the changes requested during the transaction build-up will be made to the database.
Transaction handling in BSQL differs slightly from that described here – see the Mimer SQL User's Manual, Handling Transactions, for details.
The following SET TRANSACTION options are used to optimize transaction performance:
•READ ONLY
This setting should always be used for transactions that do not require update access to the database. Significant performance gains can be achieved, especially for queries retrieving large numbers of rows, when this setting is used in queries when there is no need for update access to the database.
•READ WRITE
This setting should only be used for transactions that require update access to the database. This is the default setting for a transaction.
The default option is READ WRITE, or the option defined to be the default for the current session by using the SET SESSION statement, see Setting Default Transaction Options.
The SET TRANSACTION READ command only affects the single next transaction started after it is used.
Consistency Within Transactions
The SET TRANSACTION … ISOLATION LEVEL options can be used to control the degree to which the changes occurring within one transaction are affected by the changes occurring within other concurrently executing transactions.
The default option is REPEATABLE READ, or the option defined to be the default for the current session by using the SET SESSION statement, see Setting Default Transaction Options.
The SET TRANSACTION … ISOLATION LEVEL command only affects the single next transaction started after it is used.
Options
The following options are available:
•SERIALIZABLE
This setting guarantees that the end result of the operations performed by two or more concurrent transactions will be the same as if the transactions had been executed in a serial fashion, where one executes to completion before the other starts.
•REPEATABLE READ
This setting offers the same consistency guarantee as serializable, except that the concurrency effect known as phantoms may be encountered (see below for a reference to the definition of this concurrency effect).
•READ COMMITTED
This setting offers the same consistency guarantee as repeatable read, except that the concurrency effect known as non-repeatable read may also be encountered (see below for a reference to the definition of this concurrency effect).
•READ UNCOMMITTED
This setting offers the same consistency guarantee as read committed, except that the concurrency effect known as dirty read may also be encountered (see below for a reference to the definition of this concurrency effect).
For a definition of the concurrency effects mentioned above (phantoms, non-repeatable read and dirty read) refer to the Mimer SQL Reference Manual, SET TRANSACTION.
All of the isolation level settings guarantee that each transaction will be executed completely or not at all and that no updates will be lost.
Exception Diagnostics Within Transactions
The SET TRANSACTION DIAGNOSTICS SIZE option allows the size of the diagnostics area to be defined. An unsigned integer value specifies how many exceptions can be stacked in the diagnostics area, and examined by GET DIAGNOSTICS, see the Mimer SQL Reference Manual, GET DIAGNOSTICS, in situations where repeated RESIGNAL operations have effectively been performed.
The SET TRANSACTION DIAGNOSTICS SIZE setting only affects the single next transaction to be started.
The default SET TRANSACTION DIAGNOSTICS SIZE setting (50 or whatever has been defined to be the default by using SET SESSION) applies unless an alternative is explicitly set before each transaction.
Setting Default Transaction Options
The SET SESSION statement can be used to define the default settings for the transaction options set by SET TRANSACTION READ, SET TRANSACTION ISOLATION LEVEL and SET TRANSACTION DIAGNOSTICS SIZE.
As these SET TRANSACTION commands only affect the single next transaction started after they are used, it is often convenient to define the desired default options for each of them.
A detailed description of the SET SESSION statement can be found in the Mimer SQL Reference Manual.
Statements in Transactions
The tables that follow summarize whether statements may or may not be used inside transactions.
Access Control Statements
|
Statements |
Allowed |
Comments |
|---|---|---|
|
GRANT REVOKE |
Yes |
Must be the only statement in a transaction. |
Connection Statements
|
Statements |
Allowed |
Comments |
|---|---|---|
|
CONNECT SET CONNECTION |
Yes |
|
|
DISCONNECT |
Yes |
A ROLLBACK is performed on any active transaction. |
|
ENTER LEAVE (program ident) |
No |
|
Data Definition Statements
|
Statements |
Allowed |
Comments |
|---|---|---|
|
ALTER COMMENT CREATE DROP |
Yes |
Must be the only statement in a transaction. |
Data Manipulation Statements
|
Statements |
Allowed |
Comments |
|---|---|---|
|
SELECT EXPRESSION SELECT INTO FETCH INSERT DELETE DELETE CURRENT UPDATE UPDATE CURRENT |
Yes |
|
|
OPEN CLOSE |
Yes |
ROLLBACK closes all open cursors. COMMIT closes all open non-holdable cursors. |
Declarative Statements
|
Statements |
Allowed |
Comments |
|---|---|---|
|
DECLARE CONDITION DECLARE CURSOR DECLARE HANDLER DECLARE VARIABLE |
Not applicable |
Declarative statement |
Diagnostic Statements
|
Statements |
Allowed |
Comments |
|---|---|---|
|
GET DIAGNOSTICS RESIGNAL SIGNAL |
Yes |
|
Dynamic SQL Statements
|
Statements |
Allowed |
Comments |
|---|---|---|
|
PREPARE DESCRIBE EXECUTE EXECUTE IMMEDIATE EXECUTE STATEMENT ALLOCATE CURSOR ALLOCATE DESCRIPTOR DEALLOCATE DESCRIPTOR DEALLOCATE PREPARE GET DESCRIPTOR SET DESCRIPTOR |
Yes |
See Dynamic SQL. |
ESQL Control Statements
|
Statements |
Allowed |
Comments |
|---|---|---|
|
DECLARE SECTION WHENEVER |
Not applicable |
Declarative statement |
Procedure Control Statements
|
Statements |
Allowed |
Comments |
|---|---|---|
|
CALL CASE COMPOUND STATEMENT FOR IF ITERATE LEAVE LOOP REPEAT RETURN SET WHILE |
Yes |
|
System Administration Statements
|
Statements |
Allowed |
Comments |
|---|---|---|
|
ALTER DATABANK RESTORE ALTER DATABASE DELETE STATISTICS CREATE BACKUP SET DATABANK SET DATABASE SET SHADOW UPDATE STATISTICS |
No |
These statements create internal transactions to ensure data dictionary consistency |
Transaction Control Statements
|
Statements |
Allowed |
Comments |
|---|---|---|
|
SET SESSION SET TRANSACTION START |
No |
These statements control transaction behavior |
|
COMMIT ROLLBACK |
Yes |
|
A cursor open by the current connection may be closed implicitly by one of the transaction terminating statements COMMIT and ROLLBACK. ROLLBACK closes all open cursors for the current connection. COMMIT closes all open cursors for the current connection, except cursors declared WITH HOLD. Holdable cursors remain open after COMMIT.
When a stacked cursor is closed, all instances of the cursor are closed.
Cursors are also closed implicitly by LEAVE and DISCONNECT. In SET TRANSACTION START EXPLICIT mode, cursors may be opened and used outside transactions. Such cursors remain accessible when an ENTER statement is issued, and remain open when a LEAVE statement is issued.
This is illustrated in the following statement sequence:
...
EXEC SQL SET TRANSACTION START EXPLICIT;
EXEC SQL DECLARE c_1 CURSOR FOR SELECT col1
FROM tab1;
EXEC SQL DECLARE c_2 CURSOR FOR SELECT col2
FROM tab2
WHERE checkcol = :VAR1;
EXEC SQL OPEN c_1;
loop
EXEC SQL FETCH c_1
INTO :VAR1; -- Fetch value from tab1
EXEC SQL ENTER ... ; -- Change current ident
EXEC SQL OPEN c_2;
EXEC SQL FETCH c_2
INTO ...; -- Fetch row for c_2
EXEC SQL CLOSE c_2;
EXEC SQL LEAVE;
end loop;
...
In the above example, the value fetched for the cursor C1 is used to determine the set of rows addressed by cursor C2. Cursor C1 remains open and positioned during the ENTER … LEAVE sequence.
Each time the loop is executed, a new value is fetched by C1 and a new set of rows is addressed by C2. The same behavior applies when LEAVE RETAIN is used to leave a PROGRAM ident but keep the environment for the ident.
A cursor opened and used outside a transaction may however not be used within a transaction. If the same cursor is required outside and inside a transaction, separate instances must be opened. Remember that separate instances of a cursor address separate result sets:
...
EXEC SQL SET TRANSACTION START EXPLICIT;
EXEC SQL DECLARE c_1 REOPENABLE CURSOR FOR SELECT col1
FROM tab1;
EXEC SQL OPEN c_1;
EXEC SQL FETCH c_1
INTO ...; -- First row (outside transaction)
...
EXEC SQL START;
EXEC SQL OPEN c_1; -- New instance of cursor
EXEC SQL FETCH c_1
INTO ...; -- First row again
...
Error Handling in Transactions
In general, errors and exception conditions are reported in SQLSTATE after each executable SQL statement.
The value of SQLSTATE indicates the outcome of the preceding statement, see SQLSTATE Return Codes for a list of SQLSTATE values.
GET DIAGNOSTICS can be used to get detailed status information after an SQL statement.
The value of SQLSTATE after a COMMIT statement indicates the success or failure of the request to commit the transaction, not the outcome of any data manipulations performed within the transaction.
About WHENEVER
Use of the general error handling statement WHENEVER, see the Mimer SQL Reference Manual, WHENEVER, for a description) in transactions requires some care:
•Program control can be transferred to an exception routine in the event of an error. Make sure that the exception routine is designed to take care of uncompleted transactions.
Most commonly, the first SQL statement in the exception routine should be GET DIAGNOSTICS. The exception routine should normally also execute a ROLLBACK statement. Remember that if the exception routine is used from a statement outside a transaction, any open cursors belonging to the current ident will be closed by the ROLLBACK statement. GET DIAGNOSTICS can be used to determine whether or not a transaction is active.
•For transaction conflict, the SQLSTATE value returned from the COMMIT statement falls into the SQLEXCEPTION class. If the transaction is to be retried in the event of conflict, make sure that no WHENEVER SQLEXCEPTION GOTO exception statement is operative.
If WHENEVER error handling is used in an application program, a suitable program structure for COMMIT statements is:
EXEC SQL WHENEVER SQLEXCEPTION GOTO exception;
...
EXEC SQL WHENEVER SQLEXCEPTION GOTO retry;
EXEC SQL COMMIT;
EXEC SQL WHENEVER SQLEXCEPTION GOTO exception;
...