SET TRANSACTION

Use this MySQL statement to set an isolation level for the current transaction, for a transaction that's about to be started, or globally.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

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

Explanation

Use this MySQL 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 MySQL session. Use GLOBAL to set it for all subsequent transactions (which 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 MySQL statement applies only to InnoDB tables.

The level READ UNCOMMITTED is known as a dirty read because SELECT statements are executed in a non-locking 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.

Examples

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

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
...