MYSQL select for update/lock in share mode 对事务并发性影响

最新推荐文章于 2021-02-17 19:03:02 发布

暄夏之夜

最新推荐文章于 2021-02-17 19:03:02 发布

阅读量435

点赞数 1

本文深入探讨了SQL中select for update和select lock in share mode的使用场景及其对事务并发性的影响，通过具体案例展示了如何利用这两种机制确保数据一致性。

select for update/lock in share mode 对事务并发性影响

事务并发性理解

事务并发性，粗略的理解就是单位时间内能够执行的事务数量，常见的单位是 TPS（ transactions per second）.

那在数据量和业务操作量一定的情况下，常见的提高事务并发性主要考虑的有哪几点呢？

1.提高服务器的处理能力，让事务的处理时间变短。

这样不仅加快了这个事务的执行时间，也降低了其他等待该事务执行的事务执行时间。

2.尽量将事务涉及到的 sql 操作语句控制在合理范围，换句话说就是不要让一个事务包含的操作太多或者太少。

在业务繁忙情况下，如果单个事务操作的表或者行数据太多，其他的事务可能都在等待该事务 commit或者 rollback，这样会导致整体上的 TPS 降低。但是，如果每个 sql 语句都是一个事务也是不太现实的。一来，有些业务本身需要多个sql语句来构成一个事务（比如汇款这种多个表的操作）；二来，每个 sql 都需要commit，如果在 mysql 里 innodb_flush_log_at_trx_commit=1 的情况下，会导致 redo log 的刷新过于频繁，也不利于整体事务数量的提高（IO限制也是需要考虑的重要因素）。

3.在操作的时候，尽量控制锁的粒度，能用小的锁粒度就尽量用锁的粒度，用完锁资源后要记得立即释放，避免后面的事务等待。

但是有些情况下，由于业务需要，或者为了保证数据的一致性的时候，必须要增加锁的粒度，这个时候就是下面所说的几种情况。

select for update 理解

select col from t where where_clause for update 的目的是在执行这个 select 查询语句的时候，会将对应的索引访问条目进行上排他锁（X 锁），也就是说这个语句对应的锁就相当于update带来的效果。

那这种语法为什么会存在呢？肯定是有需要这种方式的存在啦！！请看下面的案例描述：

案例1：

前提条件：

mysql 隔离级别 repeatable-read ,

事务1：

建表：
CREATE TABLE `lockt` (
  `id` int(11) NOT NULL,
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col1_ind` (`col1`),
  KEY `col2_ind` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入数据 。。。。。

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from lockt;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    3 |
|  5 |    5 |    5 |
|  6 |    6 |    9 |
|  7 |    7 |   14 |
|  8 |    8 |   20 |
+----+------+------+
6 rows in set (0.00 sec)

然后另外一个事务2 进行了下面的操作：

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

mysql> select * from lockt;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    3 |
|  5 |    5 |    5 |
|  6 |    6 |    9 |
|  7 |    7 |   14 |
|  8 |    8 |   20 |
+----+------+------+
6 rows in set (0.00 sec)

mysql> update lockt set  col2= 144  where col2=14;  
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

结果：可以看到事务2 将col2=14 的列改为了 col2=144.

可是事务1继续执行的时候根本没有觉察到 lockt 发生了变化，请看事务1 继续后面的操作：

mysql> select * from lockt;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    3 |
|  5 |    5 |    5 |
|  6 |    6 |    9 |
|  7 |    7 |   14 |
|  8 |    8 |   20 |
+----+------+------+
6 rows in set (0.01 sec)

mysql> update lockt set  col2=col2*2  where col2=14;    
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

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

mysql> select * from lockt;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    3 |
|  5 |    5 |    5 |
|  6 |    6 |    9 |
|  7 |    7 |  144 |
|  8 |    8 |   20 |
+----+------+------+
6 rows in set (0.00 sec)

结果：事务1 明明查看到的存在 col2=12 的行数据，可是 update 后，竟然不仅没有改为他想要的col2=28 的值，反而变成了 col2=144 ！！！！

这在有些业务情况下是不允许的，因为有些业务希望我通过 select * from lockt; 查询到的数据是此时数据库里面真正存储的最新数据，并且不允许其他的事务来修改只允许我来修改。（这个要求很霸气，但是我喜欢。。）

这种情况就是很牛逼的情况了。具体的细节请参考下面的案例2：

案例2：

mysql 条件和案例1 一样。

事务1操作:

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

mysql> select * from lockt where col2=20 for update;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  8 |    8 |   20 |
+----+------+------+
1 row in set (0.00 sec)

事务2 操作：

mysql> select * from lockt;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    3 |
|  5 |    5 |    5 |
|  6 |    6 |    9 |
|  7 |    7 |  144 |
|  8 |    8 |   20 |
+----+------+------+
6 rows in set (0.00 sec)

mysql> update lockt set  col2=222  where col2=20;

注意：事务2 在执行 update lockt set col2=222 where col2=20; 的时候，会发现 sql 语句被 block住了，为什么会发现这种情况呢？

因为事务1 的 select * from lockt where col2=20 for update; 语句会将 col2=20 这个索引的入口给锁住了，（其实有些时候是范围的索引条目也被锁住了，暂时不讨论。），那么事务2虽然看到了所有的数据，但是想去修改 col2=20 的行数据的时候，事务1 只能说 “不可能也不允许”。

后面只有事务1 commit或者rollback 以后，事务2 的才能够修改 col2=20 的这个行数据。

总结：

这就是 select for update 的使用场景，为了避免自己看到的数据并不是数据库存储的最新数据并且看到的数据只能由自己修改，需要用 for update 来限制。

select lock in share mode 理解

如果看了前面的 select *** for update ，就可以很好的理解 select lock in share mode ，in share mode 子句的作用就是将查找到的数据加上一个 share 锁，这个就是表示其他的事务只能对这些数据进行简单的select 操作，并不能够进行 DML 操作。

那它和 for update 在引用场景上究竟有什么实质上的区别呢？

lock in share mode 没有 for update 那么霸道，所以它有时候也会遇到问题，请看案例3

案例3：

mysql 环境和案例1 类似

事务1：

mysql> select * from lockt;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    3 |
|  5 |    5 |    5 |
|  6 |    6 |    9 |
|  7 |    7 |  144 |
|  8 |    8 |   20 |
+----+------+------+
6 rows in set (0.00 sec)

mysql> select * from lockt where col2=20 lock in share mode;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  8 |    8 |   20 |
+----+------+------+
1 row in set (0.00 sec)

事务2 接着开始操作

mysql> select * from lockt;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    3 |
|  5 |    5 |    5 |
|  6 |    6 |    9 |
|  7 |    7 |  144 |
|  8 |    8 |   20 |
+----+------+------+
6 rows in set (0.00 sec)

mysql> select * from lockt where col2=20 lock in share mode;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  8 |    8 |   20 |
+----+------+------+
1 row in set (0.01 sec)

后面的比较蛋疼的一幕出现了，当事务1 想更新 col2=20 的时候，他发现 block 住了。

mysql> update lockt set col2=22 where col2=20;

解释：因为事务1 和事务2 都对该行上了一个 share 锁，事务1 以为就只有自己一个人上了 S 锁，所以当事务一想修改的时候发现没法修改，这种情况下，事务1 需要使用 for update 子句来进行约束了，而不是使用 for share 来使用。

可能用到的情景和对性能的影响

使用情景：

1. select *** for update 的使用场景

为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句。

2. select *** lock in share mode 使用场景

为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据使用了 in share mode 的方式上了 S 锁。

性能影响：

select for update 语句，相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。

select lock in share mode 语句是一个给查找的数据上一个共享锁（S 锁）的功能，它允许其他的事务也对该数据上 S锁，但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

for update 和 lock in share mode 的区别：前一个上的是排他锁（X 锁），一旦一个事务获取了这个锁，其他的事务是没法在这些数据上执行 for update ；后一个是共享锁，多个事务可以同时的对相同数据执行 lock in share mode。