Psycopg; How to detect row locking?

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

Jim



More information about the Python-list mailing list