Does sqlalchemy ORM locks tables while update/insert operation with postgresql

Question

Does sqlalchemy ORM locks tables while update/insert operation with postgresql. I am trying to make sure that if i run a cron script which is based on python. I am not using 'begin()' with 'with statement' in my code. If my script is running and performing some DELETE/UPDATE/INSERT operation or that time being is postgres table or DataBase is locked by script. Which means if i hit a script to insert a record in DB then will it be inserted or give me error.

It depends on which database software you are using. For most cases, like MySQL, the database won't lock at all, and they support transaction to make sure there's no strange results in one commit. — yxzlwz
– yxzlwz, Commented Dec 26, 2022 at 8:19

Mikko Ohtamaa · Accepted Answer · 2022-12-26 08:45:19Z

1

PostgreSQL rarely locks any tables. This is because PostgreSQL uses optimistic concurrency control. Instead of upfront locking up tables, conflicting transactions that touch the same rows, are rolled back. This model is much more performance friendly and developer friendly for database workloads that rarely cause conflicting transactions.

These rolled-back transactions need to be retried on the client side. Usually this happens by replaying the HTTP requests.

For the retry logic, here is an example implementation from Zope.
More examples can be found from Django, other development frameworks that deal with this problem frequently

answered Dec 26, 2022 at 8:45

Mikko Ohtamaa

85k63 gold badges296 silver badges479 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Does sqlalchemy ORM locks tables while update/insert operation with postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related