Why could select_for_update simply be ignored with databases that don't support it?

Question

The Django docs for select_for_update say

Using select_for_update() on backends which do not support SELECT ... FOR UPDATE (such as SQLite) will have no effect. SELECT ... FOR UPDATE will not be added to the query, and an error isn’t raised if select_for_update() is used in autocommit mode.

This strikes me as an odd and potentially dangerous decision, especially since select_for_update is used to lock rows. If I write code that uses select_for_update, I would rely on it actually being honored! If the DB backend doesn't support it, I would expect Django to either fall back to a safe-but-less-efficient alternative or, if one doesn't exist, to throw an exception of some kind.

In this case it seems like Django could suddenly and silently reintroduce race conditions by just ignoring select_for_update on DBs where it's not supported (such as SQLite). My intuition says Django wouldn't do that and there must be some reason why it's not needed if not supported (perhaps engines where it's not supported use complete database locking?) but I can't seem to find anything concrete in the docs to back up that theory. It doesn't seem like this issue is necessarily specific to Django, either.

This is making me very leery of using select_for_update even though it would solve some current problems nicely.

Answer 1

With database engines that allow to reduce transaction isolation in order to improve the speed of concurrent accesses (e.g., PostgreSQL, Oracle, and MySQL), SELECT FOR UPDATE is used to tell the database that rows that are read now will likely be written to later. This can avoid inconsistent data showing up in concurrent transactions, or even prevent deadlocks in some situations.

In SQLite, all transactions are serializable, i.e., it behaves as if the entire database is locked around each transaction. (And in autocommit mode, each statement is wrapped in an implicit transaction.)

So SELECT FOR UPDATE, even if it were implemented, would not actually add any more locking than is already there. Ignoring it is the right thing to do for SQLite.