使用 Django 的 get_or_create 在创建时共享锁上死锁 MySQL

问：

我正在我的数据库上运行一个脚本，该脚本同时运行多个线程。他们正在运行以下代码：

with transaction.atomic():
                (
                    aggregated_report,
                    created,
                ) = db_model.objects.select_for_update().get_or_create(
                    currency=currency,
                    date=today_date,
                    aggregator=aggregator,
                    type=transaction.type,
                    **kwargs,
                    defaults={"value": transaction.value},
                )
                if not created:
                    aggregated_report.value += transaction.value
                    aggregated_report.save()

当我运行脚本时，当 get_or_create（） 没有找到对象并且必须创建它时，我遇到了死锁。从应用程序的角度来看，我只是捕获 OperationalError 并重试。

这是我执行 SHOW ENGINE INNODB STATUS 时来自 MySQL 的死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-10-30 16:26:39 281472641544064
*** (1) TRANSACTION:
TRANSACTION 2108849, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
SELECT `reports_aggregatedvalues`.`id`, `reports_aggregatedvalues`, `reports_aggregatedvalues`.`currency`, `reports_aggregatedvalues`.`date`, `reports_aggregatedvalues`.`type`, `reports_aggregatedvalues`.`aggregator`, `reports_aggregatedvalues`.`value` FROM `reports_aggregatedvalues` WHERE (`reports_aggregatedvalues`.`aggregator` = '---' AND `reports_aggregatedvalues`.`currency` = 'USD' AND `reports_aggregatedvalues`.`date` = '2023-10-09 00:00:00' AND `reports_aggregatedvalues` AND `reports_aggregatedvalues`.`type` = 'TRANSFER') LIMIT 21 FOR UPDATE
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 26 page no 5 n bits 384 index Unique Report of table `phoenix`.`reports_aggregatedvalues` trx id 2108849 lock mode S
Record lock, heap no 313 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 5 n bits 384 index Unique Report of table `phoenix`.`reports_aggregatedvalues` trx id 2108849 lock_mode X locks rec but not gap waiting
Record lock, heap no 313 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 2108848, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
SELECT `reports_aggregatedvalues`.`id`, `reports_aggregatedvalues`, `reports_aggregatedvalues`.`currency`, `reports_aggregatedvalues`.`date`, `reports_aggregatedvalues`.`type`, `reports_aggregatedvalues`.`aggregator`, `reports_aggregatedvalues`.`value` FROM `reports_aggregatedvalues` WHERE (`reports_aggregatedvalues`.`aggregator` = '----' AND `reports_aggregatedvalues`.`currency` = 'USD' AND `reports_aggregatedvalues`.`date` = '2023-10-09 00:00:00' AND `reports_aggregatedvalues` AND `reports_aggregatedvalues`.`type` = 'TRANSFER') LIMIT 21 FOR UPDATE
​
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 26 page no 5 n bits 384 index Unique Report of table `phoenix`.`reports_aggregatedvalues` trx id 2108848 lock mode S
Record lock, heap no 313 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
​
​
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 5 n bits 384 index Unique Report of table `phoenix`.`reports_aggregatedvalues` trx id 2108848 lock_mode X locks rec but not gap waiting
Record lock, heap no 313 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
​
*** WE ROLL BACK TRANSACTION (2)

根据我的理解，发生这种情况是因为在数据库级别，get_or_create首先获取共享锁以进行 SELECT FOR UPDATE，然后尝试升级到独占锁，如果有 2 个线程同时运行并且它们都获得了读取所需的共享锁，它们正在等待彼此释放共享锁，从而导致死锁。

这在我的脑海中是有道理的，但是在运行这个脚本时，我基本上有数千个更新和 5 或 10 个创建，但是，每次我运行它时，死锁只会发生在创建时。

据我了解，通过transaction.atomic块获取的数据库锁只有在我们离开该块后才会释放。但我的问题是：那么，考虑到这也需要从共享锁升级到独占锁，我是否也应该在脚本的这一部分面临死锁？

 aggregated_report.value += transaction.value
 aggregated_report.save()

我是否还有其他方法可以实现此行为以及完全没有死锁？或者只是重试它是否足以满足此用例的需求？