Psycopg; How to detect row locking?
steve at holdenweb.com
Mon Sep 27 15:50:37 CEST 2004
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.
More information about the Python-list