Mysql – apache hang on deadlock

deadlockMySQL

i use code like this to prevent deadlock.

while($retry<3 and $notsone)
{
try
{
$transaction->commit();
$notdone=false;
}
catch
{
$transaction->rollback();
$retry++;
}
}
if ($retry==3)
{
throw new exeption("deadlock found!");
}

i test code with running code in 6 different browser concurrently . sometimes in some browser i see a deadlock found message but sometimes i got a message from apache http server.
apache http server has stopped working. i have 2 option. close program or check for online solution.

my question is do concurrency problem cause apache hang?
how to prevant this?

Best Answer

MySQL has built in deadlock detection and detects a deadlock as soon as it occurs. If you need to detect deadlocks, you will have to examine the error message coming back from the failed transaction, or use pt-deadlock-logger to log deadlocks external to the application.

The code you have supplied doesn't detect deadlocks. It detects failed transactions and retries 3 times. If the transaction took a long time, you will occupy an apache worker for three times as long as the transaction took. If the failure occurs due to a lock timing out (note locks and deadlocks are very different), the code above could take up to 2.5 minutes to return to the browser, assuming you have innodb_lock_wait_timeout set to the default of 50 seconds. That's a long long time.

Can we step back and ask what is it that you are trying to accomplish with this code path?

(side note, I think this belongs on stackoverflow, not here as this seems more like a PHP/development question).

Related Solutions

SQL Server 2008 – Deadlock Error Not Returning Deadlock SQL

The data you need is recorded in the default extended events trace.

DECLARE @xml XML

SELECT @xml = target_data
FROM   sys.dm_xe_session_targets
       JOIN sys.dm_xe_sessions
         ON event_session_address = address
WHERE  name = 'system_health'
       AND target_name = 'ring_buffer'

SELECT   
             XEventData.XEvent.query('(data/value/deadlock)[1]')  AS DeadlockGraph,
             CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS XML) AS DeadlockGraph,
              XEventData.XEvent.value('(./@timestamp)[1]', 'DATETIME2') AS [DateTime]
FROM   (SELECT @xml AS TargetData) AS Data
       CROSS APPLY 
       TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent) 
ORDER BY [DateTime] DESC

Though it won't be there any more if you have restarted the service -e.g. to apply a trace flag or if the buffer has cycled in the meantime.

You can set up your own extended events trace that stores the deadlock graph to a file target for persistent non volatile storage. Example Code here. I personally find the deadlock graph XML more friendly than the trace flag output.

Edit

@MartinC points out in the comments that on instances of SQL Server that don't have all the updates there might be a problem with it generating invalid XML. The fix for that is to do some search and replace and use CAST(REPLACE(REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'), '<victim-list>', '<deadlock><victim-list>'), '<process-list>', '</victim-list><process-list>') AS XML) AS DeadlockGraph in the SELECT list as described here.
Wayne Sheffield has posted a useful script to shred the deadlock graph XML into tabular format here.

Mysql – Cannot update certain rows in innodb tables

There are situations where InnoDB deadlock would come up when you least expect it. For example, SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three past questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that even SELECT queries can lock InnoDB data. Although autocommit could be enabled (rendering each query its own transaction), a single SQL statement can still be victimized by deadlocks.

You also have special cases of SELECT where you can lock specific rows on demand.

Based on InnoDB Deadlocking link, the sequences of event to cause this situation could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk instead of just documenting and hopong you google for it. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

Best Answer

Related Solutions

SQL Server 2008 – Deadlock Error Not Returning Deadlock SQL

Mysql – Cannot update certain rows in innodb tables

Related Question