mysql数据库乐观锁和悲观锁_MySQL学习笔记（四）悲观锁与乐观锁

恼骚

最近在搞并发的问题，订单的异步通知和主动查询会存在并发的问题，用到了Mysql数据库的 for update 锁

在TP5直接通过lock(true)，用于数据库的锁机制

Db::name('pay_order')->where('order_no',‘S1807081342018949’)->lock(true)->find();

打印生成的SQL语句

SELECT * FROM `pay_order` WHERE `order_no` = 'S1807081342018949' LIMIT 1 FOR UPDATE

上面的查询语句中，我们使用了 select…for update 的方式，这样就通过开启排他锁的方式实现了悲观锁。此时在 pay_order 表中，order_no 为S1807081342018949 的那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

上面我们提到，使用 select…for update 会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。

理解悲观锁与乐观锁

在数据库的锁机制中介绍过，数据库管理系统(DBMS)中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和一致性以及数据库的一致性。

乐观并发控制(乐观锁)和悲观并发控制(悲观锁)是并发控制主要采用的技术手段。无论是悲观锁还是乐观锁，都是人们定义出来的概念，可以认为是一种思想。其实不仅仅是数据库系统中有乐观锁和悲观锁的概念，像memcache、hibernate、tair等都有类似的概念。

针对于不同的业务场景，应该选用不同的并发控制方式。所以，不要把乐观并发控制和悲观并发控制狭义的理解为DBMS中的概念，更不要把他们和数据中提供的锁机制(行锁、表锁、排他锁、共享锁)混为一谈。其实，在DBMS中，悲观锁正是利用数据库本身提供的锁机制来实现的。

在数据库中，悲观锁的流程如下：

在对任意记录进行修改前，先尝试为该记录加上排他锁(exclusive locking)。

如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。

如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。

其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

在oracle中，利用 select * for update 可以锁表。假设有个表单products ，里面有id跟name二个栏位，id是主键。

例1: (明确指定主键，并且有此笔资料，row lock)

SELECT * FROM products WHERE id='3' FOR UPDATE;

例2: (明确指定主键，若查无此笔资料，无lock)

SELECT * FROM products WHERE id='-1' FOR UPDATE;

例3: (无主键，table lock)

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例4: (主键不明确，table lock)

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

例5: (主键不明确，table lock)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。

先开始一把

使用悲观锁的原理就是，当我们在查询出 pay_order 信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么在这个过程中，因为 pay_order 被锁定了，就不会出现其他操作者来对其进行修改了。

第一次，开启事务，但是不提交事务

异步通知

-- 开启事务

START TRANSACTION;

-- 查询订单

SELECT id,order_no,`status` FROM `pay_order` WHERE `order_no` = 'S1807081342018949' LIMIT 1 FOR UPDATE;

-- 修改订单

UPDATE `pay_order` SET `status` = 11 WHERE id = 347;

COMMIT;

-- 查询数据是否修改成功

SELECT id,order_no,`status` FROM `pay_order` WHERE `order_no` = 'S1807081342018949' LIMIT 1 FOR UPDATE;

执行结果：很快就执行完毕了，但是数据并没有修改成功(注意：但是重复执行一次，则数据又修改成功了)

主动查询

1、加锁

SELECT id,order_no,`status` FROM `pay_order` WHERE `order_no` = 'S1807081342018949' LIMIT 1 FOR UPDATE;

执行结果，一直在阻塞中

过一会，会自动取消锁机制

[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

2、不加锁

SELECT id,order_no,`status` FROM `pay_order` WHERE `order_no` = 'S1807081342018949';

执行结果，没有阻塞，则能正常查询出数据，不会受第一个事务的影响

第二次，开启事务，提交事务

异步查询开启事务，提交事务

主动查询加锁则不受影响

总结：锁如果是回滚或者提交事务，会自动释放掉锁的。

下面研究以下行锁和表锁

例1: 明确指定主键，并且有此数据，row lock

说明：通过上面的演示，可以清楚的看到，锁的是同一个记录(id = 347)，记录(id = 348)并没有受到上一条记录的影响。

例2: 明确指定主键，若查无此数据，无lock

说明：窗口1 查询结果为空。窗口2 查询结果也为空，查询无阻塞，说明 窗口1 没有对数据执行锁定。

例3：无主键，table lock

说明：

窗口1 开启了事务，查询订单号 ： order_no = "S1807081342018949"，查询数据正常。

窗口2 也开启了事务，查询订单号 ： order_no = "S1807081342018949"，查询阻塞，说明 窗口1把该记录给锁住了(其实这里表已经被锁定， 而不是该记录了)。

窗口3开启了事务，查询订单号 ： order_no = "S1807171712053133"，查询阻塞，说明 窗口1把该表给锁住了，不是同一条记录都不给查啊，阻塞的不要不要的。

只有 窗口1 的记录回滚或者提交了，窗口2的查询阻塞立刻释放掉了，但是 窗口3 依然在阻塞中(由于 窗口2开启了事务导致的)。同理，回滚或者提交 窗口2 的事务后，窗口3 的记录也可以正常查询了。

例4: 主键不明确，table lock

说明：

窗口1 开启了事务，查询主键 ： id > 375 的记录，查询数据正常(3条记录)。

窗口2 也开启了事务，查询订单号 ：  id > 375 的记录，查询阻塞，说明 窗口1把该记录给锁住了(其实这里表已经被锁定， 而不是该记录了)。

窗口3开启了事务，查询订单号 ： id > 376 的记录，查询阻塞，说明 窗口1把该表给锁住了，不是同一条记录都不给查啊，阻塞的不要不要的。

只有 窗口1 的记录回滚或者提交了，窗口2 的查询阻塞立刻释放掉了，但是 窗口3 依然在阻塞中(由于 窗口2 开启了事务导致的)。同理，回滚或者提交 窗口2 的事务后，窗口3 的记录也可以正常查询了。

例5: 主键不明确，table lock

select * from pay_order where id<>1 for update;

索引对数据库的锁定级别

例6: 明确指定索引，并且有此数据，row lock

mysql> select id,status,order_no from pay_order where status=1 for update;

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

| id | status | order_no |

|------+----------+-------------------|

| 348 | 1 | S1807081353042055 |

| 349 | 1 | S1807081356043257 |

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

13 rows in set

Time: 0.003s

注意：上面的字段 status 是建立过索引的

例7: 明确指定索引，若查无此数据，无lock

mysql> select id,status,order_no from pay_order where status=11 for update;

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

| id | status | order_no |

|------+----------+------------|

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

0 rows in set

Time: 0.001s

演示操作

1、定义索引字段开启事务加悲观锁

pay_order 表结构，order_no 定义索引。

CREATE TABLE`pay_order` (

`id`int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '#',

`order_no`varchar(255) NOT NULL COMMENT '订单号',PRIMARY KEY(`id`) USING BTREE,UNIQUE KEY`idx_order_no` (`order_no`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=514 DEFAULT CHARSET=utf8mb4 COMMENT='支付订单';

相同订单号第一次查询

mysql@:Tinywan> START TRANSACTION;

Query OK,0rows affected

Time:0.000s

mysql@:Tinywan> SELECT id,order_no,`status` FROM `pay_order` WHERE order_no = 'S64064191 -> 1161202555241' LIMIT 1 FOR UPDATE;+------+------------------------+----------+

| id | order_no | status |

|------+------------------------+----------|

| 11 | S640641911161202555241 | 1 |

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

1 row in setTime:0.001s

mysql@:Tinywan>

相同订单号第二次查询

mysql@:Tinywan> START TRANSACTION;

Query OK, 0 rows affected

Time: 0.001s

mysql@:Tinywan> SELECT id,order_no,`status` FROM `pay_order` WHERE order_no = 'S640641911161 -> 202555241' LIMIT 1 FOR UPDATE;

被阻塞掉了

不同订单号第一次查询

mysql@:Tinywan> START TRANSACTION;

Query OK,0rows affected

Time:0.000s

mysql@:Tinywan> SELECT id,order_no,`status` FROM `pay_order` WHERE

-> order_no = 'T705961911161238428844' LIMIT 1 FOR UP ->DATE;+------+------------------------+----------+

| id | order_no | status |

|------+------------------------+----------|

| 25 | T705961911161238428844 | 0 |

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

1 row in setTime:0.002s

mysql@:Tinywan>

2、未定义索引

表结构

CREATE TABLE`pay_order` (

`id`int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '#',

`order_no`varchar(255) NOT NULL COMMENT '订单号',PRIMARY KEY(`id`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=514 DEFAULT CHARSET=utf8mb4 COMMENT='支付订单';

相同订单号第一次查询

相同订单号第二次查询

不同订单号第一次查询

第一个提交事务

第二个提交事务

参考：