【MySQL系列】- MYSQL锁机制

文章目录

• 【MySQL系列】- MYSQL锁机制
• • 一、表级锁
  • • 表加读锁
    • 表独占锁
    • 释放锁
  • 二、行级锁
  • • • 间隙锁（Gap Locks）
      • 临键锁（Next-key Locks）
  • 三、页级锁
  • 四、共享锁（S锁）
  • 五、排他锁（X锁）
  • 六、死锁
  • • • 6.1 死锁的产生的原因
      • 6.2 如何避免死锁
  • 七、总结

数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

MySQL 数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。

一、表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnDB、BDB等存储引擎中，对于表级锁一般分 1).表锁 2). 元数据锁 3). 意向锁。

表加读锁

lock tables 表名.. read; -- 是指可以对多张表加锁

表独占锁

lock tables 表名... write; -- 可以对多张表加独占锁

释放锁

unlock tables; -- 释放锁，客户端短裤连接

二、行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录的加锁。

对于行级锁，主要分为以下三类：

1. 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持
2. 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
3. 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

InnoDB实现了以下两种类型的行锁：

1. 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
2. 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

SQL	行锁类型	说明
INSERT…	排他锁	自动加锁
UPDATE…	排他锁	自动加锁
DELETE…	排他锁	自动加锁
SELECT…	不加任何锁
SELECT… LOCK IN SHARE MODE	共享锁	手动加锁
SELECT…FOR UPDATE	排他锁	手动加锁

间隙锁（Gap Locks）

间隙锁是封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

产生间隙锁的条件（RR事务隔离级别下；）

1). 使用普通索引锁定；2). 使用多列唯一索引；3). 使用唯一索引锁定多行记录。对于使用唯一索引来搜索并给某一行记录加锁的语句。只会产生记录锁，不会产生间隙锁。

打开间隙锁设置

查看是否启用间隙锁

show variables like 'innodb_locks_unsafe_for_binlog';

innodb_locks_unsafe_for_binlog默认值为OFF,既启用间隙锁。因为此参数是只读模式，如果想要禁用间隙锁，需要修改my.cnf 重启才能生效。

[mysqld]innodb_locks_unsafe_for_binlog = 1

结论

1. 对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则会产生记录锁，如：WHERE Id=1 FOR　UPDATE;
2. 对于查找某一范围的查询语句，会产生间隙，如：WHERE ID BETWEEN 5 AND 7 FOR UPDATE;
3. 在普通索引列上，不管是何种查询，只是加锁，都会产生间隙锁。

临键锁（Next-key Locks）

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

临键锁的主要目的，也是为了避免幻读（Phantom Read）。如果把事务的隔离级别降级为RC，临键锁则也会失效。

三、页级锁

锁就是在 页的粒度 上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我 们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销 介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

四、共享锁（S锁）

共享锁（Shared Lock）,又称S锁、读锁。针对行锁。当有事务对数据加读锁后，其他事务只能对锁定的数据加读锁，不能加写锁（排他锁），所以其他事务只能读，不能写。

加锁方式：

select * from Table where id=2 lock in share mode;

释放方式：

commit 或 rollback;

五、排他锁（X锁）

X锁，英文为Exclusive Lock既为排他锁。也可以称为Write Lock;X锁是具有排他性。既一个写锁会阻止其他的X锁和S锁。当一个事务需要修改一条记录时，需要先获取该记录的X锁。

X锁加锁格式：

select ... for update;

六、死锁

由于Mysql有多种类型，在实际开发中可能产生死锁。

6.1 死锁的产生的原因

死锁是指两个或两个以上的进程在执行过程中，因资源的争夺造成的一种相互等待的现象。若无外来的作用，他们都将无法推进下去，此时的系统处于死锁状态或系统产生了死锁。

死锁的关键在于：两个（或以上）的Session加锁的顺序不一致。

对应的解决死锁问题的关键就是：让不同的Session加锁有次序。

6.2 如何避免死锁

• 以固定的顺序访问表和行。比如两个更新数据的事务，事务A更新数据的顺序为1,2；事务B更新数据顺序为：2,1；这样可能会造成死锁
• 大事务拆小事务。大事务更倾向于死锁，如果业务运行，将大事务拆小。
• 在同一个事务中，尽可能做到一次锁定所需所有资源，减少死锁概率。
• 降低隔离级别。如果业务允许，将隔离级别调低也是比较好的选择，比如将隔离级别从RR调整为RC，可以避免很多原因gap锁造成的死锁。
• 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增加。

七、总结

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势
但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差

1. 尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁
2. 合理设计索引，尽量缩小锁的范围
3. 尽可能减少索引条件及索引范围，避免间隙锁
4. 尽量控制事务大小，减少锁定资源量和时间长度
5. 尽可使用低级别事务隔离（需要业务层面满足需求）

来源地址：https://blog.csdn.net/songjianlong/article/details/132962707