Hello Andor, I think it would make sense to add this feature to Django but I'm not convinced per-atomic block granularity is the best API to expose this rarely used pattern. For example, how should this option behave when nesting atomic block and mixing readonly values or disabling usage of savepoints?
Do you think that allowing a readonly boolean flag[0] to be specified in OPTIONS per connection like we do for isolation level[1] could work for your use case? From my experience this particular problem is usually dealt with by using database routers to direct read queries to a connection pointing at read-only replica so it wouldn't be much different from the usual setup there. On the PostgreSQL side it would simple require adjusting get_new_connection[2] to pass the option to psycopg2's set_session[3]. Cheers, Simon [0] https://docs.djangoproject.com/en/3.0/ref/settings/#std:setting-OPTIONS [1] https://docs.djangoproject.com/en/3.0/ref/databases/#isolation-level [2] https://github.com/django/django/blob/6789ded0a6ab797f0dcdfa6ad5d1cfa46e23abcd/django/db/backends/postgresql/base.py#L186-L204 [3] https://www.psycopg.org/docs/connection.html#connection.set_session Le mercredi 1 juillet 2020 à 08:56:43 UTC-4, Andor a écrit : > Greetings Django developers, > > I'd like to explicitly declare some of my database transactions as read > only to enable certain optimizations, but after inspecting the > documentation, forums and source code, it seems this feature is missing. > > As far as I understand, > https://docs.djangoproject.com/en/3.0/topics/db/transactions/#controlling-transactions-explicitly > > describes the only way to control database transactions in a > database-agnostic way: > Use atomic(using=None, savepoint=True) with the appropriate parameters, > either as a decorator or a context manager. > > It would be nice to have an additional parameter: > atomic(using=None, savepoint=True, readonly=False) > > I understand that Django defaults to the READ COMMITTED isolation level - > just like most databases it supports - and at that level, there isn't much > to gain with read only transactions. But if one chooses REPEATABLE READ or > SERIALIZABLE, certain transaction aborts could be avoided and transactional > throughput could be increased by declaring transactions not modifying > shared data explicitly as read only. > > This is also a very common feature, so most users could make use of it > regardless of the database they use: > https://www.postgresql.org/docs/current/sql-set-transaction.html > https://mariadb.com/kb/en/set-transaction/ > https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html > > https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SET-TRANSACTION.html > > Note: Using PostgreSQL, one can add an additional keyword to read only: > deferrable. > It makes sure that the read only transaction will never be aborted, even > under serializable isolation lever, in exchange of some possible delay of > the start of its execution. > This could also be added to atomic with a default value of False, and be > ignored on other databases which don't support it. > > Please consider implementing this feature, giving us a standard way to > declare our transactions as read only when needed! > > Best regards, > Andor > -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/31e8c522-ce78-4f8a-a043-ac5a1edefd63n%40googlegroups.com.
