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:
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)
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.