Обсуждение: How to lock a table for update and make other connections to wait on it
Hi Team
This is second time I am contacting you. Thanks for your support and solution provided for my previous problem.
Now I am facing a new problem. Below is the detailed description of the problem.
We have multiple instances of a program which runs on different locations and inserts certain data in the same database.
Below are the steps followed by an instance of the program
1. Insert a row to a table , using cursor.execute()
2. To fetch the row id of the above inserted data, we are using cursor.execute() to execute a sql command which fetches the max ‘id’ from the table (id is a column in the table which gets incremented on each insert)
Here the problem is, as we have parallel processes updating the same db and same table, before we execute step 2 there might be one more row inserted into the table by a different process.
In this case step 2 returns me the wrong id.
So could you please let me know, if there a way to lock the table before step1 and unlock it after step2. If yes, how does the other parallel connection behaves when it encounters a lock.
Thanks & Regards
Vamsi
| ||||
Vamsi Krishna Reddy | Cisco Systems Limited | |||
|
This email may contain confidential and privileged material for the sole use of the intended recipient. Any review, use, distribution or disclosure by others is strictly prohibited. If you are not the intended recipient (or authorized to receive for the recipient), please contact the sender by reply email and delete all copies of this message. For corporate legal information go to: |
Think before you print.
Вложения
Thank you.
Regards
Vamsi
| ||||
Vamsi Krishna Reddy | Cisco Systems Limited | |||
|
This email may contain confidential and privileged material for the sole use of the intended recipient. Any review, use, distribution or disclosure by others is strictly prohibited. If you are not the intended recipient (or authorized to receive for the recipient), please contact the sender by reply email and delete all copies of this message. For corporate legal information go to: |
Think before you print.
From: Daniele Varrazzo [mailto:daniele.varrazzo@gmail.com]
Sent: Thursday, July 09, 2015 1:25 PM
To: Vamsi Krishna Reddy -T (vamsredd - TATA CONSULTANCY SERVICES LIMITED at Cisco)
Cc: psycopg@postgresql.org; Virag Jain (virajain)
Subject: Re: [psycopg] How to lock a table for update and make other connections to wait on it
On Thu, Jul 9, 2015 at 5:31 PM, Vamsi Krishna Reddy -T (vamsredd - TATA CONSULTANCY SERVICES LIMITED at Cisco) <vamsredd@cisco.com> wrote:
1. Insert a row to a table , using cursor.execute()
2. To fetch the row id of the above inserted data, we are using cursor.execute() to execute a sql command which fetches the max ‘id’ from the table (id is a column in the table which gets incremented on each insert)
You can use "INSERT ... RETURNING id" and avoid any concurrency issues.
-- Daniele