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

This statement sets the transaction isolation level, used for
operations on InnoDB tables.

Scope of the Isolation Level

You can set the isolation level globally, for the current session, or
for the next transaction:

o With the GLOBAL keyword, the statement sets the default transaction
  level globally for all subsequent sessions. Existing sessions are
  unaffected.

o With the SESSION keyword, the statement sets the default transaction
  level for all subsequent transactions performed within the current
  session.

o Without any SESSION or GLOBAL keyword, the statement sets the
  isolation level for the next (not started) transaction performed
  within the current session. Subsequent transactions revert to using
  the SESSION isolation level.

A change to the global default isolation level requires the SUPER
privilege. Any session is free to change its session isolation level
(even in the middle of a transaction), or the isolation level for its
next transaction.

SET TRANSACTION ISOLATION LEVEL without GLOBAL or SESSION is not
permitted while there is an active transaction:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.02 sec)

mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ERROR 1568 (25001): Transaction isolation level can't be changed
while a transaction is in progress

To set the global default isolation level at server startup, use the
--transaction-isolation=level option to mysqld on the command line or
in an option file. Values of level for this option use dashes rather
than spaces, so the permissible values are READ-UNCOMMITTED,
READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE. For example, to set
the default isolation level to REPEATABLE READ, use these lines in the
[mysqld] section of an option file:

[mysqld]
transaction-isolation = REPEATABLE-READ

It is possible to check or set the global and session transaction
isolation levels at runtime by using the tx_isolation system variable:

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
SET GLOBAL tx_isolation='REPEATABLE-READ';
SET SESSION tx_isolation='SERIALIZABLE';

Details and Usage of Isolation Levels

InnoDB supports each of the transaction isolation levels described here
using different locking strategies. You can enforce a high degree of
consistency with the default REPEATABLE READ level, for operations on
crucial data where ACID compliance is important. Or you can relax the
consistency rules with READ COMMITTED or even READ UNCOMMITTED, in
situations such as bulk reporting where precise consistency and
repeatable results are less important than minimizing the amount of
overhead for locking. SERIALIZABLE enforces even stricter rules than
REPEATABLE READ, and is used mainly in specialized situations, such as
with XA transactions and for troubleshooting issues with concurrency
and deadlocks.

For full information about how these isolation levels work with InnoDB
transactions, see
http://dev.mysql.com/doc/refman/5.5/en/innodb-transaction-model.html.
In particular, for additional information about InnoDB record-level
locks and how it uses them to execute various types of statements, see
http://dev.mysql.com/doc/refman/5.5/en/innodb-record-level-locks.html
and http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html.

The following list describes how MySQL supports the different
transaction levels. The list goes from the most commonly used level to
the least used.

o REPEATABLE READ

  This is the default isolation level for InnoDB. For consistent reads,
  there is an important difference from the READ COMMITTED isolation
  level: All consistent reads within the same transaction read the
  snapshot established by the first read. This convention means that if
  you issue several plain (nonlocking) SELECT statements within the
  same transaction, these SELECT statements are consistent also with
  respect to each other. See
  http://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html.

  For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE),
  UPDATE, and DELETE statements, locking depends on whether the
  statement uses a unique index with a unique search condition, or a
  range-type search condition. For a unique index with a unique search
  condition, InnoDB locks only the index record found, not the gap
  before it. For other search conditions, InnoDB locks the index range
  scanned, using gap locks or next-key (gap plus index-record) locks to
  block insertions by other sessions into the gaps covered by the
  range.

o READ COMMITTED

  A somewhat Oracle-like isolation level with respect to consistent
  (nonlocking) reads: Each consistent read, even within the same
  transaction, sets and reads its own fresh snapshot. See
  http://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html.

  For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE),
  UPDATE statements, and DELETE statements, InnoDB locks only index
  records, not the gaps before them, and thus permits the free
  insertion of new records next to locked records.

  *Note*: In MySQL 5.5, when READ COMMITTED isolation level is used or
  the innodb_locks_unsafe_for_binlog system variable is enabled, there
  is no InnoDB gap locking except for foreign-key constraint checking
  and duplicate-key checking. Also, record locks for nonmatching rows
  are released after MySQL has evaluated the WHERE condition. If you
  use READ COMMITTED or enable innodb_locks_unsafe_for_binlog, you must
  use row-based binary logging.

o READ UNCOMMITTED

  SELECT statements are performed in a nonlocking fashion, but a
  possible earlier version of a row might be used. Thus, using this
  isolation level, such reads are not consistent. This is also called a
  "dirty read." Otherwise, this isolation level works like READ
  COMMITTED.

o SERIALIZABLE

  This level is like REPEATABLE READ, but InnoDB implicitly converts
  all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if
  autocommit is disabled. If autocommit is enabled, the SELECT is its
  own transaction. It therefore is known to be read only and can be
  serialized if performed as a consistent (nonlocking) read and need
  not block for other transactions. (To force a plain SELECT to block
  if other transactions have modified the selected rows, disable
  autocommit.)

URL: http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html

