[Zope-dev] connection commit ordering

[Leonardo Rochael Almeida]

Hi,

In ERP5[1], which is CMF based, we have a number of strategies for
high performance and scalability. One of these is that we have
ZSQLCatalog extensively. The other is that we delay execution of
potentially expensive operations (like indexing) for background
execution. For the latter, we store the information about background
tasks to be executed (path to affected object, method to call,
serialization and ordering tags) in an SQL table. Background requests
(clock-server) then look up the activity table to either distribute
the tasks between the nodes of a ZEO cluster or to execute a
previously distributed task.

In one specific client with a very high volume of transactions, we
were experiencing failures in these background executions. We traced
it, among other things, to the ordering of connections during commit.
Here is what happened.

 1. An object in the ZODB
 2. the .reindexObject() method of this object schedules a task for
the real indexation to the background processes, using a ZMySQLDA
connector.
 3. The transaction machinery performs the commit, ordering the
connections according to the .sortKey() method of each connection:
    3.1. All ZMySQLDA connectors involved, since their .sortKey()
returns the integer 1 (see Shared.ZRDB.TM.TM.sortKey() )
    3.2. all mounted ClientStorages or FileStorages involved, whose
.sortKey()s are strings which sort after integers.

If in between 3.1 and 3.2 a background process tries to execute the
scheduled activity commited on 3.1, then it will see the new
information on the 'background-tasks' table but the object to be
indexed will not yet be in the ZODB causing the activity to fail.

The solution we found involves changing the result of .sortKey() for
the transaction manager of the database connection, but we can't do
this globally for all connectors, otherwise we could have the
connector for the SQL based catalog being committed after the
connector for the background tasks, and we would end up with a similar
error situation. The adapter for the background tasks must necessarily
commit after all data needed by the background tasks was already
committed.

By the way, this issue is completely separate from the
two-phase-commit discussion that we had recently, since all the
connectors involved here are fully transactional.

At Nexedi, we concluded that we might need to be able to customize the
sortKey() per database-adapter instance in Zope, since different
adapters might need to be committed in different order. Unfortunately
it looks like the connection sorting machinery was intended only to
obtain a consistent ordering to avoid deadlocks from competing
clients, instead of establishing dependency relationships between the
connectors, since there seems to be no standard on what the sorting
keys should be (they're integers for Shared.ZRDB.TM.TM and strings for
ZODB storages).

To make this easier without requiring reimplementation of the
.sortKey() method in all database connectors, I took the liberty of
creating a branch of Zope 2.12 [2] that adds a .setSortKey() method to
Shared.ZRDB.TM.TM and I'd welcome opinions.

In any case, we were left wondering if others have faced similar
issues with the commit order and if others have any opinions on this
problem.

Cheers,

Leo

[1] http://erp5.com/

[2] http://svn.zope.org/repos/main/Zope/branches/rochael-TM_sortKey/