Re: How to lock a table for update and make other connections to wait on it - Mailing list psycopg

From Karsten Hilbert
Subject Re: How to lock a table for update and make other connections to wait on it
Date
Msg-id trinity-e5f61172-56ce-4b5e-9832-df3f03c1824e-1436480441290@3capp-gmx-bs07
Whole thread Raw
In response to How to lock a table for update and make other connections to wait on it  ("Vamsi Krishna Reddy -T (vamsredd - TATA CONSULTANCY SERVICES LIMITED at Cisco)" <vamsredd@cisco.com>)
List psycopg
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
fetchesthe 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
mightbe one more row inserted  into the table by a different process. 
In this case step 2 returns me the wrong id.


You need:   INSERT ... RETURNING ...

Karsten


psycopg by date:

Previous
From: Christophe Pettus
Date:
Subject: Re: How to lock a table for update and make other connections to wait on it
Next
From: "Vamsi Krishna Reddy -T (vamsredd - TATA CONSULTANCY SERVICES LIMITED at Cisco)"
Date:
Subject: Re: How to lock a table for update and make other connections to wait on it