Why doesn't "select_for_update()" work in Django with MySQL?

Question

I have a table like below:

SELECT id, name FROM node;
+----+------+
| id | name |
+----+------+
|  5 | na   |
+----+------+

Then define below function:

>>> def foo_with_sfu(seconds):
...     with transaction.atomic():
...         node = Node.objects.select_for_update().filter(pk=5)
...         time.sleep(seconds)
...         node = Node.objects.get(pk=5)
...         print(node.name)
... 

I was hoping the select_for_update will lock row pk=5 so if I open another console to change the node.name during time.sleep, the change operation will be blocked.

But actually when I run the function, and run update sql in another console during the time.sleep, the update were not blocked.

It seems the select for update not locking up the row. Why?

Answer 1

Your select_for_update() query is never evaluated, so the lock is never executed on the database. See the documentation for when and how querysets get evaluated.

If you simply wrap the call in a queryset-evaluating function your test should work. For example:

with transaction.atomic():
    node = list(Node.objects.select_for_update().filter(pk=5))

Answer 2

To run SELECT FOR UPDATE, you need to put print(node) after select_for_update() as shown below. *You can also put bool(node), len(node) or list(node) instead of print(node):

node = Node.objects.select_for_update().filter(pk=5)
print(node) # bool(node), len(node) or list(node) is also fine

You can see my question and answer explaining more about select_for_update() in Django: