MySQL Select... for update with index 有并发问题 [英] MySQL Select... for update with index has concurrency issue

问题描述

这是对我上一个问题的跟进(你可以跳过它，因为我在这篇文章中解释了这个问题):
MySQL InnoDB SELECT...LIMIT 1 FOR UPDATE与更新...限制 1

This is a follow up on my previous question (you can skip it as I explain in this post the issue):
MySQL InnoDB SELECT...LIMIT 1 FOR UPDATE Vs UPDATE ... LIMIT 1

环境:

• 基于 Glassfish 的 JSF 2.1
• JPA 2.0 EclipseLink 和 JTA
• MySQL 5.5 InnoDB 引擎

我有一张桌子:

CREATE TABLE v_ext (
  v_id INT NOT NULL AUTO_INCREMENT,
  product_id INT NOT NULL,
  code VARCHAR(20),
  username VARCHAR(30),
  PRIMARY KEY (v_id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

它填充了 20,000 条这样的记录(product_id 为所有记录为 54，code 随机生成且唯一，用户名设置为 NULL):

It is populated with 20,000 records like this one (product_id is 54 for all records, code is randomly generated and unique, username is set to NULL):

v_id     product_id    code                  username
-----------------------------------------------------
1        54            '20 alphanumerical'   NULL
...
20,000   54            '20 alphanumerical'   NULL

当用户购买产品 54 时，他会从该表中获得一个代码.如果用户多次购买，他每次都会得到一个代码(用户名没有唯一限制).因为我正在为一项高强度的活动做准备，所以我想确保:

When a user purchase product 54, he gets a code from that table. If the user purchases multiple times, he gets a code each times (no unique constraint on username). Because I am preparing for a high activity I want to make sure that:

• 不会发生并发/死锁
• 性能不受所需的锁定机制的影响

从 SO 问题(见上面的链接)我发现做这样的查询更快:

From the SO question (see link above) I found that doing such a query is faster:

START TRANSACTION;
SELECT v_id FROM v_ext WHERE username IS NULL LIMIT 1 FOR UPDATE;
// Use result for next query
UPDATE v_ext SET username=xxx WHERE v_id=...;
COMMIT;

但是，我仅在 username 列上使用索引时才发现死锁问题.我想添加一个索引将有助于加快一点速度，但它会在大约 19,970 条记录(实际上在这个行数上非常一致)后造成死锁.是否有一个原因?我不明白.谢谢.

However I found a deadlock issue ONLY when using an index on username column. I thought of adding an index would help in speeding up a little bit but it creates a deadlock after about 19,970 records (actually quite consistently at this number of rows). Is there a reason for this? I don't understand. Thank you.

推荐答案

从纯理论的角度来看，看起来您没有锁定正确的行(第一条语句中的条件与更新语句中的条件不同；除了您由于LIMIT 1，只锁定一行，而您可能稍后更新更多行).

From a purely theoretical point of view, it looks like you are not locking the right rows (different condition in the first statement than in the update statement; besides you only lock one row because of LIMIT 1, whereas you possibly update more rows later on).

试试这个:

START TRANSACTION;
SELECT v_id FROM v_ext WHERE username IS NULL AND v_id=yyy FOR UPDATE;
UPDATE v_ext SET username=xxx WHERE v_id=yyy;
COMMIT;

至于你死锁的原因，这是可能的答案(来自手册):

As for the reason for your deadlock, this is the probable answer (from the manual):

如果您没有适合您的语句的索引并且 MySQL 必须扫描整个表要处理的语句，表的每一行被锁定 (...)

If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked (...)

如果没有索引，SELECT ... FOR UPDATE 语句可能会锁定整个表，而如果有索引，它只会锁定一些行.因为您没有在第一条语句中锁定正确的行，所以在第二条语句中会获得一个额外的锁.

Without an index, the SELECT ... FOR UPDATE statement is likely to lock the entire table, whereas with an index, it only locks some rows. Because you didn't lock the right rows in the first statement, an additional lock is acquired during the second statement.

显然，如果整个表被锁定(即没有索引)，死锁就不会发生.在第二次设置中肯定会出现死锁.

Obviously, a deadlock cannot happen if the whole table is locked (i.e. without an index). A deadlock can certainly occur in the second setup.

这篇关于MySQL Select... for update with index 有并发问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！