A Few Django ORM Mistakes
See if you can figure out what's wrong with the code snippets below! Ask yourself what the problem is, what effect will it have, and how can you fix it?
These examples are for Django, but probably apply to many other ORMs.
save method saves all attributes.
The problem with this code is that two Python instances of the same database row exist. Here's the annotated source:
The result is a single
Thing with a
1 and a
2. A write has been
Here's one possible fix:
thing_set_bar can happen simultaneously.
It's possible for a thread to read from the database just before a write happens in another thread, resulting in the following situation:
Here's one possible solution:
This is very much like bug 2, but the twist is that the
function can conflict with itself. If called in two different threads,
increment is called twice the total may still only be 1.
One way to fix this is to make the increment operation atomic.
The way to do this in the Django ORM is to use
READ COMMITTED Isolation Level
This is the default for PostgreSQL. Transactions can read updates from other transactions after they have been committed.
REPEATABLE READ Isolation Level
This is the default for MySQL. A snapshot is established on the first read in the transaction, and all subsequent reads are from the snapshot.
Going forward, assume we are using MySQL in its default configuration.
It is possible for
do_state_transition to be executed twice if
the state transition is executed concurrently. This could be a problem if your
state transition includes side effects!
One simple solution to this problem is to lock the object:
But, generally, you should try to avoid doing side effects in transactions!
Another way of handling this is to use optimistic locking. Instead of
naively saving every value, optimistic locking tries to update with
WHERE state ≠ DONE clause. This will first lock the
database row and read in the latest state (updates do not use the snapshot)
before updating. If no rows are successfully updated (i.e. the row has already
transitioned) then the transaction is rolled back.
There are two caveats to optimistic locking. The first is that if your transition has side effects it won't help you, and the second is that if you take other locks in the transaction you must avoid deadlocks.
This is the approach taken by the django-fsm library.
If executed concurrently, one transaction will not see the newly created
in the other transaction. The last write will win and the total will be inconsistent.
In addition to that, in MySQL this can potentially deadlock causing
your transaction to roll back entirely! Creating the
causes a lock that blocks the aggregation read.
Both issues can be fixed by locking the model being updated (not the payment!) at the start of the transaction:
Or, alternatively, making the update atomic:
Note that this cannot be in a transaction, or the deadlock issues will remain!
In my opinion, the safest way to do this is by using a SQL view instead of storing the total. Views can be awkward to use with Django unfortunately.
This is a bug in MySQL, but not PostgreSQL.
This bug is a result of the REPEATABLE READ isolation level. The read after
the transaction starts establishes a snapshot, so
refresh_from_db is performed after waiting for a lock, the
snapshot is read, not the most recent value.
This means when the
foo.bar check is performed, we are checking
potentially stale data. This can cause multiple
Bar to be
created, but only one of them linked to the correct
with lock() with
select_for_update() will work for MySQL, because MySQL has a
weird quirk where locked reads do not read from the snapshot. When
using REPEATABLE READ with PostgreSQL, this will throw an error instead.
The preferred way is to either move the lock to the top, outside of the
transaction, or using
select_for_update() as follows:
How Common Are These Bugs?
These bugs are simplified versions of bugs found in production code, and written by experienced Django developers. Remember, these bugs won't be as easy to spot as they are here. They can be buried deep in between hundreds of lines of code.
After spending a very short time looking at a few open source Django projects, I can tell that these bugs are common. In fact, at the time of writing the Django example app contains two of them! There are probably a few reasons for this:
- Thinking about concurrency is just hard.
- Developers are not taking enough time to understand the underlying database technology.
- In most cases the lost writes will happen very rarely.
- When they do happen they are often relatively harmless.
- It's a silent failure. How would you tell if your app was losing an occasional write?
So it's your call if you want to ignore these problems. But if you decide you don't care, please don't write financial or medical software. ;)
- Remember the ORM is an in-memory cache.
- ORMs can obscure bugs. Look at the SQL!
- Avoid read-modify-write
- If you don't you'll probably need a lock.
- If it's not locked, it's not up to date.
- Lock before reads to avoid weird MySQL behaviour.
- Locking reads don't use the snapshot in MySQL.
- Prefer immutable database design if practical.
- See: Immutable Data.
- Consider using a serializable isolation level.
- You don't have to worry about locking if you use serializable transactions.
- Has other drawbacks.
- PostgreSQL implementation is nicer than the MySQL one IMO.