SET TRANSACTION ISOLATION

>>--EXEC SQL--SET TRANSACTION ISOLATION------------------->

 >-------.-READ UNCOMMITTED-.---------END-EXEC------------><
         +-READ COMMITTED---+
         +-REPEATABLE READ--+
         +-SERIALIZABLE-----+ 

Comments:

Transactions can affect each other in the following ways, depending on the setting of the transaction isolation level:

• Dirty read - Transaction 1 updates a row. Transaction 2 reads the row before transaction 1 commits. Transaction 1 issues a rollback. Transaction 2's results are based on invalid data.
• Nonrepeatable read - Transaction 1 reads a row. Transaction 2 updates or deletes the row and commits the change. If transaction 1 re-reads the row, it will retrieve different values, or may not be able to re-read the row.
• Phantom - Transaction 1 reads a set of rows using a select with a where clause. Transaction 2 inserts a row that satisfies the where clause. If transaction 1 repeats the select, it will read a different set of rows.

These situations can be controlled by locking, which means that a transaction might have to wait until another transaction completes, which limits concurrency (sometimes called pessimistic concurrency), or by forcing a transaction to rollback if the situation occurs, which has less of an impact on concurrency but may force work to be repeated (this is sometimes called optimistic concurrency).

In READ UNCOMMITED mode, dirty reads, nonrepeatable reads and phantoms are all possible.

In READ COMMITED mode, dirty reads are not possible but nonrepeatable reads and phantoms are.

In REPEATABLE READ mode, dirty reads and nonrepeatable reads are not possible, but phantoms are.

In SERIALIZABLE mode dirty reads, nonrepeatable reads and phantoms are all impossible.

Note:

A driver might not support all the isolation levels defined by ODBC. If you set a mode that the driver does not support, SQLCODE and SQLSTATE are set accordingly.

EXEC SQL SET TRANSACTION ISOLATION READ UNCOMMITTED END-EXEC