不同的交易必须保证选择不同的项目；避免争论 [英] Different transactions must guarantedly select different items; avoid contentions

问题描述

作为注册新用户的一部分；我们从预编译列表(表)中为它们分配资源(在本例中为 Solr 核心).

As part of registering a new user; we assign them a resource (a Solr core, in this case) from a precompiled list (a table).

如果有 5 个用户注册，则必须为他们分配 5 个不同的内核；如果用户成功注册，则分配是最终的(见我下面的描述).

If 5 users sign up, they must be assigned 5 different cores; if the user successfully registers, the assignment is made final (see my depiction below).

但在现实世界中，同时注册新用户竞争同一行，而不是选择不同的行.如果 X 需要 5 秒来注册，则在 X 的持续时间"内的 Y 和 Z 的注册将失败，因为它们被 X 争用同一行.

But in real world, concurrently registering new users contend for the same row, not choose different rows. If X needs 5 seconds to register, Y and Z's registrations which are in X's "duration" will fail as they contend for the same row by X.

问题:即使在每秒 100 次注册等高并发情况下，如何让交易选择无争用?

Question: How to make transactions choose without contentions, even under high concurrency such as 100 signups a second?

table: User
user_id   name  core   
      1    Amy h1-c1
      2    Anu h1-c1
      3    Raj h1-c1
      4    Ron h1-c2
      5    Jon h1-c2

table: FreeCoreSlots
core_id  core status   
      1 h1-c1   used
      2 h1-c1   used
      3 h1-c1   used
      4 h1-c2   used
      5 h1-c2   used #these went to above users already
      6 h1-c2   free
      7 h1-c2   free
      8 h1-c2   free
      9 h1-c2   free

伪代码，如果是孤立的东西:

Pseudo-code if stuff was isolated:

sql = SQLTransaction()
core_details = sql.get("select * from FreeCoreSlots limit 1")
sql.execute("update FreeCoreSlots set status = 'used' where id = {id}".format(
   id = core_details["id"]))
sql.execute("insert into users (name,core) values ({name},{core})".format(
   name = name,
   id   = core_details["id"]))
sql.commit()

<小时>

如果每秒发生 100 个注册，他们将竞争 FreeCoreSlots 中的第一行并导致严重失败.

---

If 100 signups happen a second, they would contend for the first row in FreeCoreSlots and cause serious fails.

有一个选择...进行更新，如 InnoDB SELECT ... FOR UPDATE 语句锁定表中的所有行 作为解决方案，但他们似乎建议降低隔离.这种方法对吗?

There is a select... for update as in InnoDB SELECT ... FOR UPDATE statement locking all rows in a table as the solution, but they seem to suggest lowering the isolation. Is this method the right way?

推荐答案

我想问的问题是为什么用户可能需要 5 秒才能完成.START TRANSACTION 和 COMMIT 之间应该只有几分之一秒.

The question I would ask is why it could take 5 seconds for a user to complete. Between the START TRANSACTION and the COMMIT should be only fractions of a second.

为了防止您将 FreeCoreSlots 中的同一行分配给相同的用途，您必须使用 SELECT for UPDATE.在我看来，锁定级别并不是真正的问题.FreeCoreSlots 中下一个空闲行的数据库设计方式实际上是锁定的，直到事务完成.下面请看我的测试结果.而且我确实认为即使每秒 100 个新用户也应该足够了.但是，如果您甚至想克服这个问题，您必须找到一种方法将 FreeCoreSlots 中的另一个空闲行锁定给每个用户.不幸的是，没有选择第一行，除非它有锁"的功能.也许改用一些随机或模数逻辑.但正如我已经说过的那样，我认为即使对于每秒 100 个新用户的不可思议的数量，这也不应该成为您的问题.如果我对此有误，请随时发表评论，我愿意再看一遍.

To prevent that you assign the same row in FreeCoreSlots to the same use another time you must use SELECT for UPDATE. The lock level is not really the problem in my opinion. The way you designed the database the next free row in FreeCoreSlots is actually locked until the transaction is done. See my test results below. And I do think that even for 100 new users per second it should still be sufficient. But if you even want to overcome this you must find a way to lock another next free row in FreeCoreSlots to every user. Unfortunately there is no function of "Select first row unless it has a lock". Maybe instead use some random or modulus logic. But as I said already I think that should not be your problem even for the unthinkable amount of 100 new users per second. If I err with this feel free to leave a comment and I am willing to have another look at it.

这是我的测试结果.默认 InnoDB 锁定级别:可重复读取，无需 FOR UPDATE.这样就行不通了.

Here is my test result. Default InnoDB lock level: repeatable read without FOR UPDATE. This way it does NOT work.

User 1:
    START TRANSACTION
    SELECT * FROM FreeCoreSlots WHERE status = 'FREE' LIMIT 1 -- returns id 1
User 2:
    START TRANSACTION
    SELECT * FROM FreeCoreSlots WHERE status = 'FREE' LIMIT 1 -- also returns id 1 !!!
User 1:
    UPDATE FreeCoreSlots SET status = 'USED' where ID = 1;
User 2:
    UPDATE FreeCoreSlots SET status = 'USED' where ID = 1; -- WAITS !!!
User 1:
    INSERT INTO user VALUES (...
    COMMIT;
USER 2:
    wait ends and updates also ID = 1 which is WRONG

锁定级别可重复读取，但带有FOR UPDATE.这样就行了.

Lock level repeatable read but with FOR UPDATE. This way it does WORK.

User 1:
    START TRANSACTION
    SELECT * FROM FreeCoreSlots WHERE status = 'FREE' LIMIT 1 FOR UPDATE -- returns id 1
User 2:
    START TRANSACTION
    SELECT * FROM FreeCoreSlots WHERE status = 'FREE' LIMIT 1 FOR UPDATE -- WAITS
User 1:
    UPDATE FreeCoreSlots SET status = 'USED' where ID = 1;
User 2:
    still waits
User 1:
    INSERT INTO user VALUES (...
    COMMIT;
USER 2:
    Gets back Id 2 from the select as the next free Id

这篇关于不同的交易必须保证选择不同的项目；避免争论的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！