Mysql 数据库锁总结

_Creator1024_

已于 2022-09-21 21:52:30 修改

阅读量1k

点赞数

分类专栏： SRE 文章标签：数据库 mysql

于 2022-09-21 21:48:25 首次发布

本文链接：https://blog.csdn.net/qq_41832237/article/details/126981619

版权

SRE 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

数据库锁

数据库锁主要是用来解决并发问题

从锁的影响范围，可以分为：

全局锁（库级别）
表级锁
行级锁

一、全局锁

全局锁是对整个数据库加锁，可以使用以下命令

Flush tables with read lock  # (FTWRL)

加了全局锁，会让整个库处于只读状态。其他线程以下语句会被阻塞：

数据更新语句（增删改）
数据库定义语句（建表、修改表结构等）
更新类的事务提交语句

全局锁的典型使用场景是，做全库逻辑备份。不过全局锁会让整个库变成只读，对业务是有影响的。只有不支持事务的引擎MyISAM通过这样做备份。

Mysql官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 时，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这也是使用 InnoDB 而不使用 MyISAM 的原因之一。

二、表级锁

MySQL 里面表级别的锁有两种：

表锁
元数据锁（meta data lock，MDL)

2.1.表锁

表锁的影响范围是整个表，一般只在不支持行锁的的时候才会用到。 InnoDB引擎是支持行锁的，所以我们简单了解表锁下即可。

表锁的语法是 lock tables <tables> read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。

READ具有以下功能：

同时可以通过多个会话获取表的READ锁。其他会话不需要获取读锁也可以查数据；
持有READ锁的会话只能从表中读取数据，但不能写入；
来自另一个会话的写操作将被放入等待状态，直到该会话释放READ锁。

WRITE具有以下功能：

只有拥有表锁定的会话才能从表读取和写入数据。
在释放WRITE锁之前，其他会话不能从表中读写。

# session A
# 上READ锁
lock tables T read;


# session A
# 上了READ锁之后，不能执行更新语句，只能查询
insert into T values(3); 
ERROR 1099 (HY000): Table 'T' was locked with a READ lock and can't be updated

# session B
# 此时回话B可以查数据，但是不能写数据
select * from T;  # ok
insert into T values(3);  # 被阻塞

# session A
# 直到session A解开锁或者断开连接之后，session B的查询语句才能继续执行
unlock tables;

# session A
lock tables T write;

# session B
# 无法读写数据
select * from T;  # 被阻塞；

# session A
# 通过SHOW PROCESSLIST查看目前被阻塞的操作
SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+---------------------------------+------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info             |
+----+------+-----------+------+---------+------+---------------------------------+------------------+
|  4 | root | localhost | db1  | Query   |    6 | Waiting for table metadata lock | select * from T  |
|  8 | root | localhost | db1  | Query   |    0 | starting                        | SHOW PROCESSLIST |
+----+------+-----------+------+---------+------+---------------------------------+------------------+

2.2.元数据锁MDL

MDL 的作用是，保证读写的正确性（例如A会话在做全表查询，B会话在对表进行更新，需要保证A的结果正确）；
MDL不需要显示指定，在访问一个表的时候会被自动加上。
- 当对一个表做增删改查操作的时候，加 MDL 读锁；
- 当要对表做结构变更操作的时候，加 MDL 写锁。

其中，读锁之间不互斥；读写锁之间，写锁之间，是互斥的。

当对需要对一个表更改字段的时候，需要注意，可能会引起其他会话被阻塞。

session A开始执行一个事务，但是不提交，这时候加MDL读锁；
session B是普通的读操作，需要MDL读锁，因为读锁不互斥，不会被锁住，正常执行；
session C对表进行修改，需要MDL写锁，因为A的读锁还没有释放，因此被阻塞；
session D申请MDL读锁，会被session C给阻塞住；这时候相当于整个表不可读写了。

mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+-------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                    |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------+
| 10 | root | localhost | db1  | Query   |    0 | starting                        | show processlist        |
| 11 | root | localhost | db1  | Sleep   |   85 |                                 | NULL                    |
| 12 | root | localhost | db1  | Query   |   28 | Waiting for table metadata lock | alter table T add f INT |
| 13 | root | localhost | db1  | Query   |   21 | Waiting for table metadata lock | select * from T         |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------+

直到session A commit事务之后，其他C和D的操作才能继续。

因此，需要考虑如何安全地修改表的字段。

避免长事务，事务执行过程中会一直拿着MDL读锁；如果在准备更新字段的时候，还有长事务，要考虑先处理掉。
在alter table语句里面设定等待时间，这样在规定的时间内如果没有拿到MDL写锁，也不要阻塞之后的语句。

ALTER TABLE tbl_name WAIT N add column ...

三、行级锁

InnoDB 支持行锁而MyISAM不支持。行锁就是针对数据表中行记录的锁。比如事务 A 要更新某一行数据，而这时事务 B 也要更新同一行，则B必须等A操作完成之后才能更新。

先来看一个例子：

在commit前，事务A持有两个行锁，事务B会被阻塞，直到A提交后才能执行。

所谓的两阶段锁协议，就是在需要的时候加锁，不需要了不会立即释放，而是等事务结束的时候，才释放。

因此，事务中需要锁多个行时，尽量把把最可能造成锁冲突、最可能影响并发度的锁往后放。

举个例子：

假设现在有个购物系统，用户购买一个商品后，数据库需要执行以下操作（简化）：

用户表中，扣除该用户余额（update）
商家表中，商家的余额增加（update）
记录一条交易日志（insert）

很明显，第二条语句是最可能影响到并发的（其他用户也购买时，同样也要更新商家的余额），因此，在事务中，最好按 3,1,2 的顺序执行。

四、死锁和死锁检测

死锁就是不同的线程出现了循环依赖，涉及到的线程都在等待别的线程释放资源，导致这些线程进入无限等待状态。

举个例子：

在这里插入图片描述

事务A：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set k=k+1 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update t set k=k+1 where id=2;
# 阻塞
# 直到事务B执行update t set k=k+1 where id=1; 产生死锁，然后回滚事务
# 该条语句才能继续执行
# ...
Query OK, 1 row affected (11.53 sec)   # 因为拿不到锁等待了11.53s
Rows matched: 1  Changed: 1  Warnings: 0

事务B：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set k=k+1 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 在A未commit之前，去更新已经被A锁住的行
mysql> update t set k=k+1 where id=1;
# 因为产生了死锁，事务被回滚
# 这时候事务B的 update t set k=k+1 where id=2; 能够继续执行
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
# 这时候这个事务已经结束了。



# 重新开启一个事务
mysql> begin;

mysql> update t set k=1 where id=1;
# 由于A事务还未提交，这时候拿不到锁，超时之后，事务也会被回滚
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

实际上，解决死锁的方式就是上面所经历的两个：

超时等待。当等待锁的时间超过innodb_lock_wait_timeout 设置的值后，也会回滚事务。Innodb默认为50s。

mysql> show global variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+

启动死锁检测（默认开启，通过以下变量查看）。现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。Innodb默认开启。

mysql> show global variables like 'innodb_deadlock_detect';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+

对于超时等待，默认50s这么长的时间，对业务的影响很大，肯定不能直接这么用，但是设置成很短的时间，比如1s，确实可以让死锁很快地解开，但是可能也会影响到其他正常等待锁而非死锁的场景。

因此，正常情况下还是靠死锁检测策略。不过这个也有额外的负担。死锁检测的逻辑是：每当一个事务被锁的时候，就看看其依赖的线程有没有被其他人锁住（例如事务A依赖B，C依赖D，这时候事务E依赖C，事务E中的死锁检测会去检查C和D）。

假设有1000个并发，并且所有的事务都互相依赖，那么死锁检测就是100w级别的计算量，会耗费大量CPU，及时并发不高，也很占资源。

减少死锁的主要方向，就是控制访问相同资源的并发事务量。有以下几种方法可以参考：

关闭死锁检测：前提是要保证业务不会出现死锁。这个并不是一个好的解决方案；
使用队列：将会发生死锁的事务通过队列进行，同时控制并发，不过这需要中间件的支持；
将某个行拆分成多个行，比如，拆成多行计算余额，总余额是各行余额的总和，这样可以减少死锁的概率

最后，我们不能保证死锁完全避免，因此程序对于事务要有重试机制。当事务因死锁回滚了，程序需要进行重试，保证业务的完整性。

_Creator1024_

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Mysql 数据库锁总结

死锁检测的逻辑是：每当一个事务被锁的时候，就看看其依赖的线程有没有被其他人锁住（例如事务A依赖B，C依赖D，这时候事务E依赖C，事务E中的死锁检测会去检查C和D）。对于超时等待，默认50s这么长的时间，对业务的影响很大，肯定不能直接这么用，但是设置成很短的时间，比如1s，确实可以让死锁很快地解开，但是可能也会影响到其他正常等待锁而非死锁的场景。行锁就是针对数据表中行记录的锁。假设有1000个并发，并且所有的事务都互相依赖，那么死锁检测就是100w级别的计算量，会耗费大量CPU，及时并发不高，也很占资源。
复制链接

扫一扫