1、Mysql的锁基本分成3种 

 ① 表锁(MyISAM)：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低 

 ② 行锁(INNODB)：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高 

 ③ 页锁(BDB)：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般 

 2、MyISAM 

 1) MyISAM只支持表锁，锁分为读锁和写锁.

 ① 读锁状态下，其他读锁不冲突，写锁需要等待 

 ② 写锁状态下，读锁和写锁都需要等待 

 ③ 读锁和写锁同在等待情况下，写锁的优先级比读锁高 

 ④ 在指定读锁的情况下lock table test read;只能读取test,无法读取其他表，表别名也需要执行，如lock table test as tt read; 

 2) 并发插入(concurrent_insert) 

 通常来说，在MyISAM里读写操作是串行的，但当对同一个表进行查询和插入操作时，为了降低锁竞争的频率，根据concurrent_insert的设置，MyISAM是可以并行处理查询和插入的： 

 ① 当concurrent_insert=0时，不允许并发插入功能。 

 ② 当concurrent_insert=1时，允许对没有空洞（表的中间没有被删除的行）的表使用并发插入，新数据位于数据文件结尾（系统默认）。 

 ③ 当concurrent_insert=2时，不管表有没有空洞，都允许在数据文件结尾并发插入。 

 3、InnoDB表锁问题 

 1) 了解并发事务下的几种数据情况 

 ① 脏读(Dirty Reads)：一个事务修改一行数据未提交，另一个事务读取这行修改完的数据，这种情况称为脏读 

mysql_lock_1.png

 ② 不可重读(Non-Repeatable Reads) : 一个事务读取一行数据，更一段时间再次读取该行数据，却发现该行数据已经被删除了

mysql_lock_2.png  

 ③ 幻读(Phantom Reads)：一个事务再检索之前的数据发觉有新的数据插入，这种情况叫幻读 

mysql_lock_3.png

 2) InnoDB的隔离级别 

mysql_lock_4.png  

 3) InnoDB的行锁实现方式

 InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。InnoDB行锁分为3中情形。 

 ① Record Lock: 对索引项加锁 

 ② Grap[ Lock: 间隙锁，在满足条件之间，主要为了防止相关数据写入 

 ③ Next-key Lock: 前面两种的组合，对记录及其前面的间隙加锁 

 测试表结构： 

 CREATE TABLE `test` ( 

 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 

 `num` int(11) NOT NULL, 

 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 

 3-1) Read Committed(RC)隔离模式下 

 ① id为主键, 执行sql : update test set num=20 where id=10, 直接在id=10加排他锁(X锁) 

mysql_lock_5.png

 ② id为唯一索引,Num为主键，执行sql : update test set num=20 where id=10, 这时会在二级索引和聚簇索引加X锁

mysql_lock_6.png 

 ③ id为一般索引，Num为主键,执行sql : update test set num=20 where id=10, 找到满足id=10的数据，加上X锁并且相关聚簇索引的也加上对应X锁

mysql_lock_7.png 

 ④ id没有索引时, 执行sql : update test set num=20 where id=10,这时会将所有记录加上X锁(注意：这里不是表锁，只能说类似表锁) 

mysql_lock_8.png  

 3-2)Repeatable Read隔离模式下 

 ① id为主键 和 id为唯一索引,Num为主键 都是唯一的(不涉及到下面将的间隙锁)，跟RC隔离模式下一致 

 ② id为一般索引，Num为主键,执行sql : update test set num=20 where id=10, 找到满足id=10的数据，加上X锁，为了防止不可重读, RR隔离模式下会添加间隙锁，并且相关聚簇索引的也加上对应X锁 

mysql_lock_9.png  

 ③ id没有索引时，所有记录会被加上X锁和Gap锁

mysql_lock_10.png  

 ④ 往test表中加一个name (varchar)字段，为name添加普通索 

 sql1:update test set num=11 where name name='1' 

 sql2: update test set num=11 where name name=1 

 sql1为上述正常的加锁，name=1时，由于1为整型，mysql不会走索引，会直接把所有记录都锁住 

 ⑤ 复杂的锁表情况 

 CREATE TABLE `test` ( 

 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 

 `cid` int(11) NOT NULL, 

 `up` int(11) NOT NULL, 

 `view` int(11) NOT NULL, 

 PRIMARY KEY (`id`), 

 KEY `idx_mix` (`cid`,`up`) ) 

ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 

 执行sql: UPDATE test SET view=99 WHERE cid >7 AND cid <= 14 AND up=2 AND view <=1 (注意:以mysql6.5以上分析) 

 ⑤.1 根据索引idx_mix找到cid在7跟14之间的数据 

 ⑤.2 再利用索引过滤,找到up=2的数据2条，由于view不在索引中,所以忽略,加上X锁跟Gap间隙锁 

 ⑤.3 找到聚簇索引对应的数据加X锁，然后过滤view中的数据,找到最终数据 

mysql_lock_11.png  

 4) 死锁 

 一般会出现死锁，是由于不同事务需要到用到某行数据，而该行属于由于某种情况一致没有解锁，而导致死锁的出现，上面了解mysql加锁的情况，就可以知道如何避免死锁的出现 

 ① 多个表之间，2个事务操作表的顺序不同而导致死锁 

mysql_lock_12.png  

 ② 同一个表，由于2个事务对数据行锁的顺序不同导致死锁 

mysql_lock_13.png  

 ③ 二级索引的锁来锁聚簇索引的数据，出现2个不同二级索引锁聚簇索引的数据顺序相反了，出现了死锁 

mysql_lock_14.png  

 这里sql1锁的顺序是1->5, sql2锁的顺序5->1 

 由于两个事务都同时执行，所以导致了死锁 

版权声明：未经博主允许不得转载。http://smister.com/post-38/mysql-lock.html