Psycopg; How to detect row locking?

[Alban Hertroys]

Jorge Godoy wrote:
>>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...)
> 
> If there's no data then a SELECT would return nothing at all, without
> any error after all you're querying the database for some information
> that doesn't exist.

You're right. I thought I had finally found the problem, but apparently 
I'll have to look further :(

>>I'm also not sure whether I'm actually looking at the same transaction. 
>>Is there a way to verify such?
> 
> You have to look at psycopg's docs.  I use pyPgSQL here.

I'm afraid that documentation on psycopg is rather scarce. The most 
enlightening is the documentation of DBAPI 2.0, but that's not specific 
to the psycopg implementation of course.

>>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.
> 
> You would benefit a lot of transactions if you are inserting a lot of
> data or if there's some relationship between data (and constraints and
> triggers and ... at the database).  The INSERT isn't commited until you
> issue a COMMIT.  Any SELECT before that will return nothing.  If you're
> not using transactions, then you are hitting the disk for each and every
> command.  I've made some tests here and the difference goes from some
> seconds (with transactions) to several minutes (without transactions)
> for a 65k rows insert on an old project we did. 

Not to mention the possibility to rollback if a query in the transaction 
fails. I'm so glad I didn't choose for something crude like MySQL ;)

> They use MVCC: Multi-Version Concurrency Control.  You might want to
> read about it:
> 
>         http://www.linuxgazette.com/issue68/mitchell.html
>         http://www.developer.com/open/article.php/877181

Actually I did know that, I just forgot to remember ;). I used to work 
with Oracle, and it works sort of the same way.

Thanks for the help, I'll have to find a better culprit.

Alban.