MySQL InnoDB SELECT...LIMIT 1 FOR UPDATE 与 UPDATE ...LIMIT 1

2022-01-17 00:00:00 sql-update mysql

我在带有 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 :)

相关文章