Psycopg; How to detect row locking?

[Alban Hertroys]

Jorge Godoy wrote:
> Are you manually locking those rows?  If so, you can maintain some
> structure to keep track of locked rows.

No, I don't. That may be a solution, though. Then at least I *know* when 
a record is locked... But it's going to complicate things, if the 
transaction is split among multiple threads... I'm not looking forward 
to that.

I think the rows are locked because the inserts haven't finished 
inserting yet. The select takes place in the same session AFAIK, but not 
in the same thread of my Python application. I'm probably looking at a 
race condition here... (Ain't multithreading fun...)

I'm also not sure whether I'm actually looking at the same transaction. 
Is there a way to verify such?

I do know for certain that all transactions use the same database 
connection (I pass it along in a context object, together with config 
settings and debugging methods). And I'm also quite sure that it doesn't 
commit in between.

> If you are not locking, PostgreSQL uses MVCC where it locks as little as
> possible and you are able to select the new data inside the same
> transaction and old data outside of it (until it is commited).

I suppose there must be a short while where the row is locked during the 
insert, where I may already be trying to select it. If this is indeed 
the case, I would expect to receive a "row is locked" type of error.

Alternatively, the select may be waiting (w/in psql) until the insert 
finished (which should be pretty soon in all cases[*]), but that depends 
on implementations beyond my reach. Not that that matters, I shouldn't 
have this problem in that case.


[*] Unless you break your database with triggers that lock up or 
something similar. That could be a reason for the PostgreSQL team to not 
  let select wait until an insert on the same row finished, but to 
return an error instead.