mysql数据锁的类型_MySQL基础教程（11）数据库锁的分类与问题排查

一、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代表没有锁

· 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