Psycopg; How to detect row locking?

[Istvan Albert]

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

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

It makes no sense to return a "row is locked" type of error. A database is
supposed to take care of business. It will either return the new view
or the old view of the data depending on whether the new data
has been committed or not. Assuring the data was committed in
time (before selecting it) is your responsibility.

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

Istvan.