#33516: QuerySet update() under postgres with
select_for_update(skip_locked=True)
results in neither FOR UPDATE nor SKIP LOCKED
-------------------------------------+-------------------------------------
Reporter: Grant Gainey | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Grant Gainey):
Ah-ha! Perfect, that explains everything!
Two comments/suggestions:
* Could it be possible for select_for_update() to throw an exception when
it's not going to be emitted? Would have helped me target the problem way
sooner if the method yelled at me for using it incorrectly
* Similarly - when called 'correctly', select_for_update() throws an
exception if you're not in a transaction. When called incorrectly, it
doesn't. Would be great if the behavior was consistent.
For posterity's sake, below is my test-script using the suggested changes,
that works on a 20K table with no deadlocks. Thanks for the pointer!
{{{
import statistics
import time
from threading import Thread
from pulpcore.app.models import Content
from django.db import transaction
from django.utils.timezone import now
durations = []
def update_timestamp(index):
print(">>>in update_timedstamp index {}".format(index))
start = time.time()
with transaction.atomic():
content_q = (
Content.objects.filter()
.order_by("pk")
.select_for_update(skip_locked=True)
.values_list("pk", flat=True)
)
Content.objects.filter(pk__in=content_q).update(timestamp_of_interest=now())
end = time.time()
durations.append(end-start)
print(">>>done")
for r in range(10):
threads = []
for i in range(10):
threads.append(Thread(target=update_timestamp, args=(i,)))
for t in threads:
t.start()
for t in threads:
t.join()
print("Avg time : {}".format(sum(durations) / len(durations)))
print("Median time : {}".format(statistics.median(durations)))
print("StdDev : {}".format(statistics.stdev(durations)))
}}}
And the emitted SQL:
{{{
2022-02-16 13:46:02.517 UTC [368804] LOG: statement: UPDATE
"core_content" SET "timestamp_of_interest" =
'2022-02-16T13:46:02.516956+00:00'::timestamptz WHERE
"core_content"."pulp_id" IN (SELECT U0."pulp_id" FROM "core_content" U0
ORDER BY U0."pulp_id" ASC FOR UPDATE SKIP LOCKED)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/33516#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/065.d8ff2698b66c51745a1a8208d3fe1444%40djangoproject.com.
