MySQueaL Resources

SET TRANSACTION Statement

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 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.

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

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