【MySQL系列】- MYSQL锁机制

【MySQL系列】- MYSQL锁机制

文章目录

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

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. 尽可使用低级别事务隔离（需要业务层面满足需求）