InnoDB存储引擎中使用的锁机制

共享锁(s lock)：允许事务读一行数据
排他锁(x lock)：允许事务删除或者更新一行数据
意向共享锁(is lock)：事务想要获得一个表中某几行的共享锁
意向排他锁(ix lock)：事务想要获得一个表中某几行的排他锁
意向锁是表级别的锁，是为了在一个事务中揭示下一行将被请求的锁的类型。
自增长锁(autoinc_lock)：当表里有一个auto increment字段时，InnoDB会在内存里保存一个计数器来记录auto_increment的值，当插入一个新行数据时，就会用一个表锁来锁住这个计数器，直到插入结束。innodb_autoinc_lock_mode模式有0traditional，1consecutive，2interleaved
死锁：相互等待对方。
锁升级：将当前锁的粒度降低。
 
事务的隔离级别：READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ,SERIALIZABLE.
 
一致性非锁定读：不需要等待查询行上的X锁释放。与事务隔离级别有关，不同事务隔离级别，读取的方式不同，读取的并不都是一致性读，对于快照的定义也不相同。
是指InnoDB存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在进行删除，更改操作，这时读取操作不会因此而等待行上的锁释放，相反，InnoDB存储引擎会去读取行的一个快照数据。
 在read committed和repeatable read事务隔离级别下，InnoDB存储引擎使用非锁定的一致性读。
在read committed事务隔离级别下，对于快照数据，非一致性读总是读取不被锁定行的最新一份快照数据。
在repeatable read事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。
 
REPEATABLE-READ事务隔离级别：（InnoDB默认的事务隔离级别）
Session ASession B
mysql> start transaction;mysql> select * from test where id=3;
+—-+——-+
| id | value |
+—-+——-+
|  3 | ccc   |
+—-+——-+
 mysql> start transaction;mysql> update test set value=’333′ where id=3; 
mysql> mysql> select * from test where id=3;+—-+——-+
| id | value |
+—-+——-+
|  3 | ccc   |
+—-+——-+
 mysql> commit;
 mysql> show variables like “tx_isolation”;
+—————+—————–+
| Variable_name | Value           |
+—————+—————–+
| tx_isolation  | REPEATABLE-READ |
+—————+—————–+
mysql> select * from test where id=3;+—-+——-+
| id | value |
+—-+——-+
|  3 | ccc   |
+—-+——-+
mysql> commit;
结论：对于REPEATABLE-READ事务隔离级别总是读取事务开始时的行数据。

 
READ-COMMITTED事务隔离级别：
Session ASession B
mysql> set session tx_isolation=’READ-COMMITTED’;mysql> show variables like “tx_isolation”;
+—————+—————-+
| Variable_name | Value          |
+—————+—————-+
| tx_isolation  | READ-COMMITTED |
+—————+—————-+mysql> set session tx_isolation=’READ-COMMITTED’;mysql> show variables like “tx_isolation”;
+—————+—————-+
| Variable_name | Value          |
+—————+—————-+
| tx_isolation  | READ-COMMITTED |
+—————+—————-+
mysql> start transaction;mysql> select * from test where id = 2;
 mysql> start transaction;mysql> update test set value=’222′ where id=2;
mysql> select * from test where id = 2;+—-+——-+
| id | value |
+—-+——-+
|  2 | bbb   |
+—-+——-+
  mysql> commit;
mysql> select * from test where id = 2;+—-+——-+
| id | value |
+—-+——-+
|  2 | 222   |
+—-+——-+
mysql> commit;
结论：对于READ-COMMITTED事务隔离级别总是读取行的最新版本，如果行被锁定了，则读取该行版本的最新一个快照。(读取committed后的行数据)不符合事务ACID中的隔离行，一致性(即不可重复读)。

 
InnoDB存储引擎中行锁的算法：
1.record lock：单个行记录上的锁（锁定索引记录）
2.gap lock：间隙锁，锁定一个范围，但不包括记录本身
3.next-key lock：以上两种的组合，锁定一个范围并且锁定记录本身
 
Next-key lock算法：
Session ASession B
mysql> select * from test;+—-+——-+
| id | value |
+—-+——-+
|  1 | aaa   |
|  2 | 222   |
|  3 | 333   |
|  4 | 444   |
|  8 | 888   |
|  9 | 999   |
+—-+——-+
mysql> start transaction;mysql> select * from test where id <6 lock in share mode;
+—-+——-+
| id | value |
+—-+——-+
|  1 | aaa   |
|  2 | 222   |
|  3 | 333   |
|  4 | 444   |
+—-+——-+
 mysql> start transaction;mysql> insert into test select 5,555;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test select 8,888;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test select 10,101;
Query OK, 1 row affected
mysql> rollback;mysql> rollback;
mysql> start transaction;mysql> select * from test where id = 2 lock in share mode;
 mysql> start transaction;mysql> update test set value=’11111′ where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update test set value=’22222′ where id=2;    
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test set value=’33333′ where id=3;     
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> rollback;mysql> rollback;
结论：在next-key lock算法下，锁定的区间在(-∞,8)。对于单个值的索引查询，使用record lock算法。在REPEATABLE-READ事务隔离级别下InnoDB使用next-key lock算法。使用next-key lock算法可以避免不可重复读的现象。

 
 
脏读发生的原因：事务的隔离级别为READ UNCOMMITTED。
脏读是指一个事务可以读到另外一个事务中尚未提交的数据。
脏读现象：事务的隔离级别为READ UNCOMMITTED
Session ASession B
mysql> set session tx_isolation=’READ-UNCOMMITTED’;mysql> set session tx_isolation=’READ-UNCOMMITTED’;
mysql> start transaction;mysql> select * from test;
+—-+——-+
| id | value |
+—-+——-+
|  1 | aaa   |
|  2 | 222   |
|  3 | 333   |
|  4 | 444   |
|  9 | 999   |
| 10 | 101   |
+—-+——-+
 mysql> insert into test value (’20′,’202′);
mysql> select * from test;+—-+——-+
| id | value |
+—-+——-+
|  1 | aaa   |
|  2 | 222   |
|  3 | 333   |
|  4 | 444   |
|  9 | 999   |
| 10 | 101   |
| 20 | 202   |
+—-+——-+
mysql> rollback;mysql> rollback;
结论：事务的隔离级别为READ UNCOMMITTED会发生脏读的现象，可以读取到尚未提交的数据。

 
 
查看阻塞语句和等待语句：
mysql> SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
blocking_query: select b from T2 for update
 
 
+—————-+—————-+—————————–+—————–+—————–+—————————–+
| waiting_trx_id | waiting_thread | waiting_query               | blocking_trx_id | blocking_thread | blocking_query              |
+—————-+—————-+—————————–+—————–+—————–+—————————–+
| 827            |             12 | select c from T2 for update | 826             |              11 | select b from T2 for update |
| 827            |             12 | select c from T2 for update | 815             |              10 | select sleep(999)           |
| 826            |             11 | select b from T2 for update | 815             |              10 | select sleep(999)           |
+—————-+—————-+—————————–+—————–+—————–+—————————–+
等待事务id827 select c from T2 for update被select b from T2 for update和select sleep(999)阻塞；等待事务id826 select b from T2 for update被select sleep(999)阻塞。