Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
How long are the hangs? The default lock wait timeout is 50 seconds. Deadlocks are detected immediately. –  Aaron Brown Mar 24 '12 at 13:46
    
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. –  shayan Mar 24 '12 at 16:51
add comment

1 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).

share|improve this answer
    
i decrease innodb_lock_wait_timeout and increase ..._pool_size but sometimes i get a error 2 secound after running code. i have no idea why 2 second!? –  shayan Mar 24 '12 at 19:57
    
You will need to provide more details about what queries are causing this problem. The output of SHOW ENGINE INNODB STATUS\G and SHOW FULL PROCESSLIST\G while the problem is occurring would be a good place to start. –  Aaron Brown Mar 24 '12 at 22:27
    
i used ENGINE INNODB STATUS but beacause of apache error it doesnt help and i cant see any output. –  shayan Mar 25 '12 at 8:38
    
You need to look at SHOW ENGINE INNODB STATUS and SHOW FULL PROCESSLIST from within the mysql command line client, not from apache. –  Aaron Brown Mar 26 '12 at 1:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.