Overriding Default Transaction Behavior

For a particular transaction, you can override one or more default transaction behaviors by executing the START TRANSACTION statement (see START TRANSACTION). The START TRANSACTION statement starts an explicit transaction ( see About Implicit and Explicit Transactions), which always suspends autocommit behavior. For example, the following statement starts an explicit transaction, which suspends autocommit behavior, uses the default read-write transaction behavior, and uses the default transaction isolation.

START TRANSACTION;

To override the default read-write transaction behavior, execute the following statement:

START TRANSACTION READ ONLY;

This statement starts an explicit transaction, which suspends autocommit behavior, and starts a read-only transaction that uses the default transaction isolation level.

The default transaction isolation level can be explicitly overwritten for a transaction by specifying the desired level in the START TRANSACTION statement. The following statement starts an explicit transaction, which suspends autocommit behavior, and starts a read-write transaction that uses the READ COMMITTED transaction isolation level:

START TRANSACTION ISOLATION LEVEL READ COMMITTED;

The following statement starts an explicit transaction, which suspends autocommit behavior, and starts a read-only transaction that uses the READ COMMITTED transaction isolation level:

START TRANSACTION READ ONLY ISOLATION LEVEL READ COMMITTED;

When using explicit transactions, you must specify COMMIT or ROLLBACK to end the transaction (see About Implicit and Explicit Transactions