MySQL数据库锁
- 锁的分类
-
- 按照对数据操作的类型(读/写)进行分类
- 对数据操作的粒度分类
- 表锁
-
- 锁表---读表
-
- 查看表上加过的锁
- 释放所有表锁
-
- 注意
- 锁表---写表
- 总结
- 如何分析表锁定
- 行锁
-
- 行锁演示
-
- 索引失效会导致行锁变成表锁
- 间隙锁
- 如何锁定某一行
- 行锁总结
-
- 优化建议
- 页锁
- 总结
锁的分类
按照对数据操作的类型(读/写)进行分类
- 读锁(共享锁): 针对同一份数据,多个读操作可以同时进行而不会相互影响
- 写锁(排它锁): 当前写操作没有完成前,它会阻断其他写锁和读锁
对数据操作的粒度分类
- 表锁—偏读
- 行锁—偏写
表锁
偏向MyISAM存储引擎,开销小,加锁快,无死锁,锁定粒度大,发生锁冲突的概率最高,并发度低
建表SQL
CREATE TABLE mylock(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20)
)ENGINE MYISAM;
INSERT INTO mylock(`name`) VALUES('a');
INSERT INTO mylock(`name`) VALUES('b');
INSERT INTO mylock(`name`) VALUES('c');
INSERT INTO mylock(`name`) VALUES('d');
锁表—读表
手动增加表锁
lock table 表名字 read(write),表名字2 read(write),其他;
给mylock表上锁:
LOCK TABLE mylock READ;
查看表上加过的锁
show open tables;
释放所有表锁
unlock tables;
注意
锁表—写表
总结
读锁阻塞写,不阻塞读,写锁会把读写都阻塞
如何分析表锁定
行锁
InnoDB默认的行锁可以使得操作不同行时不会产生相互影响、不会阻塞,从而很好的解决了多事务和并发的问题。但是,那得基于一个前提,即 Where 条件中使用上了索引;反之,如果没有使用上索引,则是全表扫描、全部阻塞。
特点:
- 偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁;锁定粒度小,发生锁冲突的概率最低,并发度也最高
- InnoDB与MyISAM的最大不同有两点: 一是支持事务,而是采用了行级锁
事务复习:
ACID
并发事务处理带来的问题:
- 更新丢失
- 脏读
- 不可重复读
- 幻读
事务隔离级别
行锁演示
当对同一行数据进行修改操作时,后一个请求会进入阻塞状态,如果两个请求针对不同行进行修改操作,则不会进行阻塞状态
索引失效会导致行锁变成表锁
原本name='1002’是要加单引号的,但是下面的sql语句,没加,那么因为会做隐式类型转换,导致索引失效,行锁变成了表锁
UPDATE employee SET money = money + 5000 WHERE name = 1002;
结论
Where 条件中的查询字段虽然有索引,但是索引失效时(本例子中是字符串没有加单引号),InnoDB 默认的行锁更新操作变为表锁。
没有索引或者索引失效时,InnoDB 的行锁变表锁
原因:Mysql 的行锁是通过索引实现的!
https://blog.csdn.net/sshuidajiao/article/details/83150017?
间隙锁
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB(可重复读、串行化级别下才有效)会给符合条件的已有数据的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。
例如上面,给1<a<5的范围的数据加锁,但是不存在a=2的记录,此时a=2也会被加锁,因此当我们执行对应插入操作时,会进入阻塞状态
InnoDB使用间隙锁的目的,一方面是为了防止幻读,以满足相关隔离级别的要求,对于上面的例子,要是不使用间隙锁,如果其他事务插入了empid大于100的任何记录,那么本事务如果再次执行上述语句,就会发生幻读;另一方面,是为了满足其恢复和复制的需要。有关其恢复和复制对机制的影响,以及不同隔离级别下InnoDB使用间隙锁的情况。
很显然,在使用范围条件检索并锁定记录时,InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。因此,在实际开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件
如何锁定某一行
行锁总结
优化建议
页锁
总结
Mysql数据库中的各种锁