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.
-- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
