vt7.3 Database Lock Timeout caused by multiple database Transactions being started
We have added a custom module with an EventHandler during which triggers a Save on an Instance of a ServiceContract Record. When this EventHandler is triggered via the front-end (creation of a new Record in the custom module) it all works correctly. When the EventHandler is triggered via Webservice (creation of a new Record in the custom module) we kept getting an error and tracked it back to being a InnoDB Lock Timeout.
Through several hours of debugging, we have discovered that during the create process that in certain places the global variable for the database is used:
global $adb;
and queries are run against this object which also keeps track of database Transactions.
However in other places the global variable is not used and instead the Record module (which has a $db variable as part of the class) is used. When this is used sometimes the PearDatabase object does not have the database connection initialised upon creation, so when a query is run against that object it tries to start the transaction (/libraries/adodb/adodb.inc.php - StartTransaction() line 1021) which checks the connection is setup, and if its not then creates a new one, and starts another transaction in this connection.
This works fine as long as in the two transactions (which are now running at the same time, rather than being inside the same transaction which is intended) you don't update the same table!
However as we did edit the same table twice, once in each transaction, the second Update fails as it hits the InnoDb Lock Wait Timeout.
The fact that it only occurs when creating via the Webservice would indicate that somehow this error is avoided when creating via the GUI (we could not see how!)
We solved this by updating the various locations we spotted that called
$this->db->pquery()
to global $adb; $adb->pquery()
For us this was /modules/ServiceContracts/ServiceContracts.php in calculateProgress() function switching them all to global $adb; $adb->pquery()
I suspect however that this issue is lurking in all other modules, and MANY other places though! Wouldnt it be a good idea to ensure that everything references the global $adb variable instead of constantly passing a database variable around inside each record model AS WELL!