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 条评论
下一页
