Mysql--innoDB locking

1.共享锁和排他锁

innodb 实现了标准的行锁：共享锁 和排他锁

共享锁(S)：允许持有共享锁的事物读取该行

排它锁(X)：允许持有改锁的事务删除或者更新行

      当一个事务T1持有Table1上r行的S 锁时，其他事务想持有该行的锁时，必须按照下面的规则进行：

      1）：如果事务T2 想获得该行的S锁时，T2可以立即获取。

      2）：如果事务T2想获得该行的X锁时，T2不能立即获取（X S相互排斥）。

   如果T1持有r行的X锁，则不论T2想获取什么类型的锁都不能立即获取，直至T1释放掉X锁。

2.意向锁（Intertion Locks）

innodb支持多粒度锁，即允许行锁和表锁共存于一张表。为了实现多粒度的锁定，我们们用另种锁类型---意向锁。意向锁在innodb是表级别的锁，它标识着稍后该表会加上什么样的锁类型（S 或者 X）。意向锁目前有2中类型（假设事务T在表t上已经有一个锁请求）：

意向共享锁(IS):事务T意图在表t单行上加上S锁。

意向排他锁(IX):事务T意图在表t某些上加上X锁。

      意向锁加锁规则：

1）：事务T要获取表t上一行的S锁，它必须先获取该表的IS锁或更高级的锁。

        2）：事务T要获取表t上一行的X锁，它必须先获取该表的IX锁。

                           

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

    如果事务请求的锁跟已经存在的锁是兼容的，但是不冲突的。事务会的等待直到冲突的事务锁被释放。如果是请求的是锁和已经存在的事务锁冲突时不能够得到所权限的，它会产生死锁或者错误。

   意向锁只会锁全表的请求事务（例如 LOCK TABLES ... WRITE），意向锁的主要目的是展示谁在正在锁表的行，或者说将要去锁表的行。

3.记录锁（Record Locks）

记录锁是一种加在索引记录上的。例如：select c1  from t where c1=10 for update; 阻止其他任何事务的insert ，update，delete c1=10的行。

记录锁总是锁index 记录，即使该表没有定义索引，这个中情况下，Innodb 会创建一个隐藏的集群的索引，而且会把这个所应当做记录锁。

可以通过SHOW ENGINE INNODB STATUS命令查看锁类型：

   

------------
TRANSACTIONS
------------
Trx id counter 37936
Purge done for trx's n:o < 37933 undo n:o < 0 state: running but idle
History list length 412
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 6, OS thread handle 0x7ffb265c8700, query id 219 10.0.1.98 root
---TRANSACTION 37926, not started
MySQL thread id 3, OS thread handle 0x7ffb2668b700, query id 64 localhost 127.0.0.1 root
---TRANSACTION 37929, not started
MySQL thread id 2, OS thread handle 0x7ffb266cd700, query id 65 localhost 127.0.0.1 root
---TRANSACTION 37931, ACTIVE 1343 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 0x7ffb2664a700, query id 212 localhost 127.0.0.1 root updating
delete from test where id=14
Trx read view will not see trx with id >= 37936, sees < 37930
------- TRX HAS BEEN WAITING 15 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 126 page no 3 n bits 88 index `PRIMARY` of table `test`.`test` trx id 37931 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000e; asc     ;;
 1: len 6; hex 00000000942a; asc      *;;
 2: len 7; hex 8e000001b30110; asc        ;;
 3: len 4; hex 31303133; asc 1013;;
 4: len 5; hex 6878773133; asc hxw13;;
 5: len 4; hex 5a3c95c0; asc Z<  ;;

------------------

4.间隙锁（gap lock）

   升级版的记录锁,可以范围锁定索引记录。例如： SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;阻止其他事务在c1 (10,20) 之间插入值，不管这个值存在与否，间隙锁锁定的是已经存在值得范围。

   间隙锁是针对非唯一索引的。

   加入表t v1 字段有值1,2,3,4,5,7,9 ，针对非唯一索引而言，字段区间课划分为：（-∞，1）,(1,2),(2,3),(3,4),(4,5),(5,7),(7,9),(9,+∞),如果更新v1=7 行,v1锁定indx_v1的区间是（5,7）,（7,9）;针对v1=7 key加上锁。

5.Next-key lock

   是记录锁和间隙锁结合，更新非唯一索引记录时，会加Next-key lock。如果更新的记录为空，就不能加记录锁，只能加gap 锁。

   参考：http://blog.sina.com.cn/s/blog_a1e9c7910102vnrj.html

6.自增锁（AUTO-inc lock）

自动增长锁是一种特殊的表级锁应用于事务插入数据到含有自增长的列。取决于数据库参数innodb_autoinc_lock_mode。

参考http://blog.itpub.net/20892230/viewspace-2132255/  。

常见锁查询语句

1.当前innodb所等待个数
show status like '%lock%';


2.当前innodb事务等待情况
show engine innodb status;
TRANSACTIONS



3.查看当前事务锁表详细
select * from information_schema.INNODB_LOCKS