mysql 中的悲观锁和乐观锁

悲观锁

mysql 数据库自带了锁机制,从某种意义来说“悲观锁”才能称之为,“乐观锁”是人为添加的概念。在我们的查询中,可以通过 explain 查看 sql,如果查询语句走索引,那么 mysql 会将查询到的行锁住,如果查询语句不走索引,这时候就很危险,mysql 会将整个表锁住,所以这块没有处理好会非常危险。

mysql 的悲观锁有且仅有两种: 共享锁和排它锁,而且建议仅仅用这两个名词,很多地方有什么读锁和写锁的说法,完全是不合适的。

这边建一张 table a,有两个 field idnameid 为主键。

1
2
3
4
5
6
7
8
9
10
SELECT * FROM a;

+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 4 | dd |
+----+------+
3 rows in set (0.00 sec)

共享锁

共享锁指的是在一个事务开启后,再另一个事务中是可以进行读取数据:

  1. 开启一个窗口A

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    BEGIN;

    Query OK, 0 rows affected (0.00 sec)

    SELECT * FROM a WHERE id =1 LOCK IN SHARE MODE;
    +----+------+
    | id | name |
    +----+------+
    | 1 | aa |
    +----+------+
    1 row in set (0.01 sec)
  2. 开启另一个窗口B

1
2
3
4
5
6
7
8
9
10
11
BEGIN;

Query OK, 0 rows affected (0.00 sec)

SELECT * FROM a WHERE id =1 LOCK IN SHARE MODE;
+----+------+
| id | name |
+----+------+
| 1 | aa |
+----+------+
1 row in set (0.01 sec)

这里可以看到 id=1 在另一个事务中也是可以查询到

  1. 在窗口B 中执行更新操作
1
2
3
4
UPDATE a SET name="ab" WHERE id =1;

... 漫长的等待
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这里可以发现在另一个事务中是无法更新数据。

  1. 在窗口 A 中提交事务
1
2
COMMIT;
Query OK, 0 rows affected (0.01 sec)
  1. 在窗口 B 中再次发起更新
    1
    2
    3
    UPDATE a SET name="ab" WHERE id =1;
    Query OK, 1 row affected (0.03 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

这一次发现已经成功。

  1. 在窗口 B 中查看
1
2
3
4
5
6
7
8
9
SELECT * FROM a;
+----+------+
| id | name |
+----+------+
| 1 | ab |
| 2 | bb |
| 4 | dd |
+----+------+
3 rows in set (0.00 sec)

排它锁

排它锁指的是在一个事务开启后,再另一个事务中是不可以进行读取其数据:

  1. 开启一个窗口A

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    BEGIN;
    Query OK, 0 rows affected (0.00 sec)

    SELECT * FROM a WHERE id =1 FOR UPDATE;
    +----+------+
    | id | name |
    +----+------+
    | 1 | ab |
    +----+------+
    1 row in set (0.00 sec)
  2. 开启另一个窗口B

1
2
3
4
5
6
BEGIN;
Query OK, 0 rows affected (0.00 sec)

SELECT * FROM a WHERE id =1 FOR UPDATE;
... 漫长的等待
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这里可以看到 id=1 在另一个事务中是访问不到的。

  1. 在窗口 A 中提交事务
1
2
COMMIT;
Query OK, 0 rows affected (0.00 sec)

这里可以发现在另一个事务中是无法更新数据。

  1. 在窗口 B 中再次发起查询
1
2
3
4
5
6
7
SELECT * FROM a WHERE id =1  FOR UPDATE;
+----+------+
| id | name |
+----+------+
| 1 | ab |
+----+------+
1 row in set (0.01 sec)

可以看到已经查询成功。

  1. 在窗口 A 中发起更新操作

    1
    2
    UPDATE a SET name="abc" WHERE id =1;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

    可以看到无法更新数据

  2. 在窗口 B 中提交事务

1
2
COMMIT;
Query OK, 0 rows affected (0.00 sec)
  1. 在窗口 A 中更新
    1
    2
    3
    UPDATE a SET name="abc" WHERE id =1;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1 Changed: 1 Warnings: 0
    更新成功

测试锁表

  1. 开启一个窗口 A

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    BEGIN;
    Query OK, 0 rows affected (0.00 sec)

    SELECT * FROM a WHERE name ="bb" LOCK IN SHARE MODE;
    +----+------+
    | id | name |
    +----+------+
    | 2 | bb |
    +----+------+
    1 row in set (0.00 sec)
  2. 开启一个窗口 B

    1
    2
    3
    4
    5
    BEGIN;
    Query OK, 0 rows affected (0.00 sec)

    SELECT * FROM a WHERE id =1 FOR UPDATE;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  3. 窗口 B 继续测试其他 id

    1
    2
    SELECT * FROM a WHERE id =2  FOR UPDATE;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  4. 窗口 B 更新数据

1
2
UPDATE a SET name="abcd" WHERE id =1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可以看到都获取不到锁,无法完成查询以及更新,如果发生在生产环境那就是要出事故的。

  1. 窗口 A 提交事务
1
2
COMMIT;
Query OK, 0 rows affected (0.00 sec)
  1. 窗口 B 查询数据
1
2
3
4
5
6
7
mysql> SELECT * FROM a WHERE id =1  FOR UPDATE;
+----+------+
| id | name |
+----+------+
| 1 | abc |
+----+------+
1 row in set (0.01 sec)

这里已经成功了。

乐观锁

前面的内容已经提到,乐观锁并不算锁,是人为添加的一个概念,这个概念是独立于数据库系统而是在应用层实现的一套锁机制,常见的实现方式有两种,根据更新时间戳或者增加一个字段版本号,一般的框架都会提供版本号方式的实现,laravel 中看到有 laravel-optimistic-locking 这个库他的实现也很简洁,spring boot 中有一个 @Version 注解。

在高并发的情况下,可能存在时间戳相同的情况,所以更新时间戳用的比较少。

实现原理其实很简单,添加一个版本号,每次更新数据的时候带着版本号一起查询,然后将版本号+1,由于 mysql 更新的原子性,最终只有一条数据更新成功。

示例代码:

  1. 查询表数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> SELECT * FROM a ;
    +----+------+---------+
    | id | name | version |
    +----+------+---------+
    | 1 | abc | 1 |
    | 2 | bb | 1 |
    | 4 | dd | 1 |
    +----+------+---------+
    3 rows in set (0.00 sec)
  2. 发起更新

    1
    2
    3
    UPDATE a SET name="abcd",version=2 WHERE id=1 and version=1;
    Query OK, 1 row affected (0.02 sec)
    Rows matched: 1 Changed: 1 Warnings: 0
  3. 查询数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT * FROM a ;
    +----+------+---------+
    | id | name | version |
    +----+------+---------+
    | 1 | abcd | 2 |
    | 2 | bb | 1 |
    | 4 | dd | 1 |
    +----+------+---------+
    3 rows in set (0.00 sec)

    乐观锁这个想法既简单又实用。

小结

计算机的世界里,总是有一些晦涩的名词和高大上的术语,但也正是这些名词和术语将复杂逻辑进行抽象成简单概念,以区别我们懂行的和那些听不懂的。

参考文档

坚持原创技术分享,您的支持将鼓励我继续创作!