[mysql] mysql锁介绍
文章目录
一.mysql锁介绍
1.锁来源
在存在并发操作的时候，必然需要一种机制来保证数据的完整性与一致性。锁就是这一技术的实现。
2.锁种类
根据概念分：悲观锁和乐观锁
根据粒度分：表锁、页锁、行锁，最常见的就是表锁和行锁。其中，MyISAM引擎只有表锁，而InooDB既有表锁也有行锁，页锁代表引擎为BDB。
根据功能分：共享锁、排它锁（独占锁）、意向锁等。其中，共享锁被称为 S 锁。排它锁称为 X 锁。
。
3.锁特点
表锁，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。
行锁，开销大，发生锁冲突概率低。并发度高，会发生死锁。
页锁，开销、加锁时间、锁定粒度界于表锁和行锁之间，会出现死锁，并发度一般。
4.思维导图
5.锁配置
5.1进入mysql，查看配置
SHOW VARIABLES LIKE ‘%timeout%';
5.2配置解释
1). innodb_rollback_on_timeout是mysql锁超时后的回滚机制，如下：
innodb_rollback_on_timeout为OFF：如果事务因为加锁超时，相当于回滚到上一条语句。但是报错后，事务还没有完成，用户可以选择是继续提交，或者回滚之前的操作，由用户选择是否进一步提交或者回滚事务。
innodb_rollback_on_timeout为ON：整个事务都会回滚。
该变量默认值为OFF，如果事务因为加锁超时，会回滚上一条语句执行的操作。如果设置ON，则整个事务都会回滚。
2).innodb_lock_wait_timeout是锁超时时间，设置锁等待的时间，默认值是50s。
innodb_lock_wait_timeout指的是事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败；参数的时间单位是秒，最小可设置为1s(此时需要考虑应用端的频繁异常处理会消耗性能，不能设置过小)，最大可设置1073741824秒以上(再大就会被截断了，不过这样业务一直死循环等待下去而不能将资源使用来做其他的事情也是很浪费的一件事情)。
当有锁等待超过了这个时间（50s），会报错1205 - Lock wait timeout exceeded; try restarting transaction，来中断事务，并释放锁
5.3锁相关命令
– 查看innodb引擎的运行时信息
show engine innodb status;
show engine innodb status\G;
– 查看服务器状态
show status like ‘%lock%';
– 查看超时时间：
show variables like ‘%timeout%';
#看有没有锁等待
SHOW STATUS LIKE ‘innodb_row_lock%';
– mysql查看被锁住的表,查询是否锁表
show OPEN TABLES where In_use > 0;
– 查看所有进程
show processlist;
show full processlist;
– 杀掉指定mysql连接的进程号
kill $pid
– 查询死锁表
SELECT GROUP_CONCAT(CONCAT(‘kill ‘,id) SEPARATOR ‘; ‘) AS cmd FROM information_schema.processlist WHERE command=‘execute’;
– 查询运行的事务锁
SELECT * FROM information_schema.INNODB_TRX;
– 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
– 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
– 在分析innodb中锁阻塞时，几种方法的对比情况：
– （1）使用show processlist查看不靠谱；
– （2）直接使用show engine innodb status查看，无法判断到问题的根因；
– （3）使用mysqladmin debug查看，能看到所有产生锁的线程，但无法判断哪个才是根因；
– （4）开启innodb_lock_monitor后，再使用show engine innodb status查看，能够找到锁阻塞的根因。
查询表级锁争用情况
show status like ‘table%';
果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况
获取InnoDB行锁争用情况
show status like ‘innodb_row_lock%';
如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过
二.MyISAM 表锁
1.读锁
语法: lock table
在对表执行表读锁后，当前会话只能访问加锁的这个表，不能访问未加锁的表，但是非当前会话不受影响。
在执行表读锁后，当前会话只能进行查询操作，不能进行更新操作（update、delete 等）。非当前会话可以访问加锁表，但更新操作（update、delete 等）会造成阻塞。
在对表执行表读锁后，其他表可以继续加锁，并且之前加锁的表会自动解锁
2.写锁
语法：lock table
当一个会话持有该表读锁，其它会话可以持有该表读锁，但不能持有该表写锁，会造成阻塞
当一个会话持有表写锁，那么该会话只能对该表进行增删改查操作。其它会话则不能对该表进行一切操作。但是不影响其它会话对别的表进行操作
3.解锁
语法: unlock table;
4.总结
| \ | 表读锁 | 表写锁 |
|---|---|---|
| 当一个事务已持有表读 / 写锁，其它事务是否可对该表进行 curd | 可查不可增删改 | 可增删该查 |
| 当一个事务已持有表读锁，其它事务能否在继续持有表读 / 写锁 | 能在持有表读锁 | 不能持有表写锁 |
| 当一个事务已持有表写锁，其它事务能否在继续持有表读 / 写锁 | 不能持有表读锁 | 不能持有表写锁 |
| 当一个事务已持有表读 / 写锁，那这个事务能否在对别的表进行操作 | 不能 | 不能 |
三.InnoDB 表锁（意向锁）
1.定义
意向锁的含义是如果对一个结点加意向锁，则说明该结点的下层结点正在被加锁；对任一结点加锁时，必须先对它的上层结点加意向锁。
意向锁是有数据引擎自己维护的，用户无法手动干预，在加行级排它锁或共享锁之前，InooDB 先会判断所在数据行的数据表中是否有对应的意向锁。
2.表锁(意向锁)和行锁的兼容性
当一个会话持有某个表的行级共享锁，其它会话可以获取该表的表级共享锁，但不能获取该表的表级排它锁
当一个会话持有某个表的行级排它锁，其它会话不可以获取到表级的排它锁和共享锁
当一个会话持有某个表的表读锁，其它会事务可以在获取到行级读锁，但获取行级写锁会阻塞
当一个会话持有某个表的表写锁锁，其它会事务都不可以在获取该表的行级读写锁
3.总结
| \ | 意向共享锁（IS） | 意向排他锁（IX） |
|---|---|---|
| 表级共享锁（S） | 兼容 | 互斥 |
意向锁是表锁！当我们需要给一个表 加表锁的时候，我们需要根据意向锁去判断表中有没有数据行被锁定，以确定是否能加成功。如果意向锁是行锁，那么我们就得遍历表中所有数据行来判断。如果意向锁是表锁，则我们直接判断一次就知道表中是否有数据行被锁定了。
3.TIPS
注意注意注意！！！这儿有个坑，我上边的测试都是mysql5.7版本，但是在mysql5.7版本以下，加了行写锁后还能再加表读锁。版本不一样导致结果不一样，这坑研究了好几天没从网上找到答案，一度让我怀疑人生，以此谨记吧
四.InnoDB 行锁
注意，InnoDB 中的行锁需要在事务中运行才生效
1.共享锁（S 锁）
语法：lock in share mode
概念：又名读锁，对某一资源加共享锁，自身可以修改或读取该资源，其它人也能继续持有该资源的共享锁，无法持有该资源的排它锁。并只能读取，不能进行其它操作
一个会话给一个表中的某一行加共享锁，其它会话可读不可进行其它操作，直到锁释放
一个会话给一个表中的某一行加共享锁，不影响该会话操作其它表，以及自身的表，这与表锁不同（表锁是当前会话给该表加表锁后，那当前会话只能操作该表中的数据，不能在进行操作其它表中的数据了）
当一个会话持有某行的共享锁，其它会话也可在持有某行的共享锁，但是两者同时修改这条数据的话会造成死锁
2.排他锁（X 锁）
语法：for update
又名写锁，对某一资源加排它锁，自身可以修改或读取该资源，其它会话不能继续持有该资源的共享锁和排它锁。并只能对加锁数据进行读取，不能进行其它操作
排他锁的申请前提：没有线程对该结果集中的任何行数据使用排他锁或共享锁，否则申请会阻塞
for update 及 lock in share mode 仅适用于 InnoDB，且必须在事务块 (BEGIN/COMMIT) 中才能生效，在进行事务操作时，通过 for update 语句，MySQL 会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞，排他锁包含 行锁、表锁
行排它锁可不是加上以后其它事务就不能查询该行数据，只是其它事务则不能再去给该行加其它的锁。mysql InnoDB 引擎默认的修改数据语句，update,delete,insert 都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁类型，不管是行共享锁还是行排它锁都能够进行查询的，因为普通查询没有任何锁机制
当一个会话持有某行的排它锁，其它会话可读不可进行其它操作，直到锁释放
当一个会话持有某行的排它锁，其它会话则不能在修改数据以及持有改行的共享锁及排它锁。会造成阻塞
3.总结
| \ | 行读锁 | 行写锁 |
|---|---|---|
| 加上行读 / 写锁后，其它事务能删改这条数据吗 | 会阻塞 | 会阻塞 |
| 加上行读 / 写锁后，其它事务能读取这条数据吗 | 能 | 能 |
| 加上行读锁后，其它事务能在去持有行 / 读锁吗 | 能 | 不能 |
| 加上行写锁后，其它事务能在去持有行 / 读锁吗 | 不能 | 不能 |
五.锁的算法（行锁）
1、记录锁
2、间隙锁：
间隙锁（Gap Lock）是 Innodb 在可重复读提交下为了解决幻读问题时引入的锁机制。
3、临键锁
临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间，解决幻读问题。
文章作者 ZhangKQ
上次更新 2022-02-10