MySQL in a Nutshell, 2nd Edition

Name

SET TRANSACTION

Synopsis

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE}

Use this statement to set an isolation level for the current transaction, for a transaction that’s about to be started, or globally. Use the keyword SESSION to set the level for the current session. Use GLOBAL to set it for all subsequent transactions (this does not affect existing ones). If neither of these two keywords is included, the level is set for the next transaction of the current session. This statement applies only to InnoDB tables at this time.

The level READ UNCOMMITTED is known as a dirty read because SELECT statements are executed in a nonlocking manner. Thus, queries by one transaction can be affected by ongoing, uncommitted updates in another transaction, or old data may be used, thus making the results inconsistent. READ COMMITTED is a more consistent read, similar to Oracle’s isolation level. However, changes that are committed in one transaction will be visible to another. The result is that the same query in the same transaction could return different results.

REPEATABLE READ is the default. It makes all reads consistent for a transaction.

In the safest level, SERIALIZABLE, changes are not allowed in other transactions if a transaction has executed a simple SELECT statement. Basically, queries are performed with LOCK IN SHARE MODE.

Here is an example of how you can use this statement:

SET SESSION TRANSACTION ISOLATION LEVEL READ ...