[Zope] Status of Zope Database Adapters (DAs)

[Jim Fulton]

Christopher Petrilli wrote:
> 
> OK gang, I've been beating my head against all the DAs of late, looking for
> thread safety issues (the long and the short is nothing is currently fully
> thread safe), however, as you read the attached document, you'll understand
> that getting them to work in a functional fashion is not going to be hard.
> We will be releasing fixed versions of Oracle, ODBC and perhaps one other in
> the very near future (this is my next task).
> 
> If you see any glaring errors, please let me know, or if you know of tricks
> related to making something work in a thread-safe fashion, please let me
> know.

I'd like to make some addition to Chris' DA status document.  
(I'll try to get this document, with my additions,
into the doc directory of the Zope distribution.)

- The global interpreter lock

  Any database extension should release the Python global interpreter
  lock when making database calls.  Database calls can take a long time
  and it makes no sense to block other threads while this is happening.

  This is important for Zope 2 even when running with one application thread
  (e.g. "python z2.py -t1").  ZServer uses a minimum of two threads, one for
  medusa and n for application threads.  A database extension that keeps the
  global interpreter lock blocks medusa.  

  A database adapter has a C extension that does not contain the string
  "Py_BEGIN_ALLOW_THREADS" is almost certainly broken *even* for 
  "single-threaded" Zope applications.

- Transactions

  For databases that support transactions, ZODB wants to synchronize
  transactions across ZODB and all of the external databases.  This is 
  a *very* Good Thing.

  Consider the following example. A request modifies some Zope 
  objects *and* modifies data in an Oracle table and in an Access table. 
  After modifying these data, an error occurs.  At this point, you want to
  undo all of the changes that were made.  Without transactions, this
  would be very difficult. The changes might have been made in very different 
  parts of the application and it would be necessary for the application to
  keep track of old values so that they could be restored.  Fortunately, 
  ZODB and transactions make error recovery simple.  The application merely
  has to:

    get_transaction().abort()

  when an error occurs.  (This happens automatically if the request was
  issued through ZPublisher.)  The ZODB transaction manager will undo changes 
  made to Zope objects *and* will issue aborts to Oracle and Access to undo
  changes made in those databases.

  If you don't think that this is *very cool*, re-read the last paragraph and think 
  about it.

  Now, supporting transactions does require some some special effort on the part of
  the DA writer, and it will require even more effort for database adapters that
  are not thread safe.  Unfortunately, we (I) haven't does a good job of communicating
  on this issue (or on DA writing in general).  

  For a DA to participate in ZODB transaction processing, it must:

    - register with the transaction manager, and

    - implement the prootcol that the transaction manager uses to
      communicate with transaction-aware objects.

  As a bonus, the protocols have changes in ZODB 3. (To implement
  two-phase commit.)

  In an effort to make implementing transaction support easier, I created 
  a mix-in class, TM, in Shared.DC.ZRDB.TM (lib/python/Shared/DC/ZRDB/TM.py).
  This class does most of the work of implementing ZODB3 aware transaction support
  for *both* ZODB 2 and ZODB 3.  See the Gadfly DA for an example of how to use this.
 
  All DAs for transaction-aware databases should either mix-in and use TM or
  otherwise implement the ZODB 3 transaction protocol.

  Now *here's the important part wrt threads*:
  --------------------------------------------

  If a database extension isn't thread safe, it isn't enough to protect extension *calls*
  with a global lock.  If a low-level database connection is shared among multiple threads,
  it isn't enough to assure that only one thread uses the connection at once.  You must assure
  that only one thread is in a *transaction* at once.  The connection must be locked  at
  transaction boundaries, not at call or even SQL boundaries.

  We intend to provide some additional guidence and software for dealling with this issue in
  the near future.

Jim

--
Jim Fulton           mailto:jim@digicool.com   Python Powered!        
Technical Director   (888) 344-4332            http://www.python.org  
Digital Creations    http://www.digicool.com   http://www.zope.org    

Under US Code Title 47, Sec.227(b)(1)(C), Sec.227(a)(2)(B) This email
address may not be added to any commercial mail list with out my
permission.  Violation of my privacy with advertising or SPAM will
result in a suit for a MINIMUM of $500 damages/incident, $1500 for
repeats.