Home › Java › MySQL事务与Spring事务管理

MySQL事务与Spring事务管理

发表于 发表于 Java

一个案例：

在我们的系统中，需要通过定时任务来触发终端的离线事件

每次终端有数据上报时，更新终端在线状态为在线：online = 1，last_uploaded_at为上报时间

而定时任务每分钟触发一次批量操作：

  1. 批量查询出 last_uploaded_at <(当前时间 -150分钟) 的终端编号
  2. 批量将终端在线状态设置为离线 (online = 0)

简单的数据模型为：

IDonlinelast_uploaded_at
20103712019-8-30 13:00:00
20103812019-8-30 13:00:00
20103912019-8-30 13:00:00

比如当前时间为2019-8-30 15:31:00，如果往前推150分钟这三个终端没有数据上报，那么这个定时任务就会将他们更新为离线状态

那这里面存在的数据一致性问题，也就是事务问题，它是什么？

比如说 在2019-8-30 15:29:00 的时候，终端201037新上报了一条数据，但是对其对应行的更新穿插在了定时任务的SELECT和UPDATE之间，就会导致下述问题：

201037这个终端最后的数据是：last_uploaded_at 是2019-8-30 15:29:00，而在线状态为 online = 0，出现了数据的不一致，因为online被定时任务错误地更新掉了

为了解决这个问题，那就必须保证定时任务中的事务会阻塞其他事务的执行

为了达到这个目的，简单的BEGIN和COMMIT是不够的，即便隔离级别已经是REPEATABLE READ的情况下，由于MySQL使用快照，并且默认不会锁定行，这个隔离级别能带来的收益是：当前事务从BEGIN开始，只能看到事务内部的数据，可以理解为开启了一个快照，后续所有操作都是基于快照的，并且在COMMIT的时候将所有改变一次提交。同时其他事务无论未提交还是已提交的数据都对当前事务不可见，但它并没有提供锁定数据行的功能，意味着其他事务仍然可以同时对相同的行进行读写，只是读写的结果对当前事务不可见

所以我们必须在读的时候加锁，即SELECT操作上加上FOR UPDATE，对这几行进行锁定，防止其他事务的写操作，那么其他事务的更新就会阻塞并延迟到当前事务提交后再执行。

（当然我们也可以不这么做，而是在UPDATE的时候再次检查last_uploaded_at，这种做法不在当前讨论的话题中）

正确的结果应该是：201037终端最后对应的数据为online=1，last_uploaded_at = 2019-8-30 15:29:00。

https://forums.mysql.com/read.php?22,56420,57733

说说LOCK IN SHARE MODE / FOR UPDATE和锁

LOCK IN SHARE MODE（共享锁/读锁）

FOR UPDATE（排他锁/写锁）

一个事务获取到读锁，那么另一个事务可以同时获取到读锁，但无法获取到写锁

一个事务获取到写锁，那么另一个事务不能获得读锁或写锁，必须等待当前事务释放写锁

加锁或者不加锁，以及不同的加锁类行会有什么区别，下面列举一下，在REPEATABLE-READ级别下：

consistent read 即一致性非锁定读不会申请任何锁，可以理解为SELECT不加后缀就不会申请任何锁 (非SERIALIZABLE级别的情况下)

  1. 不加LOCK IN SHARE MODE / FOR UPDATE，当前事务为：BEGIN → SELECT → UPDATE → COMMIT
    1. 并发的其他事务的读操作, 包括consistent read和locking read是都不会被阻塞的
    2. 而写操作会被阻塞在当前事务UPDATE之后，COMMIT之前，因为UPDATE本身会获取一个独占锁（写锁），因为存在显示的事务，所以update申请的锁再事务结束时才会释放
  2. 加了LOCK IN SHARE MODE / FOR UPDATE，当前事务为：BEGIN → SELECT … LOCK IN SHARE MODE /  FOR UPDATE → UPDATE → COMMIT
    1. 并发的其他事务的consistent read操作仍然是都不会被阻塞的，而locking read会根据前面说到的兼容规则出现不同结果
    2. 而写操作会被阻塞在当前事务SELECT之后，COMMIT之前。因为带锁的SELECT会申请一个锁，直到事务结束时释放

这里要着重提到的一点是，transaction块本身无法提供完整的事务保障，比如我们有一个场景

当一个表里的记录条数小于100的时候，才能插入新的记录，那我们的语句可能是这样的：

BEGIN; SELECT COUNT(id) FROM `xxx`; # 代码里的逻辑判断, 如果记录条数小于100就插入新行 INSERT INTO `xxx` …; COMMIT;

那这种简单的事务块并不能真正保证数据的一致性，因为除了最高的隔离级别（即串行化级别）开启一个事务并不会对读取的数据行进行锁定，因为各个隔离级别最小化的实现只需要保证几个读的问题（脏读，不可重复读，幻读）。

而上述问题必须通过显示的加锁来保证，而且当前这个具体场景下还必须加表级锁

因为如果当前数据行是99，并且同时开启了多个事务，每个事务内看到的记录条数都是99，都会进行插入操作，最后导致数据的不一致即记录条数超过100条

MySQL官方文档中还有这么两个例子:

Suppose that you want to insert a new row into a table CHILD, and make sure that the child row has a parent row in table PARENT. Your application code can ensure referential integrity throughout this sequence of operations.First, use a consistent read to query the table PARENT and verify that the parent row exists. Can you safely insert the child row to table CHILD? No, because some other session could delete the parent row in the moment between your SELECT and your INSERT, without you being aware of it.To avoid this potential issue, perform the SELECT using LOCK IN SHARE MODE:SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;After the LOCK IN SHARE MODE query returns the parent 'Jones', you can safely add the child record to the CHILD table and commit the transaction. Any transaction that tries to acquire an exclusive lock in the applicable row in the PARENT table waits until you are finished, that is, until the data in all tables is in a consistent state.
For another example, consider an integer counter field in a table CHILD_CODES, used to assign a unique identifier to each child added to table CHILD. Do not use either consistent read or a shared mode read to read the present value of the counter, because two users of the database could see the same value for the counter, and a duplicate-key error occurs if two transactions attempt to add rows with the same identifier to the CHILD table.Here, LOCK IN SHARE MODE is not a good solution because if two users read the counter at the same time, at least one of them ends up in deadlock when it attempts to update the counter.To implement reading and incrementing the counter, first perform a locking read of the counter using FOR UPDATE, and then increment the counter. For example:SELECT counter_field FROM child_codes FOR UPDATE;UPDATE child_codes SET counter_field = counter_field + 1;
A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATEwould set on the rows.

比起FOR UPDATE, LOCK IN SHARE MODE 的问题在于更容易导致死锁（但不会出现数据的非一致性问题）

比如两组事务同时对一条记录加了读锁

然后紧接着各自又希望对加锁的记录进行写操作，那他们都会请求写锁，而写锁必须等待其他事务释放读锁，而两组事务的读锁又必须在事务提交后才释放，于是就导致了死锁

所以有一个偷懒的做法就是：尽量使用SELECT … FOR UPDATE，对应JPA的LockModeType即为PESSIMISTIC_WRITE，另一个角度上讲某些数据库并不提供行级的共享锁