Let's say I have a tabel similar to this one:
PERSON:
id | person_name
ITEM:
id | name | person_id
Items person_id is a FK to person.
I have an operation that add's items in bulk to a person. But I want to make sure no other processes are adding items concurrently to this person without blocking the entire person table.
Is there a way to achieve this in Postgres? And better a the code to accomplish this using Python SQLalchemy?
I think you want to use SELECT FOR UPDATE via Query.with_for_update. Just a warning though you have to be carefully to always lock in the same order and same way or else you can easily lockup your threads. Ie. don't lock table A then lock table B in one area of code and lock table B and then table A in another area of code because it can cause a deadlock.
# Other calls to SELECT FOR UPDATE will BLOCK when they try to lock this person
person = session.query(Person).with_for_update().filter(Person.id == 5).first()
# Update person items here
for item in person.items[0:3]:
session.delete(item)
session.commit()
https://www.postgresql.org/docs/9.0/sql-select.html#SQL-FOR-UPDATE-SHARE
Just locking the specific person will prevent other sessions from inserting items referencing that person.
select * from person where id=? for update
