On every database query django calls SET SESSION TRANSACTION ISOLATION LEVEL and this slows down the request

Using Django Using the ORM
1 
ImagesToDelete.objects.create(uid=uid, images=json.dumps(images_names))

this is the main query, images_names has not many data

On every query to db django makes a query before the main query
{‘sql’: ‘SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED’, ‘time’: ‘0.000’}
This line is shown when print(connection.queries)

And this slows down the execution of the request, despite the fact that the SQL query itself has time “time”: “0.000”

And also, with absolutely every connection to the database, the {‘sql’: "\n SELECT VERSION(),\n sql_mode,\n default_storage_engine,\n sql_auto_is_null,\n lower_case_table_names,\n CONVERT_TZ(‘2001-01-01 01:00:00’, ‘UTC’, ‘UTC’) IS NOT NULL\n ", ‘time’: ‘0.000’} query is called

MySQL is used

Is it possible to cancel these first 2 queries when connecting to the database?

2

It would be helpful to post your code and all your logs/print statements that you get in order to better undestand the problem and what is your goal.

Until then you can test with CONN_MAX_AGE

3

I believe that this is related to parameters that are required for Django to work properly, I believe that if you set then to the expected values django won’t be needing to do so on every connection.
You can read more about this topic on the databases documentation, and on your case, specifically for MySQL

4

Thanks for your answer

But CONN_MAX_AGE doesnt help

There is also a point that if we call a query often, for example with an interval of at least a second, then only one required SQL query is called, but if we wait 2 seconds and call it again, then again 3 SQL queries

This is code. Don’t pay attention to “name1” and “name2”, they don’t matter

class GetImagesS3Urls(APIView):
    def get(self, request, uid):
        images_names = ["name1", "name2"]
        ImagesToDelete.objects.create(uid=uid, images=json.dumps(images_names))
        
        for i in connection.queries:
            print(i)
            print("\n")

        return Response({"images_names": images_names})

And it prints 3 queries

{'sql': "\n                SELECT VERSION(),\n                       @@sql_mode,\n                       @@default_storage_engine,\n                       @@sql_auto_is_null,\n                       @@lower_case_table_names,\n                       CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL\n            ", 'time': '0.000'}


{'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'}


{'sql': 'INSERT INTO `idea_imagestodelete` (`uid`, `images`, `date_time`) VALUES (\'95b682ae-12c8-4f3d-93eb-3e1a49b150ff\', \'[\\"/2025-01/2025-01-20/1e00c5875c0e4a21b9da0a209d9fb6941737374365.1903498.jpeg\\"]\', \'2025-01-20 11:59:25.997528\')', 'time': '0.047'}

MySQL config

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'idea',
        'USER': 'user',
        'PASSWORD': 'password',
        'HOST': '127.0.0.1',
        'PORT': '3306',
        'OPTIONS': {
            'charset': 'utf8mb4',
        }
    }
}
5

Side note: If you’re testing this using runserver with DEBUG=True, then be aware that your operational characteristics will be different when this is running in a true production-quality deployment environment. Relying upon what you’re seeing here in development can (and usually will) be misleading.

6

I read the documentation, but it doesn’t say anything about it. Do you mean that each of the VERSION, ​​sql_mode, default_storage_engine, sql_auto_is_null, lower_case_table_names, CONVERT_TZ needs to be set in the settings.py config?

7

Oh, okay, I’ll try to test this in production and see the result.

8

Checked on a copy of the production server, running on apache2, DEBUG=False

Just set connection.force_debug_cursor = True on begging of request to print connection queries

The first query (select VERSION(), sql_mode … etc) is indeed not called

But the second one still continues to be called with each query

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

Even if there is no writing to the database, but only reading

Why is this query called every time?

9

Is it being called once for each query, or is it being called once for the view?

(In other words, if your view is executing three queries, is this being called one time or 3?)

10

Sorry, I didn’t see your reply, I didn’t receive an email notification

This is called one time at view. If there are three queries executing in view, it is called one time, not three time

11

See the docs for Isolation level in the MySQL section of the database docs. The default for MySQL is considered “unsafe” for Django.

Note, what you’re doing in this view doesn’t matter. These isolation levels affect how your view works in combination with other views currently being executed - some of which may be writing. This view has no way to know what other transactions may be in progress when it’s running.

12

Okay, now I want to ask you as someone who has experience

Is it possible to just set the default isolation level for mysql to read committed (in my.cnf), and not force django to do it with new view request every time?

Is this a bad practice?

13

That’s something you should be able to find in the MySQL docs.

However, that’s only part of the issue. Having MySQL configured to use READ-COMMITTED by default doesn’t necessarily mean that the database engine knows that this has been done, and so may continue to do so to be safe. You’ll have to dig into the engine to see whether it detects the current setting, or if there is a way that you can tell it that this is the case.

<opinion>
I think you’re wasting a lot of time & effort worrying about nothing. The amount of time spent issuing this per-connection query is a negligible amount of time compared to the time required by the rest of the view being executed.
</opinion>

14

Okay, thanks for the explanation