本站所有文章均为原创,如对您有帮助,恳请帮忙点击任何一处广告
  • 首页
  • MySQL
  • MySQL基础教程(11)数据库锁的分类与问题排查

MySQL基础教程(11)数据库锁的分类与问题排查

发布:TangLu2020-1-4 11:39分类: MySQL 标签: mysql 数据库

一、MySQL锁的作用

数据库锁主要用于解决并发问题,当并发操作发生时,数据库依靠锁来控制这些并发请求对资源(锁是针对资源而非事务)的访问规则,因为被上锁的资源不会被其他事务修改,因为可以保证事务之间的隔离性与一致性。


二、锁的分类与区别

· 读锁也叫共享锁、S锁,被上读锁的数据可以被其他事务读,但是不能被其他事务加写锁。

· 写锁也叫排他锁、X锁。被上写锁的数据,可以被上锁的事务读写,但是其他事务不能再对该数据加任何锁,直到上锁的事务释放锁。

· 全局锁:对整个数据库加读锁,当需要让整个数据库处于只读状态时才会加全局读锁,比如需要对使用非InnoDB引擎的数据库做全库逻辑备份。加锁后其他线程的增删改语句、建表、修改表结构语句都会被阻塞。客户端断开时会自动释放全局锁
mysql > flush tables with read lock

· 表锁:一般是在数据库引擎不支持行锁的情况下才会用到表锁,如果程序里有locak tables这样的语句就需要注意更换引擎或者代码了。锁定整张表,开销小,加锁快,不会出现死锁,但是发生冲突概率高,并发低。适用于查询为主、少量更新的应用,如WEB应用。客户端断开或执行unlock tables语句可以释放表锁

mysql > lock tables test_table read;  #给test_table表上读锁,只能读,不能写
mysql > unlock tables  #释放锁


· 行锁:InnoDB引擎默认使用行级锁,行锁相对表锁来说上锁的开销更大,有可能出现死锁,优点是冲突概率低,适合并发度高的业务。行级锁是通过给索引上的索引项加锁来实现的,只有通过索引查询的数据才会使用行级锁,否则使用表锁。即不使用索引查询时,一定使用表锁。

· 元数据锁(metadata lock):修改表的元数据时会自动触发,比如DDL语句。对表内数据做DML操作的时候加MDL读锁;对表做DDL变更操作的时候加MDL写锁
· 页锁:介于表锁和行锁之间,会出现死锁

· 间隙锁(GAP锁):对一个事务修改的数据中的空隙上锁,只会在RR隔离级别才会有这种锁,可以防止幻读。比如事务1执行update test set num=10 where num < 10,如果表中存在1,3,5,10这几个值,那么被锁的范围应该是10-5,5-3,3-1。这个时候去执行insert into test values (8)是会被锁住的

· 悲观锁与乐观锁:这2种锁是从应用的角度来说的,悲观锁在处理事务的时候认为数据大概率会被修改,所以每次操作都会先上锁,乐观锁则相反,只有在进行数据提交的时候才会判断是否需要加锁。悲观锁适用于写操作频繁的场景,如果有大量的读操作,每次读取都会加锁,降低了系统的吞吐量;乐观锁适用于读操作频繁的场景,如果有大量的写操作,则冲突的可能性会变高。


三、死锁问题

1、死锁的产生

理论上来说并发度越高越容易出现死锁,下面模拟一个事务A和事务B在互相等待对方释放锁的时候就会出现死锁情况

# 会话A:关闭自动提交事务,修改某行数据但并不提交
mysql> SET AUTOCOMMIT = off;
mysql> START TRANSACTION;
mysql> UPDATE worker SET type = 'B' WHERE id = 1;

# 会话B:关闭自动提交事务,修改某行数据但并不提交
mysql> SET AUTOCOMMIT = off;
mysql> START TRANSACTION;
mysql> UPDATE worker SET type = 'A' WHERE id = 2;

# 会话A:更新id=2的记录,此时事务就已经卡住了,因为会话B还没有提交对该行的修改
mysql> UPDATE worker SET type = 'A' WHERE id = 2;

# 会话B:更新id=1的记录,出现了死锁,MySQL报错,并让重启事务
mysql> UPDATE worker SET type = 'B' WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try resta rting transaction

# 会话A:更新id=2的记录成功,这是因为会话B出现死锁被KILL了,所以会话A才能执行成功,但是也经历了很长的锁等待时间



2、MySQL死锁相关设置

· 设置锁超时时间,减少无意义的等待

lock_wait_timeout = 1800  #默认是1年,非常不合理,建议设置为1800秒足够
innodb_lock_wait_timeout= 10  #行锁超时时间,默认50秒,建议调低 


· 死锁自动检测。当事务被锁的时候触发死锁检测,查看该事务所依赖的线程是否被其他事务锁住,如果确定是死锁就主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。死锁检测会消耗CPU资源。

innodb_deadlock_detect=on  #默认已开启这个逻辑


3、出现锁问题后如何定位

· 通过show processlist查看状态,重点关注state字段,如果出现很多waiting for ... lock,基本可以判断出现了死锁,但是此刻还没有办法定位是哪个SQL导致。


· 查看show open tables where in_use > 0语句查找是否有表锁,in_use为0代表没有锁

微信截图_20210119105425.png


· information_schema有三种关于锁的表

SELECT * FROM information_schema.innodb_trx  #处于运行状态的所有事务
SELECT * FROM information_schema.innodb_locks; 
SELECT * FROM information_schema.innodb_lock_waits;  #查看事务的锁等待状态

· 通过InnoDB状态查看锁

mysql > show engine innodb status
温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,谢谢合作!
et_highlighter51
版权所有:《Linux运维技术学习站点
文章标题:《MySQL基础教程(11)数据库锁的分类与问题排查
除非注明,文章均为 《Linux运维技术学习站点》 原创
转载请注明本文短网址:http://www.linuxe.cn/post-634.html  [生成短网址]

已有 0/193 人参与

发表评论:

欢迎分享Linux运维技术学习站点

欢迎使用手机扫描访问本站,还可以关注微信哦~