不同的交易必须保证选择不同的项目；避免争执

Question

作为注册新用户的一部分；我们从预编译列表（表）中为他们分配资源（在这种情况下，为Solr核心）。

如果有5个用户注册，则必须为他们分配5个 不同的内核 ；如果用户成功注册，则分配完成（请参见下面的说明）。

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

问题： 即使在高并发率（例如每秒100次注册）下，如何使交易选择而没有争用？

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

伪代码（如果隔离了东西）：

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并导致严重的失败。

有一个select … for更新，如InnoDB SELECT … FOR
UPDATE语句锁定表中的所有行作为解决方案，但它们似乎建议降低隔离度。这种方法正确吗？

Answer 1

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

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

这是我的测试结果。默认的InnoDB锁定级别：不带的 可重复读取FOR UPDATE。这样就行不通了。

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。这样就可以了。

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