[Zope-dev] Bug in all database adapters?

Shai Berger shai@platonix.com
Thu, 09 Aug 2001 16:24:41 +0300


"Matthew T. Kromer" wrote:
> 
> Actually, it's probably worse than that --
> 
> Multiple transactions can be in flight at any given time;
> without explicitly coding a transaction ID into databases like
> Oracle, everything falls under "default transaction" which
> essentially means anything on a connection gets a new
> transaction ID every commit or rollback.  However, a connection
> object could theoretically be shared between threads (Zope tries
> to avoid this by creating new instances of objects for each
> thread, but you can work around that if you try hard enough).
> Thus, since commit/rollback happens on the CONNECTION and not
> the STATEMENT (cursor), if two separate threads use the same
> connection object and one aborts, the other will have its data
> rolled back at the database level but not at Zope.  This is NOT
> the common case -- you'd have to really try hard to share
> objects between threads, because ZODB will make a NEW object per
> thread (so you would have to have a product that participated in
> the sharing) -- but it's still possible, IMHO.
> 
Interesting... but IMHO, very theoretical, as you say. The problem
I'm talking about is quite practical -- in fact, a minor and potetially
useful change to my SqlSDC product may bring it about: As written now,
it locks the database rows for a session for the length of the transaction.
If it were modified to release the lock between reading session data 
(in the beginning) and writing it (at the end) it may fail to acquire
the lock at the final stage, which will raise exactly this hell.

> However, I'm not sure if the solution is as simple as fixing TM
> to provide a tm_abort or not.  I dont have any good test cases
> to reproduce the behavior, so I end up doing a lot of guessing
> as to what the proper solution should be without being able to
> prove it.
> 
I don't have the time to make it now, but the test case (for the
problem I describe) should be fairly simple:

- Write an External function which registers a class instance
  with the transaction. In the class, implement a tpc_begin which
  throws an exception.
- Write a ZSQL method which locks some table
- Write a method which calls first the ZSQL Method, then the
  External method, and call it.

As the first phase of the commit is done according to registration order,
this ensures that the DB connection will get tpc_begin and then tpc_abort, 
and the table should stay locked; you will be able to run the same method
again only from the same thread.

I am going out of office for a week, so I will not be able to follow
this closely right now; I hope by the time I returned all relevant
parties are convinced :-)

Have fun,
	Shai.