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 ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.