 |
|
|
 | Problems when Combining Explicit Lock Placing and Transaction Isolation |  | Transactions and Savepoints | COMMIT Statement |  |
statement_START_TRANSACTION ::= START TRANSACTION [ isolation ] [, transaction_type ]
isolation ::= ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
transaction_type ::= READ ONLY | READ WRITEThis statement executes a new explicit transaction and specifies the transaction type and the isolation level. The transaction is terminated either explicitly (using the COMMIT or ROLLBACK statements), by a default commit (e.g. for ALTER TABLE) or default rollback (usually when an error occurs).
The START TRANSACTION statement always specifies the isolation level, and this level is used until a new setting is applied (even for default transactions). If the isolation is not specified, SERIALIZABLE will be used. If the transaction type is not specified, READ ONLY will be used if the isolation level is READ UNCOMMITTED, otherwise READ WRITE will be used.
If this statement is executed and an explicit transaction is already in progress (e.g. as a part of a stored procedure that executes the transaction itself), an error sqlstate 25001 occurs. If the transaction type is READ ONLY, you will not be able to execute the UPDATE, DELETE and INSERT statements in the transaction, otherwise an error sqlstate 25006 occurs.
The 602SQL Server internally implements the READ COMMITTED and SERIALIZABLE isolation levels only. When you set READ UNCOMMITTED level, READ COMMITTED is set internally. When you set REPEATABLE READ level, SERIALIZABLE is set internally. This behaviour does not violate the SQL standard.
Variations from the SQL Standard
| Problems when Combining Explicit Lock Placing and Transaction Isolation | | Transactions and Savepoints | COMMIT Statement | |