TransactionManagementError is raised when autocommit …

[Tore Lundqvist]

Reply to comments in ticket: https://code.djangoproject.com/ticket/26323#comment:10

Hi, 

@Aagustin: I get your point but in the code I'm working on there is a lot of transaction.commit(), not to handle transactions but to manage when data is written to disk and to avoid deadlocks. Running in autocommit mode does not work, its slow and sometimes the commits is used to save in a known good state. So I disable autocommit with transaction.set_autocommit(False) and run the code with explicit commits in it and than turn autocommit on again. 

The documentation for set_autocommit says "Once you turn autocommit off, you get the default behavior of your database adapter, and Django won’t help you." That is what I want and thats way I think that the TransactionManagementError should not de raise if your not using atomic blocks. 

[charettes]

Hi Tore,

Is there a reason you can't simply wrap your updates in a `transaction.atomic()` blocks?

You should be able to avoid deadlocks and control exactly when data is written to disk
with this construct.

Simon

[Tore Lundqvist]

Hi, Simon

No, I would need to wrap everything i a atomic block to start the transaction and it's only when that outermost atomic block exits that I actually get a commit the nested ones just makes save point.

/Tore 

[Aymeric Augustin]

If you do what Simon and I suggest, you should get the result you just described. If you don’t, please explain what happens.

Within a transaction — and disabling autocommit means you’re within a transaction — transaction.atomic() uses savepoints.

Note that in Django 1.6, after set_autocommit(False), you couldn’t use transaction.atomic(). That was fixed in 1.8 (I think).

-- 

Aymeric.

-- 
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 post to this group, send email to django-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/a6077f33-1113-4767-828c-8b2c0c77bd78%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Tore Lundqvist]

I don't what all updates to be in one commit each so I can't wrap just the update with a atomic block I would need to have it over a bigger chuck of code. That chunk might call a subrutin that also needs a commit and if I wrap that update in a atomic block that atomic block is nested and results in a save point which is useless.

Den fredag 4 mars 2016 kl. 22:46:30 UTC+1 skrev Aymeric Augustin:

If you do what Simon and I suggest, you should get the result you just described. If you don’t, please explain what happens.

Within a transaction — and disabling autocommit means you’re within a transaction — transaction.atomic() uses savepoints.

Note that in Django 1.6, after set_autocommit(False), you couldn’t use transaction.atomic(). That was fixed in 1.8 (I think).

-- 

Aymeric.

On 04 Mar 2016, at 21:21, Tore Lundqvist <t...@mima.x.se> wrote:

Hi, Simon

No, I would need to wrap everything i a atomic block to start the transaction and it's only when that outermost atomic block exits that I actually get a commit the nested ones just makes save point.

/Tore 

Den fredag 4 mars 2016 kl. 21:09:17 UTC+1 skrev charettes:

Hi Tore,

Is there a reason you can't simply wrap your updates in a `transaction.atomic()` blocks?

You should be able to avoid deadlocks and control exactly when data is written to disk
with this construct.

Simon

Le vendredi 4 mars 2016 15:02:45 UTC-5, Tore Lundqvist a écrit :

Reply to comments in ticket: https://code.djangoproject.com/ticket/26323#comment:10

Hi, 

@Aagustin: I get your point but in the code I'm working on there is a lot of transaction.commit(), not to handle transactions but to manage when data is written to disk and to avoid deadlocks. Running in autocommit mode does not work, its slow and sometimes the commits is used to save in a known good state. So I disable autocommit with transaction.set_autocommit(False) and run the code with explicit commits in it and than turn autocommit on again. 

The documentation for set_autocommit says "Once you turn autocommit off, you get the default behavior of your database adapter, and Django won’t help you." That is what I want and thats way I think that the TransactionManagementError should not de raise if your not using atomic blocks. 

-- 
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+unsub...@googlegroups.com.

[Jeremy Dunck]

I've had this scenario before - you have two interleaving units of work (through composition of code from different sources or concerns).  You want progress recorded for one unit of work, but perhaps not the other.  Without django, you'd have two open connections.  In my experience the simplest way to accommodate this is to have two DB aliases pointed at the same DB.  Set one to be a test mirror of the other.  Use different aliased connections for the two concerns.  Then you can use atomic (as suggested and typical).

-- 

Aymeric.

To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.

To post to this group, send email to django-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/a6077f33-1113-4767-828c-8b2c0c77bd78%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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 post to this group, send email to django-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/79611d30-21e0-45bc-8ab7-8754a39db4fc%40googlegroups.com.

[Tore Lundqvist]

Thanks for the suggestion! 

I have been thinking of workarounds with multiple db aliases and it would solve the problem I described but in reality the code I'm working with is much more complex and has around 400 explicit commits. It all worked great in Django 1.4 but since we upgraded to 1.8 (and got the new transaction management) I have been forced to make ugly workarounds and patch Django this time. 

-- 

Aymeric.

To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsub...@googlegroups.com.

To post to this group, send email to django-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/a6077f33-1113-4767-828c-8b2c0c77bd78%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Tore Lundqvist]

Regardless of the particular problem I have got shouldn't it be possible to disable Djangos transactional management if you want to?

Is not transaction.set_autocommit(False) doing that?

Is it not surprising to get a TransactionManagementError when you have turned of the transaction management?

There is also another broken thing with set_autocommit(False) if the database connection has timed out and is reconnected by Django, autocommit is set to True again, that was kind of surprising and ugly to workaround.

[Aymeric Augustin]

Hi Tore,

> On 05 Mar 2016, at 20:41, Tore Lundqvist <t...@mima.x.se> wrote:
>
> Regardless of the particular problem I have got shouldn't it be possible to disable Djangos transactional management if you want to?

It's possible. It’s documented.

https://docs.djangoproject.com/en/1.9/ref/settings/#autocommit
https://docs.djangoproject.com/en/1.9/topics/db/transactions/#deactivate-transaction-management

> Is not transaction.set_autocommit(False) doing that?

It has the same effect as the AUTOCOMMIT option but you have to run it on every new connection.

> Is it not surprising to get a TransactionManagementError when you have turned of the transaction management?

As documented, “turning off Django's transaction management” means “you have to understand exactly what happens and, most often, implement yourself a significant part of Django’s transaction management tools”.

The ORM still needs to be aware of the current transaction state e.g. for "create or update” operations, for multi-table inserts when inheritance is used, etc. There’s no way to make the ORM not care about transactional integrity at all.

> There is also another broken thing with set_autocommit(False) if the database connection has timed out and is reconnected by Django, autocommit is set to True again, that was kind of surprising and ugly to workaround.

It must be quite frustrating for you to keep ignoring the documentation and the advice given by people who wrote the code, to make up arbitrary theories on Django’s behavior, to realize that this isn’t the actual behavior, and to fantasize that it’s a bug. I’m not sure how we can discuss on such terms.

For this discussion to move forwards, you'll have to read and understand the documentation. If you need to go further and get a deeper understanding of how Django ended up there, look at this mailing list’s archives. I explained my reasoning when I implemented Django’s current transaction management in Django 1.6. I explained some choices again in several threads since then. You may also be interested in my DjangoCon talks — the videos are online.

I can’t say that the current system is perfect. I know its flaws better than anyone else. However I will say that, until now, you haven't demonstrated a sufficient level of understanding of its design or its implementation to challenge them.

I’m sorry for the lack of a good upgrade path. If you read the history, you’ll see why it was impossible to implement adequate transactional integrity guarantees with the old APIs. Perhaps getting this perspective will give you ideas to solves your upgrade problem? (Also, to be honest, given the bugs in the old APIs, I’m skeptical that a system with 400 commit statements in arbitrary locations actually provided the guarantees you hoped for.)

In any case, that should give you a better idea of how to use Django’s transaction management APIs and, if they're really unsuitable for your needs, how to do things differently. For what it’s worth, every constructive discussion I’ve had on this topic has resulted in the removal of shaky transaction management code and the implementation of a much more straightforward system based on the new APIs.

It’s very hard to help you further without seeing some code. I have very little idea of what problems you’re hitting. I could infer one of your issues when we were discussing on Trac because it’s a common one and, admittedly, a weakness of the current APIs. Then you confirmed it by posting a test case. Unfortunately my crystal ball doesn’t go any further :-(

--
Aymeric.

[Shai Berger]

Hi Tore,

You should be able to get what you want be replacing your commits by

connection.cursor.execute("commit"); transaction.rollback()

or equivalents (caveat: untested).

This looks like a very hackish thing to do, code I wouldn't put in production.
And it should.

Shai.

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

> >> To post to this group, send email to django-d...@googlegroups.com

> >> <javascript:>.

> >> Visit this group at https://groups.google.com/group/django-developers.
> >> To view this discussion on the web visit

> >> https://groups.google.com/d/msgid/django-developers/79611d30-21e0-45bc-8
> >> ab7-8754a39db4fc%40googlegroups.com
> >> <https://groups.google.com/d/msgid/django-developers/79611d30-21e0-45bc
> >> -8ab7-8754a39db4fc%40googlegroups.com?utm_medium=email&utm_source=footer
> >> > .

[Tore Lundqvist]

Hi, Aymeric

Thanks for your extensive reply.

I'm sorry that I just throw in that comment about timed out connections without enough information for you to actually respond to it.

an example of when it happens is when:

Starting with auto commit on.

transaction.set_autocommit(False)

... a lot of time passes without the connection being used so it times out....

close_old_connections()

Now autocommit is on again. I'm not saying that it's a bug, but it's inconvenient.

I have already seen your talk at DjangoCon and it's very good and I have recommend it to others too.

I think the new transaction management API makes a lot more sense than the old one. The problem is that the legacy code uses COMMIT side effects to manage disk writes and avoid deadlocks, securing an adequate transactional integrity is often not an issue. With explicit commit statements it's easy to control when commit is preformed but with atomic blocks you get the commit implicitly when (if they are nested) the outermost block ends, that makes it hard to grantee a commit from deep down a call stack. So I'm trying to disable or at least use as little as possible of Djangos transaction management for this code. Other parts of the system can use the transaction management as intended.

Cheers

Tore

[Tore Lundqvist]

Hi, Shai

As far as I know your not allowed to do a transaction.rollback() in an atomic block so I don't seen how this is different from doing a transaction.commit()

[Aymeric Augustin]

Hi Tore,

I’ve been meaning to check whether your use case was supported, because it should be. I’m sorry, it took me a few days to find the time to investigate.

While I was testing, I produced an exception which looks similar to the problem you originally reported: https://code.djangoproject.com/ticket/26340.

Specifically, could you try adding `self.needs_rollback = False` at the bottom of the `BaseDatabaseWrapper.rollback()` and tell me if that helps?

(Since I don’t remember hearing you mention savepoints, you can probably ignore everything I say about savepoints.)

On 07 Mar 2016, at 12:55, Tore Lundqvist <t...@mima.x.se> wrote:

an example of when it happens is when:

Starting with auto commit on.

transaction.set_autocommit(False)

... a lot of time passes without the connection being used so it times out....

close_old_connections()

There’s a long discussion of this use case here: https://code.djangoproject.com/ticket/21597.

Now autocommit is on again. I'm not saying that it's a bug, but it's inconvenient.

This surprises me. The connection shouldn’t reopen at all. It should become unusable, for the reasons explained in that issue (ticket 21597).

Regardless, at any point in the code where you expect not to use the database connection for some time, you should close it explicitly with connection.close(). Then you can call set_autocommit(False) when you’re about to start using the database connection again.

Since I don’t expect the database timeout to be reached while handling a HTTP request, I assume this happens in a long-running management command. You could have another settings module just for the purpose of running that command. It would be the same as your regular settings module, except it would set the AUTOCOMMIT option to False in the database configuration. In that case, you wouldn’t need to call set_autocommit(False).

The problem is that the legacy code uses COMMIT side effects to manage disk writes and avoid deadlocks, securing an adequate transactional integrity is often not an issue.

That’s an interesting way to use transactions, and to be honest, one I never imagined ;-)

While I wouldn’t recommend that design for new code, Django shouldn’t prevent you from continuing to use it.

Obviously, some other users of Django need transactional integrity. We can’t relax that constraint when discussing changes to Django.

So I'm trying to disable or at least use as little as possible of Djangos transaction management for this code.

After transaction.set_autocommit(False), transaction.commit() and transaction.rollback() seem to work fine.

As I pointed out in my previous messages, you have to rollback with transaction.atomic() when you’re catching IntegrityError.

I thought you could rollback with transaction.rollback() or transaction.savepoint_rollback(). It turns out you also need to call transaction.set_rollback(False). Essentially this tells Django “hey, I’m happy to continue from this point, just go ahead”.

I think Django could do better. That’s why I filed the issue I mentioned at the beginning of this email (ticket 26340).

Given MySQL’s interesting approach to transactional integrity, you can call transaction.set_rollback(False) after a query that failed with an IntegrityError and keep going.

In case that’s useful, here are some patterns that work currently. A and B are two distinct sessions. I’m using session B to check whether the changes from session A have been committed.

I have a user model with a unique email field. I used PostgreSQL because I don’t have a MySQL instance around. If you’re getting different results on MySQL, it’s a bug.

A >>> from django.db import IntegrityError, transaction

A >>> transaction.set_autocommit(False)

A >>> User.objects.create(email='us...@example.com')

A <User: us...@example.com>

A >>> User.objects.create(email='us...@example.com')

A <User: us...@example.com>

B                 >>> User.objects.filter(email__startswith='user')

B                 []

A >>> transaction.commit()

B                 >>> User.objects.filter(email__startswith='user')

B                 [<User: us...@example.com>, <User: us...@example.com>]

A >>> try:

A ...     with transaction.atomic():

A ...         User.objects.create(email='us...@example.com')

A ... except IntegrityError:

A ...     print("duplicate")

A ... 

A <User: us...@example.com>

A >>> try:

A ...     with transaction.atomic():

A ...         User.objects.create(email='us...@example.com')

A ... except IntegrityError:

A ...     print("duplicate")

A ... 

A duplicate

A >>> try:

A ...     with transaction.atomic():

A ...         User.objects.create(email='us...@example.com')

A ... except IntegrityError:

A ...     print("duplicate")

A ... 

A <User: us...@example.com>

B                 >>> User.objects.filter(email__startswith='user')

B                 [<User: us...@example.com>, <User: us...@example.com>]

A >>> transaction.commit()

B                 >>> User.objects.filter(email__startswith='user')

B                 [<User: us...@example.com>, <User: us...@example.com>, <User: us...@example.com>, <User: us...@example.com>]

A >>> User.objects.create(email='us...@example.com')

A <User: us...@example.com>

A >>> User.objects.create(email='us...@example.com')

A <User: us...@example.com>

B                 >>> User.objects.filter(email__startswith='user')

B                 [<User: us...@example.com>, <User: us...@example.com>, <User: us...@example.com>, <User: us...@example.com>]

A >>> transaction.rollback()

B                 >>> User.objects.filter(email__startswith='user')

B                 [<User: us...@example.com>, <User: us...@example.com>, <User: us...@example.com>, <User: us...@example.com>]

Best regards, 

-- 

Aymeric.

[Tore Lundqvist]

Specifically, could you try adding `self.needs_rollback = False` at the bottom of the `BaseDatabaseWrapper.rollback()` and tell me if that helps?

Yes, this helps! With this change I can make it work without bypassing the public API.

On 07 Mar 2016, at 12:55, Tore Lundqvist <t...@mima.x.se> wrote:

an example of when it happens is when:

Starting with auto commit on.

transaction.set_autocommit(False)

... a lot of time passes without the connection being used so it times out....

close_old_connections()

There’s a long discussion of this use case here: https://code.djangoproject.com/ticket/21597.

Now autocommit is on again. I'm not saying that it's a bug, but it's inconvenient.

This surprises me. The connection shouldn’t reopen at all. It should become unusable, for the reasons explained in that issue (ticket 21597).

Ticket 21597 seems to be the same problem I have. I Think the timeout exception does not aper until you try to use the timed out connection and close_old_connections() closes it and solves problem. I guess its a similar solution to closing the connection before the time it's not used. You can set autocommit to false right after closing the connection, you just have to know that closing the connection also resets the autocommit setting. 

Given MySQL’s interesting approach to transactional integrity, you can call transaction.set_rollback(False) after a query that failed with an IntegrityError and keep going.

 You can't use set_rollback() outside an atomic block so that not an option for me.

Thanks

Tore 

[Aymeric Augustin]

On 11 Mar 2016, at 10:28, Tore Lundqvist <t...@mima.x.se> wrote:

Given MySQL’s interesting approach to transactional integrity, you can call transaction.set_rollback(False) after a query that failed with an IntegrityError and keep going.

 You can't use set_rollback() outside an atomic block so that not an option for me.

Indeed. (I realized that after sending my email and forgot to send a correction.)

-- 

Aymeric.

[Tore Lundqvist]

I recently upgrade Django 1.7->1.8 and ended up needing to patch set_rollback()  and comment out the "exception if not in an atomic block" part. The problem was that sometimes database errors like IntegrityError occurred because multiple workers was trying to create the same data but that's ok, the code captures the error and does a get instead of a create and moves on. A rollback was not an option here as that wound roll back a lot of other things that was ok. Way do you need to be in a atomic block to use this function? Is the filosofi of Djangos transaction handling that an exception from the database should always be handled with a rollback no matter what?

Cheers

Tore

[Jeremy Dunck]

You can use atomic just over the section that causes the error.  The issue is that different db engines have different semantics under error during transaction. Rolling back to the last savepoint (as atomic does when nested) recovers the ability to complete the remainder of the transaction. With a savepoint to roll back to, in some DBs, the full transaction is lost.

--

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 post to this group, send email to django-d...@googlegroups.com.

Visit this group at https://groups.google.com/group/django-developers.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/a8daaa98-f66a-40e5-bb4d-c0d4ad647f70%40googlegroups.com.

[Tore Lundqvist]

I can't use atomic block for reasons I tried to explain earlier in the thread. set_rollback is an advanced feature, should you not be able to use it in a db engine specific way/for mysql?

Thanks for the feedback!

[Tore Lundqvist]

Correction:

I can't use atomic blocks 

This is not true.

"You can use atomic just over the section that causes the error", this has been suggested to me multiple times and I thought I had no use for it but it was actually very useful in many cases. 

With that technic and with the Django patch Aymeric suggested I managed to solve my problem without patching set_rollback().

Thanks everyone for helping me with this!

Tore