start transaction

Start a transaction

Syntax

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

Description

The START TRANSACTION statement does just that: it begins a transaction. A transaction is defined as a group of related database changes that are either committed (made permanent) or rolled-back (discarded) as a group. This is necessary in order to maintain the logical consistency of the database content in case the system fails (e.g., power failure) in the middle of the transaction. All database changes (INSERT, UPDATE, DELETE statement executions) made after START are written in a single atomic operation upon execution of the COMMIT statement. The changes made after START TRANSACTION can be discarded (e.g., in the event of a user input error) upon execution of the ROLLBACK statement. Note that SQL will automatically start a transaction upon execution of the first INSERT, UPDATE, or DELETE statement where a START TRANSACTION has not already been executed.

The ISOLATION LEVEL clause is provided for future use and to conform to the SQL standard. Note, however, that currently only SERIALIZABLE is supported regardless of which isolation level is specified.

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, the execution of a START TRANSACTION will disable autocommit until the next COMMIT or ROLLBACK is executed.

Example

...connection alpha...
START TRANSACTION READ ONLY;
    ... issue a series of select statements
    
...meanwhile, over at connection omega...
START TRANSACTION;
    ... issue a series of related insert, update, and delete statements
commit;   -- alpha cannot see omega's changes

...back at alpha...
commit;   -- ends alpha's read only transactions
    ... subsequent reads can now see omega's changes