Overriding Default Transaction Behavior

For a particular transaction, you can override one or more default transaction behaviors by specifying START TRANSACTION (see START TRANSACTION). Specification of START TRANSACTION 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 setting (see Supported Transaction Isolation Levels):

START TRANSACTION;

To override the default read-write transaction behavior but use the default transaction isolation setting, specify the following statement. This statement starts an explicit transaction, which suspends autocommit behavior, and starts a read-only transaction that uses the default transaction isolation setting:

START TRANSACTION READ ONLY;

Suppose that the default transaction isolation level is WRITE COMMITTED and you want to override it. Specify a statement such as the following. This 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;

Finally, you can override both the default read-write behavior and the default transaction isolation level. For example, suppose the default transaction isolation level is CONSISTENT READ. 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