MySQL InnoDB SELECT ... LIMIT 1 FOR UPDATE与UPDATE ... LIMIT 1 [英] MySQL InnoDB SELECT...LIMIT 1 FOR UPDATE Vs UPDATE ... LIMIT 1

问题描述

我在具有InnoDB引擎的MySQL中有一个表v_ext:
-id:主键
-代码:预先生成的代码列表(例如随机生成1000个代码)
-user_id:最初为NULL

I have a table v_ext in a MySQL with InnoDB engine:
- id: primary key
- code: pre-generated list of codes (say 1000 codes are generated randomly)
- user_id: initially NULL

当用户购买商品时，他们会收到一个代码.我需要更新表以填充user_id列.我有两个选择:

When a user purchase an item, they receive a code. I need to update the table to populate the user_id column. I have two options:

START TRANSACTION;
SELECT id FROM v_ext WHERE user_id IS NULL LIMIT 1 FOR UPDATE; -- return id 54 for ex.
UPDATE v_ext SET user_id=xxx WHERE id=54;
COMMIT;

或

UPDATE v_ext SET user_id=xxx WHERE user_id IS NULL LIMIT 1;

如果我有成千上万的用户同时购买，第二种选择是否安全?如果是这样，那么假设第二个选项只需要一个查询就可以提高性能，是否正确?

Is the second option safe if I have thousands of users purchasing at the same time? If so, is it correct to assume this second option is better for performance since it needs only one query?

推荐答案

由于没有得到答案，所以我开始进行基准测试.我的标准如下:

Since I didn't get an answer, I started doing benchmarking. My criteria are as follows:

• 20,000个预生成的代码
• 使用Apache ab命令处理20,000个请求，并发100:ab -n 20000 -c 100
• Servlet-> EJB(JPA 2.0 EclipseLink，JTA)以在DB中执行更新(因为在实际情况下将通过JSF操作进行更新)
• 2个版本的Servlet，一个带有选项1(SELECT ... FOR UPDATE)，另一个带有选项2(UPDATE ... LIMIT 1)
• 停止了Glassfish，手动按了5次经过测试的Servlet进行预热，将user_id全部重置为NULL
• 每个测试运行3次，并提供平均值

• 20,000 pre-generated codes
• Use of Apache ab command with 20,000 requests, 100 concurrency: ab -n 20000 -c 100
• Servlet -> EJB (JPA 2.0 EclipseLink, JTA) to perform the update in DB (as it will be through a JSF action in real situation)
• 2 versions of the Servlet, one with option 1 (SELECT ... FOR UPDATE ), and one with option 2 (UPDATE ... LIMIT 1)
• Stopped Glassfish, hit the tested Servlet manually 5 times to warm it up, reset all to NULL to user_id
• Tests are run 3 times each and average is provided

结果:

选择...进行更新;更新...:

SELECT ... FOR UPDATE; UPDATE ... :

Concurrency Level:      100
Time taken for tests:   758.116 seconds
Complete requests:      20000
Failed requests:        0
Write errors:           0
Row updated:            20000

更新......限制1:

UPDATE.... LIMIT 1:

Concurrency Level:      100
Time taken for tests:   773.659 seconds
Complete requests:      20000
Failed requests:        0
Write errors:           0
Row updated:            20000

因此，至少在我的系统上，带有2个查询的选项似乎比一个查询更有效.我没想到:)

So at least on my system, the option with 2 queries seems more efficient than the one query. I didn't expect that :)

这篇关于MySQL InnoDB SELECT ... LIMIT 1 FOR UPDATE与UPDATE ... LIMIT 1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！