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://www.smister.com/post-38/mysql-lock.html