#Php catch mysql deadlock and try again update

We are seeing a consistent pattern where under high volume we see deadlocks on INSERT INTO civicrm_email - via contact.create api (called in turn from a job api by drush). Per ' InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock.' However, it turns out that mysql often rolls back more than just the query it was doing when it decided there was a deadlock. The above scenario is good because once the deadlock was resolved we were able to retry and it worked.

When don't we do a good job with deadlocks

the Dataobject code retries the query and succeeds.

the deadlock is caught in packages/DB/Dataobject.

Neither of the other 2 can get the exclusive lock as they both have shared locks

the first query 'gets the lock' - the other 2 get shared locks while they wait.

#Php catch mysql deadlock and try again update

The query to update the employer name field is called by all 3.contact create is called by 3 different processes in pretty quick succession.The expectation is that the application layer will handle & retry.įor example the query in this PR was causing deadlocks - here is how I think the flow worked - note the queries are not slow but this is under high volume. However, in some cases mysql cannot resolve it and it returns a deadlock error. Mysql tries to manage contention under load to the extent that can be done at the DB layer, allowing tables & rows to be locked & queuing transactions that need to use those resources to complete after that. I want to re-open the discussion on how & where we catch them & roll them back.ĭeadlocks are a 'natural' part of mysql scalability. This seems to work and be helpful in some cases but in others it doesn't work and is possibly harmful. Some time ago we added handling to the DataObject class to catch and retry deadlocks.