[Zope] Why SQLSession 0.3.0 sometimes gets stuck

[Shai Berger]

Dear Anthony and Zopers,

At Aristocart, we had been using SQLSession until a few
months ago, for our e-commerce web application. We kept
having problems where those sessions would get stuck, 
and sometimes cause the whole Zope instance to become
hosed. After a few weeks of fiddling with the details
of the SQL Methods (in part to add expiration to the
sessions), we dispared and moved to CoreSessionTracking.
CST 0.8 solved our hangs, crashes and stickiness problems.
But we still needed a SQL-based sessioning mechanism,
because this was standing between us and eliminating
Zope's single-point-of-failure problem (as we keep all
the application data in the relational database, if
we could just move the sessions there too, we could
have multiple Zope servers with read-only identical ZODBs).

I ended up solving the problem by developing special
product for keeping CST sessions on an Oracle database,
SqlSDC. However, in the last phases of the struggle to
make it work, I found out what was the problem with
SQLSession. It is easy enough to fix, but I don't have
the time to do it myself.

The problem is, essentially, with the order of commits.
SQLSession assumes that commiting is done in the order of 
registration, and the documentation even mentions a
patch to Transaction.py to make its registration look
like it happened earlier -- to ensure it is committed
before the relevant database connection. This is a 
sensible assumption, and is almost correct, but not
correct enough.

When a Zope transaction includes other transactions -- 
like a relational database transaction -- it uses two
phase commit. This means that there are two rounds of
messages: The first, "tpc_begin", says essentially,
"prepare to commit"; the second, "tpc_finish", says
"commit". Most database adapters, and other naive
descendents of ZRDB.TM (including SQLSession 0.3.0), 
ignore the first, and call their _finish() method 
when they receive the second.

However, while tpc_begin messages are sent according
to the order of registration, the transaction mechanism
uses the loop going over registered objects to put
those objects in a dictionary, and the tpc_finish calls
are done by looping on the values of this dictionary.
Effectively, this means the tpc_finish messages are
sent in _random_ order. From the transaction's point
of view, this does not make awful nonsense: They have
all been warned, and second-phase commits should be trivial
operations that never fail -- like a commit on a database,
or a flush on a file.

If you think about it a bit longer, the first phase of
a two-phase-commit is the most natural place for actions
such as running update and insert SQL commands for keeping
saving sessions into a database. It is a sort of preparation,
to be finalized by the database commit.

The solution, therefore, is simple: Modify SQLSession by
overriding TM's tpc_begin, tpc_abort and tpc_finish so
that the _finish method is called from tpc_begin, and
tpc_finish does nothing. I do not remember the SQLSession
source details so well, but if the registered object is
in the ZODB, this will lead to a conflict error; to bypass
this, you need a separate object to handle registration.
To see a way of doing this, you are invited to look at
the source of my SessionDataContainerBase.

Both above-mentioned products are available from 
http://www.zope.org/Members/shai.

Have fun,
	Shai.