Basic knowledge

Isolation Level

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.

Index structure

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.

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_index

Locking

MySQL support multiple granularity locking .

Lock modes

 

  • row-level:  S lock and X lock (S - shared/read,  X - exclusive/write)
  • table-level: IS lock and IX lock (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)

Lock types
  • 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

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED andREPEATABLE READ isolation levels.

In REPEATABLE READ,  all consistent reads within the same transaction read the snapshot established by the first such read in that transaction.

In 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