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

[charettes]

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

[Christian González]

Am 01.07.20 um 20:28 schrieb charettes:

>
> 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.
>

Hi,

may I blend in here, I'd support the "per database connection" way too.
I've got a use case where I tap a Database I have access to with a user
that CAN write, but I want to make ABSOLUTELY sure that Django doesn't
change that database.

All ORM classes that are writeable are in another connection, but, you
know, there could be bugs. And If I mess up that "3rd party" DB, it's
NotGood(TM).

Oh yes, and I can't create a r/o user for that database.

So It would be really cool if I could  tell Django in a DATABASES option
that this connection should never be written to.

Christian

[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

[Adam Johnson]

Christian, FYI you can do this already for all new connections with something like this in an apps.py:

from django.db.backends.signals import connection_created

@connection_created.connect
def transactions_readonly(*, connection, **kwargs):
    with connection.cursor() as cursor:
        cursor.execute("SET SESSION TRANSACTION READ ONLY")

--
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-develop...@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.

--

Adam