laravel mysql 悲观锁_mysql 中的悲观锁和乐观锁

悲观锁

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

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

这边建一张 table a，有两个 field id 和 name，id 为主键。

SELECT * FROM a;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 4 | dd |

+----+------+

3 rows in set (0.00 sec)

共享锁

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

开启一个窗口A

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)

开启另一个窗口B

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 在另一个事务中也是可以查询到

在窗口B 中执行更新操作

UPDATE a SET name="ab" WHERE id =1;

... 漫长的等待

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

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

在窗口 A 中提交事务

COMMIT;

Query OK, 0 rows affected (0.01 sec)

在窗口 B 中再次发起更新

UPDATE a SET name="ab" WHERE id =1;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

这一次发现已经成功。

在窗口 B 中查看

SELECT * FROM a;

+----+------+

| id | name |

+----+------+

| 1 | ab |

| 2 | bb |

| 4 | dd |

+----+------+

3 rows in set (0.00 sec)

排它锁

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

开启一个窗口A

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)

开启另一个窗口B

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 在另一个事务中是访问不到的。

在窗口 A 中提交事务

COMMIT;

Query OK, 0 rows affected (0.00 sec)

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

在窗口 B 中再次发起查询

SELECT * FROM a WHERE id =1 FOR UPDATE;

+----+------+

| id | name |

+----+------+

| 1 | ab |

+----+------+

1 row in set (0.01 sec)

可以看到已经查询成功。

在窗口 A 中发起更新操作

UPDATE a SET name="abc" WHERE id =1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可以看到无法更新数据

在窗口 B 中提交事务

COMMIT;

Query OK, 0 rows affected (0.00 sec)

在窗口 A 中更新

UPDATE a SET name="abc" WHERE id =1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

更新成功

测试锁表

开启一个窗口 A

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)

开启一个窗口 B

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

窗口 B 继续测试其他 id

SELECT * FROM a WHERE id =2 FOR UPDATE;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

窗口 B 更新数据

UPDATE a SET name="abcd" WHERE id =1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

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

窗口 A 提交事务

COMMIT;

Query OK, 0 rows affected (0.00 sec)

窗口 B 查询数据

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 更新的原子性，最终只有一条数据更新成功。

示例代码：

查询表数据

mysql> SELECT * FROM a ;

+----+------+---------+

| id | name | version |

+----+------+---------+

| 1 | abc | 1 |

| 2 | bb | 1 |

| 4 | dd | 1 |

+----+------+---------+

3 rows in set (0.00 sec)

发起更新

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

查询数据

SELECT * FROM a ;

+----+------+---------+

| id | name | version |

+----+------+---------+

| 1 | abcd | 2 |

| 2 | bb | 1 |

| 4 | dd | 1 |

+----+------+---------+

3 rows in set (0.00 sec)

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

小结

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

参考文档