602SQL Documentation Index  
<­Cursors in SQL UpSQL Language in 602SQL Processing Errors and Exceptions in SQL­>

Transactions and Savepoints (SQL)

Transactions bind database operations into atomic units. The operations joined in a transaction may be either:

Transactions are used if a group of operations in the database closely relates together and we need to avoid the situation when one part of the operation is performed and another is not. A common example is money transfer from one account to another. It is necessary to subtract the amount from the first account and add it to the second.

Modifications performed after a transaction are executed are stored into the internal transaction log. This log is initially in the operating memory and is stored in the transact.fil file for larger transactions. Therefore, the database still preserves the original values until the transaction is committed. If transaction security is enabled (see Protection by transactions and transaction security) the entire transaction contents will be saved to disk in order to prevent data loss due to server termination (the transaction will be finished when the server is started again).

Controlling Transactions when Sending Statements from a Client

Each statement (that affects the data) is executed as a single transaction when a client is connected to a server. If the statement is successful, it will be committed, otherwise it will be rolled back. These transactions are called default (this mode is called autocommit).

If the client executes the START TRANSACTION SQL statement or calls the Start_transaction API function, an explicit transaction that binds the execution of the following statements, is opened. This transaction is then either confirmed by the client using the COMMIT SQL statement or the Commit API function, or undone using the ROLLBACK SQL statement or the Roll_back API function. The mode of statement execution returns back to the autocommit mode once the transaction is either committed or undone, until another explicit transaction will begin.

Errors in Explicit Transaction

If a serious error occurs when executing statements in an explicit transaction, a default rollback will occur. According to the SQLOPT_ERROR_STOPS_TRANS compatibility attribute setting the transaction is then rolled back immediately or is running until explicit termination and is rolled back then.

The transaction is automatically rolled back in the first case and the mode of statement execution returns back to the autocommit mode. The client therefore may not continue sending the remaining statements of the recalled transaction, since the statements would be executed as a single transaction (and therefore would not be rolled back).

The default rollback also causes a call to the cd_Break_user and cd_disconnect client API functions.

Forced Explicit Transaction Termination

There are some special actions that always commit an explicit transaction (default commit) regardless of the original transaction structure. It is the actions that should not be rolled back no matter what. These actions are the Delete_all_records, Free_deleted, Backup_database_file, Compact_table, Compact_database, Database_integrity, Move_data, Login, Logout and Set_property_value API functions for the SERVER_KEY property. A commit is also performed before or after these SQL statements: DROP TABLE, ALTER TABLE, CREATE INDEX, DROP INDEX, DROP SCHEMA, ALTER DOMAIN, CREATE SEQUENCE, ALTER SEQUENCE, CREATE FULLTEXT, DROP FULLTEXT, ALTER FULLTEXT, and RENAME. A commit is also performed when calling functions for retrieving e-mail that work with system tables (MailBoxLoad, MailBoxGetFilInfo, MailBoxGetMsgEx, MailBoxGetMsg, MailBoxSaveFileDBs, or MailBoxSaveFileDBr).

Transaction in the 602SQL Client

It is possible to open an explicit transaction for the 602SQL Client using the SQL console, but it is not recommend, since the user has no control on transaction termination.

Controlling Transactions in Procedures Executed on Server

When a client executes a (default) procedure on a server, the entire procedure is performed as a single transaction. This includes actions executed as a result of statement execution, such as triggers or active referential integrity actions.

If you need to split this action into many (default) transactions, you will need to put the COMMIT statement into this order. The transaction will be committed by doing so, and a new transaction will be automatically opened.

You may control these actions more precisely with explicit transactions. An explicit transaction may still be active even when a procedure is terminated. Explicit transactions cannot be used in triggers.

More Transaction Properties

A rollback does not allow you to recall local variable value assignment or the effect of executed external routines (such as sending an e-mail or writing to a log).

Transactions cannot be nested. Transaction nesting results in an error.

It may be useful for debugging purposes to trace default rollback execution because of an error (situation TRACE_IMPL_ROLLBACK ).

The use of transactions is closely related to database protection against integrity violation.

The client functions Transaction_open and Rolled_back provide information about transactions and transaction errors. Transaction state of any thread may be retrieved using the system query _iv_logged_users (Transaction_open column).

Savepoints

There is another method to recall a group of executed statements and return to the state before these statements were executed apart from transactions. This method is called a savepoint.

The SAVEPOINT statement creates a savepoint inside a transaction. The ROLLBACK TO SAVEPOINT statement executed after the execution of other statements returns the database state to the point when the SAVEPOINT statement was executed (the transaction is not terminated!).

Variations from the Intermediate level to the Full level (SQL 3)

List of topics:

<­Cursors in SQL UpSQL Language in 602SQL Processing Errors and Exceptions in SQL­>