如何保证数据库读写事务

场景

先讲下我当时遇到的一个应用场景：

一份含有5万条数据的表，希望每次都能从中获取到一条未被使用过的数据，然后再标记该条数据已被使用。

数据库时mysql，为方便起见，转化成sql的语义就是：

一张表：

value	status
v1	1
v2	1
...	1

 

 

 

 

我们需要一次读取一行数据，再把该行的status字段改成0。它必然涉及到一次select，一次update，那如何保证的事务呢？

特别说明：表结构并不是必须这样，你可以自己设计。

 

解决思路

如果你觉得很简单：直接select这条数据，然后update它即可。那就真的悲剧了。

比如说这样的读写SQL

SELECT value FROM table WHERE status = 1 LIMIT 1
UPDATE table SET status = 0 WHERE value = 'v1'

如果每次请求都如下图一样，是依次顺序执行，的确是没有任何问题

但是如果应用的并发量很高时，这样就会出现问题，比如下图中，用户2在用户1执行完毕select但没执行完毕update时，执行了select。那么用户1和用户2将获得同一条数据，这明显不是我们想要的结果。

那又什么好的解决方案呢？相信很多人立马想到的方案肯定是事务。不错，用事务的确是种不错的解决方案

解决方案一：事务 

START TRANSACTION;
SELECT value FROM table WHERE status = 1 LIMIT 1
UPDATE table SET status = 0 WHERE value = 'v1'
COMMIT;或rollback;

 但毕竟事务是种很影响性能的方法, 那有没有可以不用事务的方法呢？

解决方案二：根据UPDATE结果判定是否有效

依据的原理是，在Mysql中执行UPDATE语句时，它会返回执行结果，具体如下：

mysql> UPDATE table SET status = 0 WHERE value = 'v1' AND status = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

这里 Rows matched 表示匹配的数量，Changed表示改动的行数

回到刚到的问题，我们可以用这些信息来判断是否成功

SELECT value FROM table WHERE status = 1 LIMIT 1
UPDATE table SET status = 0 WHERE value = 'v1' AND status = 1

当返回结果是：

Rows matched: 1  Changed: 1  Warnings: 0

表示这次数据更新是有效的。

当返回结果是：

Rows matched: 0  Changed: 0  Warnings: 0

表示这次数据更新失败，因为该行数据已经别人占用，需要重试

但这种方法的问题，就是并发量特别高时，很多请求会出现冲突，需要重试。

解决方案三：利用表的自增id属性

id	value	status
1	v1	1
2	v2	1

 

 

 

id 是该表的自增主键（auto_increment)

需要另外一张表table2

id	...
1	...
2	...
...	...

 

 

 

 

id 也是该表的自增主键（auto_increment)

 

INSERT INTO table2 ( ....) VALUES( .... );
获取到table2 中最近的id
UPDATE table SET status = 0 WHERE id = id
SELECT value FROM table WHERE id = id

 由于自增id是唯一性的，所以可以保证最终得到的数据也是唯一性的，但缺点也非常明显：多需要一张表，而且两张表的id需要一定的映射关系。

解决方案四：引入requestID字段

表设置为：

value	status	request_id
v1	1
v2	1

 

 

 

使用SQL:

UPDATE table SET status = 0, request_id = 'xxxxx'  WHERE status = 1 LIMIT 1
SELECT value FROM table WHERE request_id = 'xxxxx'

只要保证request_id是唯一性，我们得到的结构也肯定是有效的

 

希望对大家有所帮助。^v^