CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Pablo Estrada
Pablo Estrada

Reputation: 3452

Lock Adding rows with a specific Foreign Key ID on Postgres/SQLAlchemy

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?

Upvotes: 1

Views: 546

Answers (2)

Ian Wilson
Ian Wilson

Reputation: 9059

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://docs.sqlalchemy.org/en/14/orm/query.html?highlight=for_update#sqlalchemy.orm.Query.with_for_update

https://www.postgresql.org/docs/9.0/sql-select.html#SQL-FOR-UPDATE-SHARE

Upvotes: 2

jjanes
jjanes

Reputation: 44192

Just locking the specific person will prevent other sessions from inserting items referencing that person.

select * from person where id=? for update

Upvotes: 0

Related Questions