This is definitely an issue that can slip under the radar without any explicit warnings. To get an idea of the issue at hand, imagine this scenario.
from django.db import transaction, models, connection
@transaction.commit_on_success
def safe_view(request):
# Object 1
x, is_new = SomeModel.objects.get_or_create(arg1 = '1', arg2 = '2')
x.some_custom_sql()
# Object 2
y = ImageModel(associated_object = x)
y.caption = "This should be created if the whole request is valid"
y.save()
# Abort
raise Exception("STOP! Hammertime.")
class SomeModel(models.Model):
title = models.TextField()
def some_custom_sql(self):
sql = "UPDATE %s SET fti = to_tsvector('%s') WHERE %s = %s" % (self._meta.db_table, self.title, self._meta.pk.column, self.pk)
cursor = connection.cursor()
cursor.execute(sql)
cursor.execute("COMMIT;") Because the view safe_view() is wrapped in a transaction, you would presume that any code in there will be rolled back if an exception were to occur, so an instance of SomeModel and ImageModel would not exist.
However, due to an explicit call to "COMMIT" in some_custom_sql(), the transaction is finalised by the time it returns back to safe_view().
Once the code continues, it will create an instance of ImageModel and save it to the database.
Even though the view is wrapped in commit_on_success() and an exception is raised, the changes to the database are already permenant.
EBy the time you hit the exception, it would have:
- Created an instance of SomeModel
- Updated the row for the FTI entry
- Created an instance of ImageModel
This is not ideal!
To prevent this, refrain from using "COMMIT" in raw SQL unless you know exactly what you're doing!
Instead, replace cursor.execute("COMMIT;") with transaction.commit_unless_managed(). It'll automatically detect if the database is locked in a transaction before committing the transaction.
This problem looks incredibly easy to spot when it's next to each other on the same page, but remember that the "COMMIT" lines could be anywhere in your project. Even in 3rd party modules you've downloaded.
They will silently break your transactions and leave little evidence of permanent database changes until somebody notices!
Be vigilant and you will spot out all the problems in no time!
