When using update_or_create in the presence of concurrent requests, it can lead to deadlocks.

[pangxiaobin]

Describe the bug A clear and concise description of what the bug is. Whilst employing tortoise-orm update_or_create within my FastAPI framework, I encountered a predicament wherein deadlocks arose during the execution of concurrent coroutines. I conducted several tests with tortoise-orm and encountered the subsequent error message:

Traceback (most recent call last):
  File "/Users/hubery/.pyenv/versions/ldcts/lib/python3.10/site-packages/tortoise/models.py", line 1060, in get_or_create
    await cls.select_for_update().filter(**kwargs).using_db(connection).get(),
  File "/Users/hubery/.pyenv/versions/ldcts/lib/python3.10/site-packages/tortoise/queryset.py", line 1020, in _execute
    raise DoesNotExist("Object does not exist")
tortoise.exceptions.DoesNotExist: Object does not exist

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/hubery/.pyenv/versions/ldcts/lib/python3.10/site-packages/tortoise/backends/mysql/client.py", line 44, in translate_exceptions_
    return await func(self, *args)
  File "/Users/hubery/.pyenv/versions/ldcts/lib/python3.10/site-packages/tortoise/backends/mysql/client.py", line 172, in execute_insert
    await cursor.execute(query, values)
  File "asyncmy/cursors.pyx", line 179, in execute
  File "asyncmy/cursors.pyx", line 364, in _query
  File "asyncmy/connection.pyx", line 450, in query
  File "asyncmy/connection.pyx", line 638, in _read_query_result
  File "asyncmy/connection.pyx", line 1025, in read
  File "asyncmy/connection.pyx", line 600, in read_packet
  File "asyncmy/protocol.pyx", line 190, in asyncmy.protocol.MysqlPacket.raise_for_error
  File "asyncmy/protocol.pyx", line 194, in asyncmy.protocol.MysqlPacket.raise_for_error
  File "asyncmy/errors.pyx", line 128, in asyncmy.errors.raise_mysql_exception
  File "asyncmy/errors.pyx", line 137, in asyncmy.errors.raise_mysql_exception
asyncmy.errors.OperationalError: (1213, 'Deadlock found when trying to get lock; try restarting transaction')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/ddd/rr.py", line 42, in main
    await asyncio.gather(*tasks)
  File "/ddd/rr.py", line 23, in update_or_create_test
    r = await Entry.update_or_create(param=param, defaults={"name": name})
  File "/Users/hubery/.pyenv/versions/ldcts/lib/python3.10/site-packages/tortoise/models.py", line 1110, in update_or_create
    return await cls.get_or_create(defaults, db, **kwargs)
  File "/Users/hubery/.pyenv/versions/ldcts/lib/python3.10/site-packages/tortoise/models.py", line 1065, in get_or_create
    return await cls.create(using_db=connection, **defaults, **kwargs), True
  File "/Users/hubery/.pyenv/versions/ldcts/lib/python3.10/site-packages/tortoise/models.py", line 1136, in create
    await instance.save(using_db=db, force_create=True)
  File "/Users/hubery/.pyenv/versions/ldcts/lib/python3.10/site-packages/tortoise/models.py", line 944, in save
    await executor.execute_insert(self)
  File "/Users/hubery/.pyenv/versions/ldcts/lib/python3.10/site-packages/tortoise/backends/base/executor.py", line 227, in execute_insert
    insert_result = await self.db.execute_insert(self.insert_query, values)
  File "/Users/hubery/.pyenv/versions/ldcts/lib/python3.10/site-packages/tortoise/backends/mysql/client.py", line 52, in translate_exceptions_
    raise OperationalError(exc)
tortoise.exceptions.OperationalError: (1213, 'Deadlock found when trying to get lock; try restarting transaction')

To Reproduce Steps to reproduce the behavior, preferably a small code snippet. To setup a quick mysql server I used docker:

docker run --rm -d --name some-mysql -e MYSQL_ROOT_PASSWORD=docker -e MYSQL_DATABASE=test -p 3307:3306 mysql:8.2.0

Then I installed tortoise-orm this way:

pip install tortoise-orm[asyncmy]==0.20.0

example code:

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    : rr.py
# docker run --rm -d --name some-mysql -e MYSQL_ROOT_PASSWORD=docker -e MYSQL_DATABASE=test -p 3307:3306 mysql:8.2.0

import asyncio
import logging

from tortoise import Tortoise, fields, run_async
from tortoise.models import Model
import traceback

logging.basicConfig(level=logging.DEBUG)

class Entry(Model):
    param = fields.TextField()
    name = fields.TextField()

async def update_or_create_test(param, name):
    r = await Entry.update_or_create(param=param, defaults={"name": name})
    await asyncio.sleep(1)

async def main():
    await Tortoise.init(
        db_url="mysql://root:docker@127.0.0.1:3307/test",
        modules={"models": ["__main__"]},
        # _create_db=True  # Use it only once at first run
    )
    await Tortoise.generate_schemas()
    await Entry.all().delete()
    tasks = []
    for i in range(10):
        param = f"param{i}"
        name = f"mame{i}"
        task = asyncio.ensure_future(update_or_create_test(param, name))
        tasks.append(task)
    try:
        await asyncio.gather(*tasks)
    except Exception:
        print(traceback.format_exc())
    finally:
        await Tortoise.close_connections()

if __name__ == '__main__':

    for i in range(10):
        run_async(main())

Expected behavior A clear and concise description of what you expected to happen. Desirous of circumventing exceptions, or seeking guidance on the proper utilization of the create_or_update function, I humbly request your assistance.

Additional context Add any other context about the problem here. Python 3.10.6 tortoise-orm==0.20.0 asyncmy==0.2.8