Psycopg; How to detect row locking?

[Steve Holden]

Alban Hertroys wrote:

> Istvan Albert wrote:
> 
>> Alban Hertroys wrote:
>>
>>> I'm also not sure whether I'm actually looking at the same 
>>> transaction. Is there a way to verify such?
>>
>>
>> Are you using the same connection across threads or the
>> same cursor?
>>
>> Transactions happen per cursor. They get committed when you call
>> the commit() on the cursor (and you probably should not share the cursor
>> across threads).
> 
See below.
> 
> Ok, that means I'm looking at different transactions. That's an 
> important distinction...
> 
In that case, psycopg must be non-compliant with DBAPI, because commit() 
is a connection method, not a cursor method.

Hence my advice that all cursors were part of the same transaction.

>> Not so along ago there was a similar post. In both, the posters were
>> using a mulithreaded database system, postgresql within a mulithreaded 
>> program
>> yet at the same time desperately trying to fully control the database
>> threads from the program threads, both hoping that transactions
>> will save the day. But that is not what transactions are about. 
> 
> 
> One of those posters was probably me... I'm still kind of stuck on the 
> problem, but now knowing that I'm looking at different transactions I am 
> pretty sure that I should try a different approach.
> 
Please make sure that you correctly understand the way psycopg is acting 
before you assume that multiple cursors on the same connection are 
isolated from each other. You may be right, but if so then psycopg is 
definitely not DBAPI compliant.

>> Transactions are
>> simply a way to ensure that a series of database instructions
>> (within a thread) either all execute or none of them do. One can
>> always try to coerce them to do something fancier or more different
>> but then quality of the solution shows this.
> 
> 
> Which would have been the case if I were looking at a single 
> transaction. But apparently that's not the case. Thank you very much for 
> your help, now at least I know where I should look.
> 
> Alban.

regards
  Steve