django-concurrency-talk
django-concurrency-talk copied to clipboard
pirate
Gap Locking Question, PostgreSQL
Hi @pirate thanks for putting this on GitHub. I came across it today and it was really helpful.
One thing I've been looking into for a while but haven't definitively gotten the answer to:
https://pirate.github.io/django-concurrency-talk/#sql-gap-locking
It definitely seems like "Gap Locking" applies on MySQL with InnoDB. However, I couldn't find/figure out if it applies to PostgreSQL or not. Do you know?
Not sure if it's doable in PG actually, the alternative is to do your own application-level advisory locking. Here are some links with more detail:
- https://wiki.postgresql.org/wiki/Value_locking
- https://www.postgresql.org/docs/9.4/explicit-locking.html
- https://github.com/Xof/django-pglocks
Thank you so much! My mentally proposed solution was actually to create a separate table because I had IDs from another service that were unique values. Then I'd essentially create records in that table with the unique ids, and then lock those for update. The upside of that idea is that those "lock rows" could also hold useful logging/introspection values in them in say ArrayFields, etc. and see like the last five spots that have accessed them (or however many), etc.
I may actually use that django-pglocks library that seems great!
I did also confirm Gap Locking doesn't work in PostgreSQL, at least with the settings I was using.