MySQL锁(锁粒度/上锁与排查/死锁)
2021-02-26 15:38:03 3 举报
AI智能生成
MySQL锁是数据库管理系统用来确保数据一致性和并发控制的一种机制。锁粒度分为表级锁、行级锁和页级锁,分别对应不同的锁定范围和影响程度。上锁与排查是指在执行SQL语句时,通过使用事务和锁定资源来保护数据的完整性,同时需要对可能出现的死锁进行排查和解决。死锁是指两个或多个事务互相持有对方所需的资源,导致无法继续执行的情况,可以通过设置超时时间、调整事务顺序或者使用死锁检测机制来解决。
作者其他创作
大纲/内容
喜欢收藏+点赞👍 谢谢
事务并发访问
读-读
由于两个事务都进行只读操作,不会对记录造成任何影响,因此并发读完全允许
写-写
可能会产生脏写,并发事务同时修改一行数据,只能加锁,事务执行完后释放锁
读-写
可能会产生脏读、不可重复读、幻读
读操作利用多版本并发控制(MVCC)
写操作进行加锁
锁的粒度
行锁
作用在数据行上,锁的粒度小,并发度高
开销大,加锁慢;会出现死锁
默认搜索引擎:InnoDB
实现原理
InnoDB的行锁,是通过锁住索引来实现的
范围
记录锁(Record Lock):锁定一个行记录
间隙锁(Gap Lock):锁定一个区间(不包括边界)
Next-key Lock(默认)
临键锁:记录锁和间隙锁的结合(包括边界)
除了锁住记录本身,还要再锁住索引之间的间隙
表锁
作用在整张数据表上,锁的粒度大,并发度低
开销小,加锁快;不会出现死锁
默认搜索引擎:MyISAM
锁的分类
共享锁
在事务要读取一条记录时,需要先获取该记录的 S 锁,S 锁可以在同一时刻被多个事务同时持有
排他锁
在事务要改动一条记录时,需要先获取该记录的 X 锁。X 锁在同一时刻最多只能被一个事务持有
意向锁
意向锁是由数据库自己维护的加锁标识,可以快速判断表中是否有记录被上锁,避免遍历,提高加锁效率
mysql默认根据实际场景自动选择加锁方式,当然也可以通过 innodb_autoinc_lock_mode 强制指定只使用其中一种
互斥
上锁与排查
上锁
隐式上锁(默认)
select(快照读,不加锁)
MVCC
insert、update、delete(当前读,排他锁)
显式上锁
select * from table_name lock in share mode;(当前读,共享锁)
select * from table_name for update;(当前读,排他锁)
解锁/释放锁
提交事务(commit)
回滚事务(rollback)
kill 阻塞进程
排查
show status like 'innodb_row_lock%';
innodb_row_lock_current_waits:当前等待锁的数量
innodb_row_lock_time:锁定总时长
innodb_row_lock_time_avg:平均等待时长
innodb_row_lock_time_max:最长一次等待时间
innodb_row_lock_waits:系统启动到现在总共等待的次数
select(共享锁)
insert、update、delete(排他锁)
显式上锁(手动)
lock table table_name read;(共享锁)
lock table table_name write;(排他锁)
解锁(手动)
unlock table table_name;(单表)
unlock tables;(所有表)
show open tables;(查看)
show status like 'table%';(分析)
table_locks_waited:因表级锁争用而等待的次数
table_locks_immediate:产生表级锁定的次数
数据库死锁
概念
指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象
产生条件
请求与保持
不可剥夺
循环等待
解除死锁
查看:show engine innodb status \\G;
查看是否锁表,查看进程,杀死进程
查看当前锁定和等待的事务,杀死进程
如何避免
加锁顺序一致,尽可能一次锁定所需数据行
保持简短的事务,单次操作数量不宜过多
使用较低的隔离级别
合理使用索引,减少不必要的索引
0 条评论
回复 删除
下一页