Summary:
See also: Connections, Static SQL, Dynamic SQL, Result Sets, SQL Errors, Programs.
A Database Transaction delimits a set of database operations that are processed as a whole. Database operations included inside a transaction are validated or canceled as a unique operation.
The database server is in charge of Data Concurrency and Data Consistency control. Data Concurrency control allows the simultaneous access of the same data by many users, while Data Consistency control gives each user a consistent view of the database.
Without adequate concurrency and consistency controls, data could be changed improperly, compromising data integrity. If you want to write applications that can work with different kinds of database servers, you must adapt the program logic to the behavior of the database servers regarding concurrency and consistency management. This requires good knowledge of multi-user application programming, transactions, locking mechanisms, isolation levels and wait mode. If you are not familiar with these concepts, carefully read the documentation of each database server that covers this subject.
Usually, database servers set exclusive locks on rows that are modified or deleted inside a transaction. These locks are held until the end of the transaction to control concurrent access to that data. Some database servers like Oracle implement row versioning (before modifying a row, the server makes a copy). This technique allows readers to see a consistent copy of the rows that are updated during a transaction not yet committed. When the isolation level is high (Repeatable Read) or when using a SELECT FOR UPDATE statement, the database server sets shared locks on read rows to prevent other users from changing the data fetched by the reader. Again, these locks are held until the end of the transaction. Some database servers like Informix allow read locks to be held regardless of the transactions (WITH HOLD cursor option), but this is not a standard.
Processes accessing the database can change transaction parameters such as the isolation level or lock wait mode. The main problem is to find a configuration which results in similar behavior on every database engine. Programs using Informix-specific behavior must be adapted to work with other database servers.
Here is the recommended configuration to get common behavior with all kinds of database engines:
When using this configuration, the locking granularity does not have to be set at the row level. For example, to improve performance with Informix databases, you can use the "LOCK MODE PAGE" locking level, which is the default.
A lot of applications have been developed for old Informix SE databases that do not manage transaction logging. These applications often work in the default lock wait mode which is "NOT WAIT". Additionally, applications using databases without transactions usually do not change the isolation level, which defaults to "Dirty Read". You must review the program logic of these applications in order to conform to the portable configuration.
Recent database engines support transaction savepoints, which allowing to set markers in the current transaction, in order to rollback to a specific point without canceling the complete transaction. Genero BDL supports savepoint instructions to be compliant to IBM Informix IDS. You should pay attention to SQL compatibility when writing applications for different database server types.
To write portable SQL applications, programmers use the instructions described in this section to delimit transaction blocks and define concurrency parameters such as the isolation level and the lock wait mode. At runtime, the database driver generates the appropriate SQL commands to be used with the target database server.
If you initiate a transaction with a BEGIN WORK statement, you must issue a COMMIT WORK statement at the end of the transaction. If you fail to issue the COMMIT WORK statement, the database server rolls back any modifications that the transaction made to the database. If you do not issue a BEGIN WORK statement to start a transaction, each statement executes within its own transaction. These single-statement transactions do not require either a BEGIN WORK statement or a COMMIT WORK statement.
For historical reasons, the language is based on IBM Informix SQL language, which defines the transaction management instructions. IBM Informix database servers can work in different transaction logging modes:
The first mode does not allow transaction management and should be avoided. In the second and third modes, you can use the BEGIN WORK, COMMIT WORK and ROLLBACK WORK statements. In ANSI mode, you can only use the COMMIT and ROLLBACK statements, because transactions are implicit.
When using Informix databases, the type of logging defines the way you manage transactions in your programs. For example, when using an ANSI-compliant Informix database, you do not have to start transactions with BEGIN WORK, since these are implicit.
When using the Standard Database Interface (SDI) architecture, you are free to use any type of transaction logging with Informix databases. When using the Open Database Interface (ODI) architecture you are free to use the native transaction management statements supported by the underlying database server, but it is recommended that you follow the default (native) Informix logging, by using BEGIN WORK, COMMIT WORK and ROLLBACK WORK to manage transactions. At runtime, the database drivers can manage the execution of the appropriate instructions for the target database server. This allows you to use the same source code for different kinds of database servers.
The instructions described in this section must be executed as Static SQL statements. Even if it is supported by the Informix API, it is not recommended that you use the Dynamic SQL instructions to PREPARE and EXECUTE transaction management statements, because it can result in unexpected behavior when using other database servers.
Starts a database transaction in the current connection.
BEGIN WORK
Some database servers do not support a Data Definition Language statement (like CREATE TABLE) inside transactions, or even auto-commit the transaction when such a statement is executed. Therefore, it is strongly recommended that you avoid DDL statements inside transactions.
A transaction that contains statements that affect many rows can exceed the limits that your operating system or the database server configuration imposes on the maximum number of simultaneous locks.Include a limited number of SQL operations in a transaction to execute short transactions. In a standard database session configuration (wait mode), it is not recommended that you have a transaction block running a long time, since it may block concurrent processes which want to access the same data.
The next code example starts a transaction block, inserts a row and updates the row, then commits the transaction. To other users, the INSERT and UPDATE instruction will be seen as an single atomic database modification:
01MAIN02DATABASE stock03BEGIN WORK04INSERT INTO items VALUES ( ... )05UPDATE items SET ...06COMMIT WORK07END MAIN
Defines or resets the position of a rollback point in the current transaction.
SAVEPOINT spname [UNIQUE]
The SAVEPOINT instruction declares a new rollback label at the current position in the lexical order within the current transaction. After defining a savepoint, you can rollback to the specified point in the transaction by using the ROLLBACK WORK TO SAVEPOINT instruction.
If the same savepoint name was used in a prior SAVEPOINT instruction, the previous savepoint is destroyed and the name is reused to flag the new rollback position. The optional UNIQUE keyword specifies that you do not want to reuse the same savepoint name in a subsequent SAVEPOINT instruction. Re-using the same name after a SAVEPOINT spname UNIQUE will raise an SQL error.
In the next example, a first savepoint is defined before the INSERT statement, then reset before the UPDATE statement. The ROLLBACK TO SAVEPOINT instruction will cancel the UPDATE statement only:
01MAIN02DATABASE stock03BEGIN WORK04DELETE FROM items05SAVEPOINT sp106INSERT INTO items VALUES ( ... )07SAVEPOINT sp1 -- releases previous savepoint named sp108UPDATE items SET ...09ROLLBACK WORK TO SAVEPOINT sp110COMMIT WORK11END MAIN
Validates and terminates a database transaction in the current connection.
COMMIT WORK
Cancels and terminates a database transaction in the current connection.
ROLLBACK WORK [TO SAVEPOINT [spname]]
Normally, the ROLLBACK WORK statement closes all cursors not declared with the WITH HOLD option. This is not the case with some databases like IBM DB2, which closes all kind of cursors when doing a ROLLBACK.
When specifying a savepoint with the TO SAVEPOINT clause, all SQL statements executed since the specified savepoint will be canceled. The transaction is not canceled, however, and you can continue to execute other SQL statements.
The following example checks for a potential SQL error after the DELETE statement and cancels the complete transaction with a ROLLBACK instruction:
01MAIN02DATABASE stock03WHENEVER ERROR CONTINUE04BEGIN WORK05INSERT INTO orders_hist VALUES ( ... )06DELETE FROM orders WHERE ...07IF SQLCA.SQLCODE < 0 THEN08ROLLBACK WORK09ELSE10COMMIT WORK11END IF12END MAIN
Destroys the specified savepoint in the current transaction.
RELEASE SAVEPOINT spname
In the next example, the RELEASE SAVEPOINT instruction cancels the UPDATE and INSERT statements and destroys the sp1 and sp2 savepoints. Only the DELETE statement will take effect at the end of the transaction:
01MAIN02DATABASE stock03BEGIN WORK04DELETE FROM items05SAVEPOINT sp106INSERT INTO items VALUES ( ... )07SAVEPOINT sp208UPDATE items SET ...09RELEASE SAVEPOINT sp110ROLLBACK WORK TO SAVEPOINT11COMMIT WORK12END MAIN
Defines the transaction isolation level for the current connection.
SET ISOLATION TO
{ DIRTY READ
| COMMITTED READ [LAST COMMITTED] [RETAIN UPDATE LOCKS]
| CURSOR STABILITY
| REPEATABLE READ }
When using a non-Informix database, the ODI driver executes the native SQL statement that corresponds to the specified isolation level.
When using the DIRTY READ isolation level, the database server might return a phantom row, which is an uncommitted row that was inserted or modified within a transaction that has subsequently rolled back. No other isolation level allows access to a phantom row.On most database servers, the default isolation level is usually COMMITTED READ, which is appropriate to portable database programming. Therefore, we do not recommend that you change the isolation level.
The LAST COMMITTED and RETAIN UPDATE LOCKS options have been added to the language syntax for conformance with Informix IDS 11. Note that the LAST COMMITTED option can be turned on implicitly with a server configuration parameter, saving un-necessary code changes.
The next example defines a typical committed read isolation level (the default, in most database servers):
01MAIN02DATABASE stock03SET ISOLATION TO COMMITTED READ04...05END MAIN
Defines the behavior of the program that tries to access a locked row or table.
SET LOCK MODE TO { NOT WAIT | WAIT
[ seconds ] }
When possible, the underlying database driver sets the corresponding connection parameter to define the timeout for lock acquisition. But some database servers may not support setting the lock timeout parameter. In this case, the runtime system generates an exception.
When using the NOT WAIT clause, the timeout is set to zero. If the resource is locked, the database server ends the operation immediately and returns an SQL Error.
seconds defines the number of seconds to wait for lock acquisition. If the resource is locked, the database server ends the operation after the elapsed time and returns an SQL Error.
When using the WAIT clause without a number of seconds, the database server waits for lock acquisition for an infinite time.
On most database servers, the default is to wait for locks to be released.
Make sure that the database server and corresponding database driver both support a lock acquisition timeout option, otherwise the program will raise an exception. For example, the IBM DB2 V8.1 database server does not support this option at the application level (only a global DB server option).
The following example sets the typical lock wait mode (not the default in IBM Informix):
01MAIN02DATABASE stock03SET LOCK MODE TO WAIT04...05END MAIN