Kira's Blog
文章 标签 分类
Kira's Blog

MySQL的锁

 收录于  类别 数据库
     约 2806 字   预计阅读 6 分钟 
https://image.linux88.com/2023/03/02/37feb1ab66baa67f7504eb52e1e72f08.png
警告
本文最后更新于 2023-03-04，文中内容可能已过时。

全局锁的典型使用场景是，做全库逻辑备份。

1
2
3
4

-- FTWRL 整个库处于只读状态
FLUSH TABLES WITH READ LOCK;
-- 解锁
UNLOCK TABLES;
  • 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
  • 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。
  • single-transaction 方法只适用于所有的表使用事务引擎的库。
  • readonly 选项会用来执行其他逻辑处理，比如用它判断某个库是主库还是备库。因此，修改 global 变量的影响范围更大，不建议使用这种方式。
  • 在异常处理机制上存在差异。如果发出 FTWRL 命令后，由于客户端发生了异常而断开连接，MySQL 就会自动释放全局锁，然后数据库就会回到可以继续更新的状态。但如果将整个数据库设置为 readonly，并在操作过程中发生了异常，数据库就会一直处于 readonly 模式，并且会面临较高的风险。

与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。

1
2
3
4

-- 锁定名为t的表，以便其它用户在这张表上只能进行读操作，无法写入。
FLUSH TABLES t WITH READ LOCK;
-- 锁定名为t的表，其他用户不能对该表读写任何数据。
FLUSH TABLES t WITH WRITE LOCK;

MySQL 5.5 引入了MDL（Meta Data Lock）锁，它分为读锁和写锁。

  • 读锁之间不会相互影响，所以你可以让多个线程同时进行增删改查操作。
  • 但读写锁之间、写锁之间是互斥的，用来保证给表结构变更操作的安全性。因此，你要是有两个线程去给一张表加字段，其中一个需要等另一个先执行完毕，才能开始。
1
2
3
4
5

```sql
-- 给小表加字段
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
```

当你发现 lock tables 这样的语句时，这很有可能是因为你的数据库引擎不支持行锁导致的。两种情况：

  • 你的系统正在使用 MyISAM 等不支持事务的引擎，那么就需要考虑升级数据库引擎；
  • 你数据库引擎已经升级，但是代码却没有跟着升级，此时你只需要把 lock tables 和 unlock tables 改为 begin 和 commit 就可以解决问题了。
1
2
3
4

-- 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。（当前读）
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;
-- 其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁。
SELECT * FROM table_name WHERE ... FOR UPDATE;

在InnoDB事务中，行锁是需要时才加上的，但不会立刻释放，只有等到事务完成时，这个”两阶段锁协议“才会释放。

如果你的事务中需要锁定多行，最好是尽可能先锁定那些不太可能引起锁冲突行。例如，此应用程序要涉及以下操作：

  1. 从顾客A的余额中扣除电影票价；
  2. 给影院B账户余额增加这张电影票价；
  3. 记录一条交易日志。

从上面可以看出，多个用户对应一个影院账户，所以影院账户余额的行引起锁冲突的概率最高，所以调整顺序为（3、1、2）。

  • shared (S) locks 共享锁：T1持有行r的共享（S）锁，即T2请求对行r的锁定时，将以如下方式处理：
    • T2请求 S锁 可以立即授予。因此，T1和T2都持有行r的S锁。
    • T2请求 X锁 不能立即授予。
  • exclusive (X) locks 排他锁：如果事务T1在行r上拥有排他(X)锁，则来自不同事务T2的锁定请求不能立即满足。相反，事务T2必须等待事务T1释放其对行r的锁定。

InnoDB 支持多粒度锁定，可以允许行锁和表锁共存。例如，LOCK TABLES ……WRITE 将在指定的表上排他性锁定(X 锁定) 。为了使多级粒度的锁定实用，InnoDB 使用意向锁。意向锁是表级锁，它表明事务在表中的行上需要什么类型的锁定(共享或排他)。有两种类型的意向锁：

X IX S IS
X 冲突 冲突 冲突 冲突
IX 冲突 兼容 冲突 兼容
S 冲突 冲突 兼容 兼容
IS 冲突 兼容 兼容 兼容

记录锁是索引记录上的锁。例如， SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 阻止任何其他事务插入、更新或删除 t.c1 值为 10 的行。

  • 如果您将事务隔离级别更改为 READ COMMITTED ，间隙锁不生效。

https://www.brightbox.com/images/blog/mysql_locks.png

next-key lock 允许一个共享或可重复访问锁锁定 next-key 范围。也就是说，它同时锁住一行数据，也锁定它之后的空间，使其他事务不能插入这个范围内的任何数据。所以它是由 记录锁 和 间隙锁 组成的。

  1. 原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
  2. 原则 2：查找过程中访问到的对象才会加锁。
  3. 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为 行锁。
  4. 优化 2：索引上的等值查询，向右遍历时且最后一个值也不是需要找的值时，next-key lock 退化为 间隙锁。
  5. 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

当出现死锁的情况下，有两种可选的处理方式：

  • 第一种办法是，直接进入等待状态，直到超时。你可以通过 innodb_lock_wait_timeout 这个参数去设定等待时间。
  • 第二种办法是，启动死锁检测(Deadlock Detection)，当死锁出现之后，便会主动回滚死锁链条中的某一个事务，以此让其他事务继续执行。要启动这个检测，需将 innodb_deadlock_detect 参数设置成 on。

show processlist 命令查看 Waiting for table metadata lock 。

sys.schema_table_lock_waits 显示哪些会话已被锁定，等待元数据锁定

1

SELECT * FROM sys.schema_table_lock_waits\G

1

SHOW PROCESSLIST;

sys.innodb_lock_waits 总结了事务正在等待的InnoDB锁。默认情况个，行按降序锁定年龄排序。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

SELECT * FROM sys.innodb_lock_waits\G
***************************[ 1. row ]***************************
wait_started                 | 2023-03-01 21:27:44
wait_age                     | 0:00:02
wait_age_secs                | 2
locked_table                 | `test`.`t`
locked_index                 | PRIMARY
locked_type                  | RECORD
waiting_trx_id               | 20074
waiting_trx_started          | 2023-03-01 21:27:44
waiting_trx_age              | 0:00:02
waiting_trx_rows_locked      | 1
waiting_trx_rows_modified    | 0
waiting_pid                  | 1123
waiting_query                | update t set d=5 where id=0
waiting_lock_id              | 20074:12:3:2
waiting_lock_mode            | X
blocking_trx_id              | 20072
blocking_pid                 | 1159
blocking_query               | <null>
blocking_lock_id             | 20072:12:3:2
blocking_lock_mode           | X
blocking_trx_started         | 2023-03-01 21:25:44
blocking_trx_age             | 0:02:02
blocking_trx_rows_locked     | 7
blocking_trx_rows_modified   | 0
sql_kill_blocking_query      | KILL QUERY 1159
sql_kill_blocking_connection | KILL 1159

乐观锁又称为无锁或版本锁，它使用版本戳来实现，某一记录在更新时，其版本必须是当前数据库中最新的版本。乐观锁不会对其他用户发起的读写操作施加任何约束，所以最大程度上减少了锁带来的性能开销。

优点：主要优点是没有使用锁，简化了事务处理，增加了访问数据库的速度，减少了死锁产生。

缺点：它更容易发生并发冲突，存在脏读取的情况，比如并发时两个事务由于版本不同而都可以提交。

1
2
3
4
5
6

CREATE TABLE my_table (
    id INT NOT NULL,
    value VARCHAR(64) NOT NULL,
    version_number INT DEFAULT 0,
    PRIMARY KEY (id)
);
  1. 读取“version_number”字段值；
  2. 执行更新操作，例如：UPDATE my_table SET value = ? WHERE id = ? AND version_number = ?；
  3. 如果上一步更新成功，则执行 UPDATE my_table SET version_number = ? WHERE id = ? AND version_number = ?；
  4. 将事务提交。

悲观锁则相反，即可能对其他用户的读写操作施加约束，所以性能开销也更大。悲观锁可以使用行级锁和表级锁实现，在多用户访问数据库的情况下，可以防止脏读取和不可重复读取。

优点：支持对数据进行细粒度、持久性的锁定，安全性高。

缺点：它降低了应用程序的整体性能，特别是在访问人数众多的情况下。

1
2
3
4

BEGIN;  
SELECT * FROM students WHERE ID = 123 FOR UPDATE;  
UPDATE students SET Name = 'John' WHERE ID = 123;  
COMMIT;

InnoDB Locking

相关内容

分布式锁

更新于 2023-03-04
阅读原始文档
 MySQL, 锁
返回 | 主页