sybase_ct: deadlock_retry_count View as Markdown

Hi list, sybase_ct maintainer,

we are running a php based web application together with Sybase
ASE-11.9.2 as the database backend. The production environment sees
increasing traffic as more and more users start working with the system.
Unfortunately some nasty database inconsistencies occur from time to
time, which in the worst case block the users from the system.

I finally traced down the problem to be deadlock related. We utilize
several transactions and the chance of deadlocking increases with
concurrency. Running two instances of a cli test script, which stresses
the server heavily with concurrent transactions, frequent deadlocks can
be observed in the Sybase server log.

The DB server checks at periodic intervals for deadlocks by examining
the system lock table and rolls back the youngest transaction returning
error 1205 on the associated connection.

Strangely enough the script did never log a DB error message 1205 and
stop after the first deadlock was resolved by a rollback but continues
and occasionally writes inconsistent data to the DB.

I found the reason for this behaviour in the default setting of
deadlock_retry_count which is -1 for unlimited retries. In this case the
LAST SQL-statement of the transaction rolled back will be retried, which
is wrong for all cases I could imagine. This will probably succeed, but
will write inconsistent information to the database in some cases. If
anything should be retried, then the WHOLE transaction should be, but
this would call for a dedicated deadlock handler.

Setting sybct.deadlock_retry_count=0 in php.ini switches off retries and
consistently aborts the script in case of rolling back a deadlocked
transaction and thus avoids further uncontrolled execution.

My questions to the sybase_ct (Hi Timm ;-) ) maintainer:

1. How is the deadlock/retry logic in sybase_ct_query() intended to
   work? From my understanding a single SQL statement will never deadlock.

2. The default setting of deadlock_retry_count = -1 leads to
   uncontrolled behaviour, if external transactions deadlock and are rolled
   back by the server. In this case a clean exit is much better than
   retrying the LAST statement of the aborted transaction. My suggestion
   therefore is changing the default to deadlock_retry_count=0.

Hope this helps ... Michael U.