Syntax:
SET [GLOBAL | SESSION] TRANSACTION
    transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic:
    ISOLATION LEVEL level
  | READ WRITE
  | READ ONLY

level:
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE

This statement specifies transaction characteristics. It takes a list
of one or more characteristic values separated by commas. These
characteristics set the transaction isolation level or access mode. The
isolation level is used for operations on InnoDB tables. The access
mode may be specified as to whether transactions operate in read/write
or read-only mode.

In addition, SET TRANSACTION can include an optional GLOBAL or SESSION
keyword to indicate the scope of the statement.

Scope of Transaction Characteristics

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

o With the GLOBAL keyword, the statement applies globally for all
  subsequent sessions. Existing sessions are unaffected.

o With the SESSION keyword, the statement applies to all subsequent
  transactions performed within the current session.

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

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

SET TRANSACTION 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 characteristics 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';

Similarly, to set the transaction access mode at server startup or at
runtime, use the --transaction-read-only option or tx_read_only system
variable. By default, these are OFF (the mode is read/write) but can be
set to ON for a default mode of read only.

Setting the global or session value of tx_isolation or tx_read_only is
equivalent to setting the isolation level or access mode with SET
GLOBAL TRANSACTION or SET SESSION TRANSACTION.

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.7/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.7/en/innodb-record-level-locks.html
and http://dev.mysql.com/doc/refman/5.7/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.7/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 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.7/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.7, when READ COMMITTED isolation level is used, or
  the deprecated 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.)

Transaction Access Mode

The transaction access mode may be specified with SET TRANSACTION. By
default, a transaction takes place in read/write mode, with both reads
and writes permitted to tables used in the transaction. This mode may
be specified explicitly using an access mode of READ WRITE.

If the transaction access mode is set to READ ONLY, changes to tables
are prohibited. This may enable storage engines to make performance
improvements that are possible when writes are not permitted.

It is not permitted to specify both READ WRITE and READ ONLY in the
same statement.

In read-only mode, it remains possible to change tables created with
the TEMPORARY keyword using DML statements. Changes made with DDL
statements are not permitted, just as with permanent tables.

The READ WRITE and READ ONLY access modes also may be specified for an
individual transaction using the START TRANSACTION statement.

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

