Python Django MYSQL中的死锁问题及select_for_update的筛选(优化)

我在我的项目中遇到了一些死锁，其中我有多个进程在更改数据库中的相同对象.

我有一个终结点，其要点是获取最近未完成的 playbook ，如果存在则继续更改它，如果不存在，则它应该创建一个.我需要确保，如果我在这个端点收到两个并发请求，其中一个创建对象，另一个阻止执行，直到创建该请求，然后第二个请求将更改创建的对象.

在我的Django应用程序中，我有以下关于Transaction()内部上下文的查询:

# Use list to force evaluation
play = list(Play.objects.select_for_update().filter(
                game=self.game,
                user=self.user,
                discard=False,
                finished=False,
            )
        )

它曾经是.last()，但我读到它在数据库查询中执行的ORDER BY有时会引发死锁问题，所以我try 了这个方法.

根据我的理解(这可能是有缺陷的)，MySQL应该在完成的索引上获得独占记录锁(我在我的数据库中有该索引)，这将不允许在此事务发生时创建对象，并且任何试图在此事务完成后被阻止的事务都将被阻止.它还应该获取行本身的记录锁，以便任何事务都不能更改其内容

在重负载测试中，我从来没有遇到过并发问题，并且它按预期工作，但是我有时会发现数据库中的死锁，这是我对下面这段特定代码不完全理解的:

 with transaction.atomic():
        Play.objects.select_for_update().get(pk=play.pk)
        <Changes to the Play>
        play.save()

在做SHOW ENGINE INNODB STATUS次时，我得到了以下报告，为了简单起见，我对其进行了编辑:

*** (1) TRANSACTION:
TRANSACTION 901805890, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 116 lock struct(s), heap size 24696, 5 row lock(s)
SELECT `*` FROM `games_play` WHERE (`games_play`.`discard` = 0 AND `games_play`.`finished` = 0 AND `games_play`.`game_id` = 1 AND `games_play`.`user_id` = 28) FOR UPDATE

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 4268 page no 411 n bits 1616 index games_play_finished_71622b41 of table `test_dev`.`games_play` trx id 901805890 lock_mode X locks rec but not gap
Record lock, heap no 18 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 80; asc  ;;
 1: len 4; hex 8000425c; asc   B\;;

Record lock, heap no 19 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 80; asc  ;;
 1: len 4; hex 8000425d; asc   B];;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4268 page no 969 n bits 80 index PRIMARY of table `test_dev`.`games_play` trx id 901805890 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 4; hex 8000425d; asc   B];;
 1: len 6; hex 000035c07741; asc   5 wA;;
 2: len 7; hex 02000001a42d67; asc      -g;;
 3: len 1; hex 81; asc  ;;
 4: len 30; hex 0003006e00190004001d000400210008000029000c2d0004010062657473; asc    n         !    )  -    bets; (total 111 bytes);
 5: len 7; hex 63726173685f37; asc crash_7;;
 6: len 30; hex 00020073001200070019000e00002700004f00696e697469616c6f6e5f6c; asc    s          '  O initialon_l; (total 116 bytes);
 7: len 8; hex 99b07498ef076fa0; asc   t   o ;;
 8: len 8; hex 99b07498f40dce58; asc   t    X;;
 9: len 1; hex 81; asc  ;;
 10: len 4; hex 80000001; asc     ;;
 11: len 4; hex 8000000f; asc     ;;
 12: SQL NULL;


*** (2) TRANSACTION:
TRANSACTION 901805889, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
UPDATE `games_play` SET `finished` = 1, `details` = '{}', `game_token` = '1234', `jwt_token` = NULL, `game_id` = 1, `user_id` = 15, `created` = '2023-06-26', `modified` = '2023-06-26', `discard` = 1 WHERE `games_play`.`id` = 16989

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4268 page no 969 n bits 80 index PRIMARY of table `test_dev`.`games_play` trx id 901805889 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 4; hex 8000425d; asc   B];;
 1: len 6; hex 000035c07741; asc   5 wA;;
 2: len 7; hex 02000001a42d67; asc      -g;;
 3: len 1; hex 81; asc  ;;
 4: len 30; hex 0003006e00190004001d000400210008000029000c2d0004010062657473; asc    n         !    )  -    bets; (total 111 bytes);
 5: len 7; hex 63726173685f37; asc crash_7;;
 6: len 30; hex 00020073001200070019000e00002700004f00696e697469616c6f6e5f6c; asc    s          '  O initialon_l; (total 116 bytes);
 7: len 8; hex 99b07498ef076fa0; asc   t   o ;;
 8: len 8; hex 99b07498f40dce58; asc   t    X;;
 9: len 1; hex 81; asc  ;;
 10: len 4; hex 80000001; asc     ;;
 11: len 4; hex 8000000f; asc     ;;
 12: SQL NULL;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4268 page no 411 n bits 1616 index games_play_finished_71622b41 of table `test_dev`.`games_play` trx id 901805889 lock_mode X locks rec but not gap waiting
Record lock, heap no 19 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 80; asc  ;;
 1: len 4; hex 8000425d; asc   B];;

根据我对此报告的理解，第一个事务获取了已完成索引上的锁以防止插入，并试图自己获取Play It上的锁，但该锁已被另一个事务持有，该事务也希望获取已完成索引上的锁.但这些 playbook 属于不同的用户，在我看来，第一次查询永远不应该获得对该用户的锁定.

我想了解这里发生了什么，以及可以做些什么来防止这种情况发生.