6.7.4 SET TRANSACTION Syntax

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

Sets the transaction isolation level for the global, whole session or the next transaction.

The default behaviour is to set the isolation level for the next (not started) transaction. If you use the GLOBAL keyword, the statement sets the default transaction level globally for all new connections created from that point on. You will need the SUPER privilege to do this. Using the SESSION keyword sets the default transaction level for all future transactions performed on the current connection.

You can set the default global isolation level for mysqld with --transaction-isolation=.... See section 4.1.1 mysqld Command-line Options.

User Comments

How about some details on the variants of this
command? What is the significance of READ
UNCOMMITTED, READ COMMITTED, REPEATABLE
READ and SERIALIZABLE?

What's the difference?

Not sure about MySQL's specifics on this one, but
here's what they should do:
READ UNCOMMITTED: queries within this transaction
type will be able to see all data, whether within
another transaction or not (often unsafe).
READ COMMITTED: queries within this transaction
type can only see data that has been committed;
uncommitted data in other transactions will be
invisible.
REPEATABLE READ: queries within this transaction
type will always get the same results; queries in
other transactions trying to change such data will
possibly block until the other transactions
'looking at' the same rows/tables are done.

What's the default isolation transaction level,if not
set by this command neither with command line
option?.

"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;"

Does not work in version 3.23.51

What do you mean it doesn't work in 3.23 ? the whole SET ISOLATION LEVEL feature has no use in this version?

What's SERIALIZABLE ? it wasn't answered in the previous comment...

ok i will answer to your question, but sorry for my english:
READ UNCOMMITED=level 0 :you have the possibility to read no validated information.


READ COMMITED=level 1 of isolation: Informations can be modified before the end of the transaction

REPEATABLE READ=level 2 of isolation: New lines can appear before the end of the transactions

SERIAZABLE=level 3 of isolation: The transactions are put in series

for READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE read:
7.5.9 InnoDB Transaction Model

Further Explaination of Isolation Level.

1) REPEATABLE READS : This can give you data view from start to end of transaction (transaction means it is a group of SQL statements, end of transaction means a commit is issued ). Hence if you are having any number (lets say 4 identical sql statements in a transaction, the data which is viewed by first sql will be same as viewed by last sql statement, even if someone changes data or adds or deletes). Mysql versioning feature provides this.

2) UNCOMMITED READ : You can view uncommited data ( i.e data not commited, this is inconsistent data as the user can rollback (revert back) the data).

3) SERIALIAZABLE : This means turn by turn the data can be accessed. This is true for select, delete, update & insert. Means if someone is selecting nobody can issue sel,insert,delete & update.

4) READ COMMITED : Always read commited data. This is consistent, but considering the first case (-->1)repeatable reads) the second sql results can be different from first sql statement in transaction if some one has changed or inserted any data between first and second sql statement execution.

Hope this are best explaination , please respond if any further clarifications needed.

Add your own comment.