Feature request: Possibility to declare transactions as read only

[Andor]

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/ad6d1e15-4ce0-4c03-8a1f-af715c22785ao%40googlegroups.com.

Re: Feature request: Possibility to declare transactions as read only

[Szeles Andor]

Hello Simon and Christian,

good point, making a particular connection read only would be even better
than using a modified atomic.

This way, if I really do need to use a read only transaction, I could just
simply wrap my view inside an atomic decorator like
@transaction.atomic(using='readonly')

On the PostgreSQL side it would simple require adjusting
> get_new_connection[2] to pass the option to psycopg2's set_session[3].
>

Yes, I believe that should work. We could also add the 'deferrable' flag
this way easily.

Many thanks,
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/CANqAxHa810Zo6-0ZocV5Qj7a7242LC3%3DrHncMcUMNhhPqVjh-A%40mail.gmail.com.