Transactions

It is very important that any database management system (DBMS) ensures that the data that is stored in a database satisfies the ACID criteria: Atomicity, Consistency, Isolation, and Durability. Atomicity means that a set of interrelated database modifications all be made together at the same time. If one modification from the set fails then all fail. Consistency means that a database never contains errant data or relationships and that a transaction always transforms the database from one consistent state into another. Consistency is something that is primarily the responsibility of the application because the database cannot be certain that all of the necessary modifications have been properly included in any given transaction. In RaimaDB SQL, consistency rules are specified through the foreign and primary key declarations and RaimaDB SQL does ensure that those relationships are consistent. Isolation means that the changes that are being made during a transaction are only visible to the user (program task) making them. Not until the transaction's changes have been committed to the database are other users (tasks) able to see them. Durability refers to the DBMS's ability to ensure that the changes made by all transactions that have committed survive any kind of system failure.

The work necessary to ensure that a DBMS supports "ACIDicity" makes it among the most complex of all system software components. The challenge being to maintain ACIDicity and yet allow the database data to be easily accessed by as many users as possible, as fast as possible. However, there is an unavoidable and severe negative performance impact caused by the need to maintain an ACID compliant database. When enforcement of these properties is relaxed, data can be updated and accessed much more quickly but the consistency and integrity of the data will certainly be impaired should a system failure occur.

Three statements are used for transaction processing. The START TRANSACTION statement does just that. The COMMIT statement will write to the database all of the changes made since the last START TRANSACTION. The ROLLBACK statement will undo all of the changes made since the last START TRANSACTION. The syntax for each of these statements is shown below.

start_stmt:
          START TRANSACTION [transid_name] [transact_item [, transact_item]...]

transact_item:
          {READ {ONLY | WRITE} | UPDATE} [ON table_list]
     |    isolation level iso_level

table_list:
          database_name.]table_name [, [database_name.]table_name]...

iso_level:
          READ UNCOMMITTED
    |     READ COMMITED
    |     REPEATABLE READ
    |     SERIALIZABLE

If no START TRANSACTION statement has been executed prior to the execution of an INSERT, UPDATE, or DELETE statement, the system will automatically start a transaction for you.

RaimaDB SQL currently only supports the SERIALIZABLE isolation level. You can specified any of the other SQL standard allowed levels but it will not change how RaimaDB SQL processes transactions.

The START TRANSACTION statement also allows you to specify the tables which are to be locked. This non-standard feature allows you to specify which tables will be used within the transaction so that they can all be locked at one time. This prevents deadlock situations from arising. Should any of the requested table locks not be granted, error code eSQL_TIMEOUT (SQLSTATE “HYT00”) will be returned.

Issuing a START TRANSACTION when a transaction is already active is not allowed.

If autocommit is enabled (via the SET TRANSACTION statement), the execution of a START TRANSACTION will disable autocommit until the next COMMIT or ROLLBACK is executed.