Transaction Isolation Levels:
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.
READ COMMITTED:
First UPDATE acquires an x-lock on each row that it reads and releases those for rows that it does not modify. Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. If you use READ COMMITTED, you must use row-based binary logging.
REPEATABLE READ:
Default for InnoDB - the first UPDATE acquires an x-lock on each row that it reads and does not release any of them. Consistent reads within the same transaction read the snapshot established by the first read.Several plain (nonlocking) SELECT statements within the same transaction are consistent also with respect to each other.
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.
- Isolation level can be change for a single session or for all subsequent connections with the SET TRANSACTION statement.
- To set the server's default isolation level for all connections, use the --transaction-isolation option on the command line or in an option file.
- Each isolation level has different locking strategies.
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.
READ COMMITTED:
First UPDATE acquires an x-lock on each row that it reads and releases those for rows that it does not modify. Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. If you use READ COMMITTED, you must use row-based binary logging.
REPEATABLE READ:
Default for InnoDB - the first UPDATE acquires an x-lock on each row that it reads and does not release any of them. Consistent reads within the same transaction read the snapshot established by the first read.Several plain (nonlocking) SELECT statements within the same transaction are consistent also with respect to each other.
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.
Explore Autocommit, Commit and Rollback
Explore Autocommit, Commit and Rollback
Comments
Post a Comment