处理 MySQL 事务中的延迟

2022-01-06 00:00:00 transactions mysql stored-procedures latency

我正在尝试弄清楚如何在数据库中正确设置事务并考虑潜在的延迟.

I'm trying to figure out how to correctly set up a transaction in a database, and account for potential latency.

在我的示例中，我有一个 users、keys 表，其中每个用户可以有多个键，以及一个 config 表，它指示允许每个用户拥有多少个密钥.

In my example I have a table of users, keys, where each user can have multiple keys, and a config table that dictates how many keys each user is allowed to have.

我想运行一个存储过程:

I want to run a stored procedure that:

  1. 确定是否允许给定用户请求密钥.
  2. 获取一个可用的、无人认领的钥匙.
  3. 尝试为给定用户兑换密钥.

该过程的伪代码为:

    START TRANSACTION
(1)     CALL check_permission(...,@result);
        IF (@result = 'has_permission') THEN
(2)         SET @unclaimed_key_id = (QUERY FOR RETURNING AVAILABLE KEY ID);
(3)         CALL claim_key(@unclaimed_key_id);
        END IF;
    COMMIT;

我遇到的问题是，当我在步骤 1 后模拟延迟时(通过使用 SELECT SLEEP())，这是可能的通过在第一个程序完成睡眠之前在多个会话中运行该程序(这再次模拟滞后)，当给定用户只有赎回一个的权限时，他们可以赎回多个密钥

The problem that I am running into, is that when I simulate lag after step 1, (by using SELECT SLEEP(<seconds>)), it's possible for a given user to redeem multiple keys when they only have permissions to redeem one, by running the procedure in multiple sessions before the first procedure has finished its sleep (which again, is to simulate lag)

这是表格和流程(注意:对于这个小例子，我没有考虑索引和外键，但显然我在实际项目中使用了它们).

要查看我的问题，只需在数据库中设置表和程序，然后打开两个 mysql 终端，并在第一次运行:

To see my issue just set up the tables and procedures in a database, then open two mysql terminals, and in the first run this:

CALL `P_user_request_key`(10,1,@out);
SELECT @out;

然后快速(你有 10 秒)在第二次运行:

And then quickly (you have 10 seconds) in the second run this:

CALL `P_user_request_key`(0,1,@out);
SELECT @out;

两个查询都将成功返回 key_claimed 并且用户 Bob 最终会分配给他 4 个键，尽管配置中的最大值设置为每个用户 3 个.

Both queries will successfully return key_claimed and User Bob will end up with 4 keys assigned to him, although the max value in config is set to 3 per user.

  1. 避免此类问题的最佳方法是什么?我正在尝试使用事务，但我觉得它不会专门解决此问题，并且可能会错误地执行此操作.
    • 我意识到解决问题的一种可能方法是将所有内容都封装在一个大型更新查询中，但我更愿意避免这种情况，因为我喜欢能够设置单独的过程，其中每个过程仅用于做一个单一的任务.

推荐答案

您不想将所有内容封装在一个大型查询中，因为这实际上也不能解决任何问题，只会降低可能性.

You're off the hook for not wanting to encapsulate everything in one large query, because that won't actually solve anything either, it just makes it less likely.

你需要的是行上的锁，或者是插入新行的索引上的锁.

What you need are locks on the rows, or locks on the index where the new row would be inserted.

InnoDB 使用一种称为 next-key 锁定的算法，该算法将索引行锁定与间隙锁定相结合.InnoDB 执行行级锁定的方式是，当它搜索或扫描表索引时，它会在遇到的索引记录上设置共享锁或排他锁.因此，行级锁实际上是索引记录锁.此外，索引记录上的下一个键锁也会影响该索引记录之前的间隙".也就是说，next-key 锁是一个索引记录锁加上一个在索引记录之前的间隙上的间隙锁.如果一个会话对索引中的记录 R 具有共享锁或排他锁，则另一个会话不能在索引顺序中紧靠 R 之前的间隙中插入新的索引记录.

InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the "gap" before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

http://dev.mysql.com/doc/refman/5.5/en/innodb-next-key-locking.html

那么我们如何获得排他锁?

So how do we get exclusive locks?

两个连接，mysql1 和 mysql2，每个连接都使用 SELECT ... FOR UPDATE 请求排他锁.表 'history' 有一列 'user_id' 被索引.(它也是一个外键.)没有找到任何行，所以它们看起来都正常进行，就好像不会发生任何异常一样.user_id 2808 有效，但没有任何历史记录.

Two connections, mysql1 and mysql2, each of them requesting an exclusive lock using SELECT ... FOR UPDATE. The table 'history' has a column 'user_id' which is indexed. (It's also a foreign key.) There are no rows found, so they both appear to proceed normally as if nothing unusual is going to happen. The user_id 2808 is valid but has nothing in history.

mysql1> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql2> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql1> select * from history where user_id = 2808 for update;
Empty set (0.00 sec)

mysql2> select * from history where user_id = 2808 for update;
Empty set (0.00 sec)

mysql1> insert into history(user_id) values (2808);

...我没有得到我的提示...没有响应...因为另一个会话也有一个锁...但是:

... and I don't get my prompt back ... no response ... because another session has a lock, too ... but then:

mysql2> insert into history(user_id) values (2808);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

然后 mysql1 立即返回成功插入.

Then mysql1 immediately returns success on the insert.

Query OK, 1 row affected (3.96 sec)

剩下的就是 mysql1 到 COMMIT 并且神奇的是，我们阻止了 0 个条目的用户插入超过 1 个条目.发生死锁是因为两个会话都需要发生不兼容的事情:mysql1 需要 mysql2 释放其锁才能提交，而 mysql2 需要 mysql1 释放其锁才能插入.必须有人输掉这场战斗，通常工作最少的线程是输家.

All that is left is for mysql1 to COMMIT and magically, we prevented a user with 0 entries from inserting more than 1 entry. The deadlock occurred because both sessions needed incompatible things to happen: mysql1 needed mysql2 to release its lock before it would be able to commit and mysql2 needed mysql1 to release its lock before it would be able to insert. Somebody has to lose that fight, and generally the thread that has done the least work is the loser.

但是如果在我执行 SELECT ... FOR UPDATE 时已经存在 1 行或更多行怎么办?在这种情况下，锁会在行上，所以第二个尝试 SELECT 的会话实际上会阻塞等待 SELECT 直到第一个会话决定 SELECTcode>COMMIT 或 ROLLBACK，此时第二个会话将看到准确的行数计数(包括第一个会话插入或删除的任何行数)并且可以准确地决定用户已经达到了允许的最大值.

But what if there had been 1 or more rows already existing when I did the SELECT ... FOR UPDATE? In that case, the lock would have been on the rows, so the second session to try to SELECT would actually block waiting for the SELECT until the first session decided to either COMMIT or ROLLBACK, at which time the second session would have seen an accurate count of the number of rows (including any inserted or deleted by the first session) and could have accurately decided the user already had the maximum allowed.

您无法超越竞争条件，但可以将其锁定.

You can't outrace a race condition, but you can lock them out.

相关文章