Thread: psycopg concurrency control
I am working on a project using psycopg2 together with python 2.7 where I have one writer to a DB table and multiple readers from the same table. On python I am using the TCP socketserver with the ThreadingMixin where each thread create their own connection and cursor , read the data and then commit and close. This is working fine.
Right now I have a situation where I can have multiple writers to the same table and there is a chance for concurrency issues .
I am not a Postgres guy but I tried to read the documentation and I believe that Explicit Locking --> Table level Locks --> ROW EXCLUSIVE are the way to go .
Can someone give me some guidance on the concurrency control and important also how I can translate this into python psycopg code ??
Many thanks
On Sep 11, 2016, at 8:26 AM, John Lb <johnlb77@gmail.com> wrote: > I am not a Postgres guy but I tried to read the documentation and I believe that Explicit Locking --> Table levelLocks --> ROW EXCLUSIVE are the way to go . You almost certainly *don't* have to do any explicit locking, even with writers to the same table; PostgreSQL will largelyhandle the concurrency issues for you. Note that once a particular connection has written to an (existing) row, noother session can write to that same row until the first session commits or aborts; the second session will wait for thefirst transaction to finish. -- -- Christophe Pettus xof@thebuild.com
On Sep 12, 2016, at 3:43 PM, John Lb <johnlb77@gmail.com> wrote: > I did some more documentation reading and I noticed that I can use the LOCK command example : LOCK TABLEmytable IN ACCESS EXCLUSIVE MODE . Reason for ACCESS EXCLUSIVE is that there is tip in the documentation thatsays only ACCESS EXCLUSIVE can block a SELECT. > > Am I thinking right ?? You are correct in that is the only lock mode that will block a SELECT. Note that this means you will effectively have threadsgoing single-file through the database, with very significant performance penalties. You might consider using SELECT ... FOR UPDATE instead of a explicit table-level lock. This will prevent changes to therows that the thread is working on, while not blocking SELECT ... FOR UPDATE against a different set of rows. > Further a question : when doing this way , the Read Committed Isolation level can stay default ?? Yes, because there is effectively no concurrency in the database, now. -- -- Christophe Pettus xof@thebuild.com
On Sep 12, 2016, at 5:00 PM, John Lb <johnlb77@gmail.com> wrote: > Thread A writes bob in a row successfully , and then when Thread B tries to write the name bob , an error notification will appear indicating name bob is already in the row . Is this right ?? Yes, that's correct. -- -- Christophe Pettus xof@thebuild.com
Thanks for the reply.
I will be a more specific now . Instead of writer , I must say it is a read , modify and then write operation .
The situation is that first I do a SELECT , based on the read data the following instruction can be either INSERT , UPDATE or do nothing, then commit and the close .
When a thread is doing a SELECT , I want to block all the other threads for doing a SELECT on the same table . So I want only one thread to have access to the table at a time . Other threads must wait.
I did some more documentation reading and I noticed that I can use the LOCK command example : LOCK TABLE mytable IN ACCESS EXCLUSIVE MODE . Reason for ACCESS EXCLUSIVE is that there is tip in the documentation that says only ACCESS EXCLUSIVE can block a SELECT.
Am I thinking right ??
Further a question : when doing this way , the Read Committed Isolation level can stay default ??
Thanks
On Sun, Sep 11, 2016 at 7:47 PM, Christophe Pettus <xof@thebuild.com> wrote:
On Sep 11, 2016, at 8:26 AM, John Lb <johnlb77@gmail.com> wrote:
> I am not a Postgres guy but I tried to read the documentation and I believe that Explicit Locking --> Table level Locks --> ROW EXCLUSIVE are the way to go .
You almost certainly *don't* have to do any explicit locking, even with writers to the same table; PostgreSQL will largely handle the concurrency issues for you. Note that once a particular connection has written to an (existing) row, no other session can write to that same row until the first session commits or aborts; the second session will wait for the first transaction to finish.
--
-- Christophe Pettus
xof@thebuild.com
Again thanks . Now it is more clear for me after checking information about unique index. Again I am not a DB guy , but more on embedded systems where I also have to deal with concurrency and race conditions on another level .
OK let's take the hypothetical situation again .
Let's say I created the unique index on Name after the table is created.
Thread A and B have the same snapshot of the table at the beginning where there is no bob in any row . Thread A writes bob in a row successfully , and then when Thread B tries to write the name bob , an error notification will appear indicating name bob is already in the row . Is this right ??
Thanks
On Mon, Sep 12, 2016 at 7:40 PM, Christophe Pettus <xof@thebuild.com> wrote:
On Sep 12, 2016, at 4:39 PM, John Lb <johnlb77@gmail.com> wrote:
> In the table is a column called Name . Thread A wants to insert the name bob in a row , and at the same time Thread B wants to insert the name bob as well. But before inserting the names , thread A and thread B do a SELECT to check if there is the name bob already inserted in the table , if not then Thread A inserts name bob , and on the following row , I believe , Thread B also inserts name bob in the table . So there are two names of bob in the table and I want to prevent this .
This can be prevented with a unique index on Name.
Thank you very much
On Mon, Sep 12, 2016 at 8:01 PM, Christophe Pettus <xof@thebuild.com> wrote:
On Sep 12, 2016, at 5:00 PM, John Lb <johnlb77@gmail.com> wrote:
> Thread A writes bob in a row successfully , and then when Thread B tries to write the name bob , an error notification will appear indicating name bob is already in the row . Is this right ??
Yes, that's correct.