Psycopg; How to detect row locking?

[Steve Holden]

Alban Hertroys wrote:

> 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?
> 
If all threads are using the same database connection then you need to 
make sure that psycopg is thread-safe, which some database modules are 
and some aren't.

> 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 all threads are using the same database connection, even if you 
create multiple cursors, then you shouldn't have any locking issues 
because all threads are part of the same transaction.

>> 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.
> 
This will perhaps depend on the isolation level you've selected. I'm 
sorry, I'm not a PostgreSQL user so I don't know the detail of what's 
available, but in many databases you can determine whether transactional 
changes are visible from other connections.

> 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.

regards
  Steve