Psycopg; How to detect row locking?
jhefferon at smcvt.edu
Tue Sep 28 15:04:41 CEST 2004
Steve Holden <steve at holdenweb.com> wrote
> Please make sure that you correctly understand the way psycopg is acting
> before you assume that multiple cursors on the same connection are
> isolated from each other. You may be right, but if so then psycopg is
> definitely not DBAPI compliant.
This is from the README for the (reasonably fresh) version of psycopg that
I happen to have (I couldn't find a link, so I'm pasting):
Extensions to the Python DBAPI-2.0
psycopg offers some little extensions on the Python DBAPI-2.0. Note that the
extension do not make psycopg incompatible and you can still use it without
ever knowing the extensions are here.
The DBAPI-2.0 mandates that cursors derived from the same connection are not
isolated, i.e., changes done to the database by one of them should be
immediately visible by all the others. This is done by serializing the queries
on the same physical connection to the database (PGconn struct in C.)
Serializing queries when the network latencies are hight (and network speed is
low) dramatically lowers performance, so it is possible to put a connection
into not-serialized mode, by calling the .serialize() method giving it a
0-value argument or by creating a connection using the following code:
conn = psycopg.connect("dbname=...", serialize=0)
After that every cursor will get its own physical connection to the database
and multiple threads will go at full speed. Note that this feature makes the
new cursors non-compliant respect to the DBAPI-2.0.
The main extension is that we support (on not-serialized cursors) per-cursor
commits. If you do a commit() on the connection all the changes on all the
cursors derived from that connection are committed to the database (in random
order, so take your care.) But you can also call commit() on a single cursor
to commit just the operations done on that cursor. Pretty nice.
Note that you *do have* to call .commit() on the cursors or on the connection
if you want to change your database. Note also that you *do have* to call
commit() on a cursor even before a SELECT if you want to see the changes
apported by other threads to the database.
So, although you can turn it off, by default (and with some justification) the
behavior is non-compliant.
More information about the Python-list