Not nesting version of @atomic() in Django?
<p>From the <a href="https://docs.djangoproject.com/en/dev/topics/db/transactions/#django.db.transaction.atomic">docs of atomic()</a></p>

<blockquote>
  <p>atomic blocks can be nested</p>
</blockquote>

<p>This sound like a great feature, but in my use case I want the opposite: I want the transaction to be durable as soon as the block decorated with <code>@atomic()</code> gets left successfully.</p>

<p>Is there a way to ensure durability in django's transaction handling?</p>

<h1>Background</h1>

<p>Transaction are ACID. The "D" stands for durability. That's why I think transactions can't be nested without loosing feature "D".</p>

<p>Example: If the inner transaction is successful, but the outer transaction is not, then the outer and the inner transaction get rolled back. The result: The inner transaction was not durable.</p>

<p>I use PostgreSQL, but AFAIK this should not matter much.</p>

<p>This type of <em>durability</em> is <strong>impossible due to ACID</strong>, with one connection. (i.e. that a nested block stays committed while the outer block get rolled back) It is a consequence of ACID, not a problem of Django. Imagine a super database and the case that table <code>B</code> has a foreign key to table <code>A</code>.</p>

<pre><code>CREATE TABLE A (id serial primary key);
CREATE TABLE B (id serial primary key, b_id integer references A (id));
-- transaction
   INSERT INTO A DEFAULT VALUES RETURNING id AS new_a_id
   -- like it would be possible to create an inner transaction
      INSERT INTO B (a_id) VALUES (new_a_id)
   -- commit
-- rollback  (= integrity problem)
</code></pre>

<p>If the inner "transaction" should be durable while the (outer) transaction get rolled back then the integrity would be broken. The rollback operation must be always implemented so that it can never fail, therefore no database would implement a nested independent transaction. It would be against the principle of causality and the integrity can not be guarantied after such selective rollback. It is also against atomicity. </p>

<p>The transaction is related to a database connection. If you create <strong>two connections</strong> then two independent transactions are created. One connection doesn't see uncommitted rows of other transactions (it is possible to set this <em>isolation level</em>, but it depends on the database backend) and no foreign keys to them can be created and the integrity is preserved after rollback by the database backend design.</p>

<p>Django supports multiple databases, therefore multiple connections.</p>

<pre><code># no ATOMIC_REQUESTS should be set for "other_db" in DATABASES

@transaction.atomic  # atomic for the database "default"
def my_view():
    with atomic():   # or set atomic() here, for the database "default"
        some_code()
        with atomic("other_db"):
            row = OtherModel.objects.using("other_db").create(**kwargs)
        raise DatabaseError
</code></pre>

<p>The data in "other_db" stays committed.</p>

<p>It is probably possible in Django to create a trick with two connections to the same database like it would be two databases, with some database backends, but I'm sure that it is untested, it would be prone to mistakes, with problems with migrations, bigger load by the database backend that must create real parallel transactions at every request and it can not be optimized. It is better to use two real databases or to reorganize the code.</p>

<p>The setting DATABASE_ROUTERS is very useful, but I'm not sure yet if you are interested in multiple connections.</p>

python django postgresql transactions acid