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):一个事务修改一行数据未提交,另一个事务读取这行修改完的数据,这种情况称为脏读
② 不可重读(Non-Repeatable Reads) : 一个事务读取一行数据,更一段时间再次读取该行数据,却发现该行数据已经被删除了
③ 幻读(Phantom Reads):一个事务再检索之前的数据发觉有新的数据插入,这种情况叫幻读
2) InnoDB的隔离级别
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锁)
② id为唯一索引,Num为主键,执行sql : update test set num=20 where id=10, 这时会在二级索引和聚簇索引加X锁
③ id为一般索引,Num为主键,执行sql : update test set num=20 where id=10, 找到满足id=10的数据,加上X锁并且相关聚簇索引的也加上对应X锁
④ id没有索引时, 执行sql : update test set num=20 where id=10,这时会将所有记录加上X锁(注意:这里不是表锁,只能说类似表锁)
3-2)Repeatable Read隔离模式下
① id为主键 和 id为唯一索引,Num为主键 都是唯一的(不涉及到下面将的间隙锁),跟RC隔离模式下一致
② id为一般索引,Num为主键,执行sql : update test set num=20 where id=10, 找到满足id=10的数据,加上X锁,为了防止不可重读, RR隔离模式下会添加间隙锁,并且相关聚簇索引的也加上对应X锁
③ id没有索引时,所有记录会被加上X锁和Gap锁
④ 往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中的数据,找到最终数据
4) 死锁
一般会出现死锁,是由于不同事务需要到用到某行数据,而该行属于由于某种情况一致没有解锁,而导致死锁的出现,上面了解mysql加锁的情况,就可以知道如何避免死锁的出现
① 多个表之间,2个事务操作表的顺序不同而导致死锁
② 同一个表,由于2个事务对数据行锁的顺序不同导致死锁
③ 二级索引的锁来锁聚簇索引的数据,出现2个不同二级索引锁聚簇索引的数据顺序相反了,出现了死锁
这里sql1锁的顺序是1->5, sql2锁的顺序5->1
由于两个事务都同时执行,所以导致了死锁
版权声明:未经博主允许不得转载。https://yigainian.com/post-38/mysql-lock.html