MySQL innodb engine support ACID appliance. (The following will be always applied to innodb) Innodb supports 4 Isolation Levels: READ UNCOMMITTED, READ COMMITTED, UNREPEATABLE READ, SERIALIZABLE
Innodb’s Isolation level is slightly different from the standard SQL Isolation level definition:
|Read Uncommitted||Read Committed||Repeatable Read||Serializable|
Repeatable Read is the default Isolation Level, which can ensure repeatable reads and no phantom reads.
InnoDB has two types of index: clustered index, secondary indexes
Clustered index is synonymous with the primary key, which means the record is stored in the leaf node of the index B+ Tree.
The key of leaf is the primary key. Even if you haven’t specified any primary key, innodb will generate a hidden one for you.
Secondary indexes are the ones other than the primary index. It doesn’t store the record data, but has a pointer to the primary index.
CREATE TABLE article ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, userid VARCHAR(20) NOT NULL, blogid CHAR(4), pubtime TIMESTAMP NOT NULL, comment VARCHAR(1024), PRIMARY KEY(id), KEY when_who (pubtime, userid) ) ENGINE=InnoDB DEFAULT CHARSET latin1;
MySQL support multiple granularity locking .
IS- intention shared/read,
IX- intention exclusive/write)
Conflict Conflict Conflict Conflict
Conflict Compatible Conflict Compatible
Conflict Conflict Compatible Compatible
Conflict Compatible Compatible Compatible
SELECT ... LOCK IN SHARE MODEsets an _
IS_ lock and
SELECT ... FOR UPDATEsets an _
A lock is granted to a requesting transaction if it is compatible with existing locks, but not if it conflicts with existing locks. A transaction waits until the conflicting existing lock is released.
Intention locks do not block anything except full table requests (for example,
LOCK TABLES ... WRITE)
- Record lock: This is a lock on an index record.
- Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.
- Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record. A next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. InnoDB uses clustered index and the row data are always in the leaf of the index B+ Tree. Thus, the row-level locks are actually index-record locks
Nonlocking - Consistent Nonlocking reads
REPEATABLE READ, all consistent reads within the same transaction read the snapshot established by the first such read in that transaction.
READ COMMITTED, each consistent read within a transaction sets and reads its own fresh snapshot. (snapshot update at every read statement)
MVCC is used to naturally implement REPEATABLE READ.
Locking in different Isolation Level
|Read Uncommitted(RU)||Read Committed(RC)||Repeatable Read(RR)||Serializable|
|consistent reads||[`SELECT ... FROM`](http://dev.mysql.com/doc/refman/5.6/en/select.html "13.2.9 SELECT Syntax")||no lockearlier version might be used(dirty read)||no lockeach consistent read reads its own fresh snapshot||no lockall consistent reads read the snapshot established by the first read||implicitly converts [`SELECT`](http://dev.mysql.com/doc/refman/5.6/en/select.html "13.2.9 SELECT Syntax") statements to [`SELECT ... LOCK IN SHARE MODE`](http://dev.mysql.com/doc/refman/5.6/en/select.html "13.2.9 SELECT Syntax")|
|locking reads||[`SELECT ... FROM ... LOCK IN SHARE MODE`](http://dev.mysql.com/doc/refman/5.6/en/select.html "13.2.9 SELECT Syntax")||like RC||locks only index records, no gap locks except for foreign-key constraint checking and duplicate-key checking||unique index: locks only the index record foundother search condition: locks the index range scanned, using [gap locks](http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_gap_lock "gap lock") or [next-key locks](http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_next_key_lock "next-key lock")||like RR|
|[`SELECT ... FROM ... FOR UPDATE`](http://dev.mysql.com/doc/refman/5.6/en/select.html "13.2.9 SELECT Syntax")|
|[`UPDATE ... WHERE ...`](http://dev.mysql.com/doc/refman/5.6/en/update.html "13.2.11 UPDATE Syntax")|
|[`DELETE FROM ... WHERE ...`](http://dev.mysql.com/doc/refman/5.6/en/delete.html "13.2.2 DELETE Syntax")|
|[`INSERT`](http://dev.mysql.com/doc/refman/5.6/en/insert.html "13.2.5 INSERT Syntax")||index-record exclusive lock, not a next-key lockif a duplicate-key error occurs, a shared lock on the duplicate index record is set.|