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

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