Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to lock a table for update and make other connections to wait on it

How to lock a table for update and make other connections to wait on it

From
"Vamsi Krishna Reddy -T (vamsredd - TATA CONSULTANCY SERVICES LIMITED at Cisco)"
Date:

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

 

 

http://www.cisco.com/web/europe/images/email/signature/logo05.jpg

Vamsi Krishna Reddy
Engineer - Test/QA
vamsredd@cisco.com
Phone: +1 978 936 3645

Cisco Systems Limited
300 Beaver Brook Road
BOXBOROUGH
MASSACHUSETTS
01719
US
Cisco.com

 

Think before you print.Think before you print.

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:
http://www.cisco.com/web/about/doing_business/legal/cri/index.html

 

 

Attachment

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

From
Daniele Varrazzo
Date:

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

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

From
Christophe Pettus
Date:
On Jul 9, 2015, at 9:31 AM, 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
fetchesthe max ‘id’ from the table (id is a column in the table which gets incremented on each insert) 

Don't do it that way.  Use the RETURNING clause on INSERT to get the inserted value.
--
-- Christophe Pettus
   xof@thebuild.com

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

From
"Karsten Hilbert"
Date:
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

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

From
"Vamsi Krishna Reddy -T (vamsredd - TATA CONSULTANCY SERVICES LIMITED at Cisco)"
Date:

Thank you.

 

Regards

Vamsi

http://www.cisco.com/web/europe/images/email/signature/logo05.jpg

Vamsi Krishna Reddy
Engineer - Test/QA
vamsredd@cisco.com
Phone: +1 978 936 3645

Cisco Systems Limited
300 Beaver Brook Road
BOXBOROUGH
MASSACHUSETTS
01719
US
Cisco.com

 

Think before you print.Think before you print.

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:
http://www.cisco.com/web/about/doing_business/legal/cri/index.html

 

 

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

 

Attachment