How I prevent deadlock occurrence in my application? I am developing an LMS application in PHP framework(Codeigniter 2.1.0). I am using MySQL database.
All the tables in the database have innodb engine. I also created indexes on each tables.
Now I am doing load testing using Jmeter version 2.9 locally for 200 users concurrently. During the load testing, in a specific page action I got Deadlock Found error. I changed my original query to the new one but again same error is occurring.

I have written save_interactions function which takes four parameters interaction array,module_id,course_id,user_id &amp; is been called so many times by the AJAX script.
The following script inserts the record if the specific interaction_id is not present in that table otherwise the update query will get fire.

public function save_interactions($interaction_array,$modid,$cid,$uid)
{
    foreach($interaction_array as $key=&gt;$interact_value)
    {
        $select_query = $this-&gt;db-&gt;query("SELECT COUNT(*) AS total FROM `scorm_interactions` WHERE `mod_id`='".$modid."' AND `course_id`='".$cid."' AND `user_id`='".$uid."' AND `interaction_id`='".$interact_value[0]."'");
        $fetchRow = $select_query-&gt;row_array();

        if($fetchRow['total']==1)
        {
            $update_data = array(
                        "interaction_type"=&gt;$interact_value[1],
                        "time"=&gt;$interact_value[2],
                        "weighting"=&gt;$interact_value[3],
                        "correct_response"=&gt;$interact_value[4],
                        "learner_response"=&gt;$interact_value[5],
                        "result"=&gt;$interact_value[6],
                        "latency"=&gt;$interact_value[7],
                        "objectives"=&gt;$interact_value[8],
                        "description"=&gt;$interact_value[9]
            );
            $this-&gt;db-&gt;where('mod_id', $modid);
            $this-&gt;db-&gt;where('course_id', $cid);
            $this-&gt;db-&gt;where('user_id', $uid);
            $this-&gt;db-&gt;where('interaction_id', $interact_value[0]);
            $this-&gt;db-&gt;update('scorm_interactions', $update_data);
        }else
        {
            $insert_data = array(
                        "user_id"=&gt;$uid,
                        "course_id"=&gt;$cid,
                        "mod_id"=&gt;$modid,
                        "interaction_id"=&gt;$interact_value[0],
                        "interaction_type"=&gt;$interact_value[1],
                        "time"=&gt;$interact_value[2],
                        "weighting"=&gt;$interact_value[3],
                        "correct_response"=&gt;$interact_value[4],
                        "learner_response"=&gt;$interact_value[5],
                        "result"=&gt;$interact_value[6],
                        "latency"=&gt;$interact_value[7],
                        "objectives"=&gt;$interact_value[8],
                        "description"=&gt;$interact_value[9]
            );
            $this-&gt;db-&gt;insert('scorm_interactions', $insert_data);
        }
    }
}


I got this type of error:

Deadlock found when trying to get lock; try restarting transaction

UPDATE `scorm_interactions` SET
    `interaction_type` = 'choice',
    `time` = '10:45:31',
    `weighting` = '1',
    `correct_response` = 'Knees*',
    `learner_response` = 'Knees*',
    `result` = 'correct',
    `latency` = '0000:00:02.11',
    `objectives` = 'Question2_1',
    `description` = ''
WHERE
    `mod_id` =  '4' AND
    `course_id` =  '5' AND
    `user_id` =  '185' AND
    `interaction_id` =  'Question2_1'
;

Filename: application/models/user/scorm1_2_model.php Line Number: 234


Can anyone please suggest me how to avoid Deadlock?
DISCLAIMER : Not a Codeigniter Person, Just a MySQL DBA

Looking at the flow of control in the PHP. It says to me:


Check the following mod_id,course_id,user_id,interaction_id
If not found, INSERT the new row
If found, UPDATE all the other columns


There are three(3) mechanisms provided for INSERT and UPDATE scenarios

MECHANISM #1 : REPLACE INTO

REPLACE INTO `scorm_interactions` SET
    `mod_id` =  '4',
    `course_id` =  '5',
    `user_id` =  '185',
    `interaction_id` =  'Question2_1',
    `interaction_type` = 'choice',
    `time` = '10:45:31',
    `weighting` = '1',
    `correct_response` = 'Knees*',
    `learner_response` = 'Knees*',
    `result` = 'correct',
    `latency` = '0000:00:02.11',
    `objectives` = 'Question2_1',
    `description` = ''
;


or

REPLACE INTO `scorm_interactions`
(`mod_id`,`course_id`,`user_id`,`interaction_id`,
`interaction_type`,`time`,`weighting`,`correct_response`,
`learner_response`,`result`,`latency`,`objectives`,`description`) VALUES
('4','5','185','Question2_1','choice','10:45:31','1','Knees*',
'Knees*','correct','0000:00:02.11','Question2_1','');


This mechanically performs DELETE and INSERT on mod_id, course_id , user_id, interaction_id

MECHANISM #2 : INSERT ... ON DUPLICATE KEY

INSERT INTO `scorm_interactions`
(`mod_id`,`course_id`,`user_id`,`interaction_id`,
`interaction_type`,`time`,`weighting`,`correct_response`,
`learner_response`,`result`,`latency`,`objectives`,`description`) VALUES
('4','5','185','Question2_1',
'choice','10:45:31','1','Knees*',
'Knees*','correct','0000:00:02.11','Question2_1','')
ON DUPLICATE KEY UPDATE
    `interaction_type` = 'choice',
    `time` = '10:45:31',
    `weighting` = '1',
    `correct_response` = 'Knees*',
    `learner_response` = 'Knees*',
    `result` = 'correct',
    `latency` = '0000:00:02.11',
    `objectives` = 'Question2_1',
    `description` = ''
;


MECHANISM #3 : SELECT ... FOR UPDATE

Did you know you can perform an exclusive lock on rows you intend to update with precision? 

According the MySQL Documentation, you can run a SELECT query on the very row and issue a row lock along the way. For example:

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;


This will lock the entire table and allow an UPDATE of the counter_field column.

In your case, simply run the two queries back-to-back

SELECT * FROM `scorm_interactions` 
WHERE `mod_id` =  '4',
AND `course_id` =  '5',
AND `user_id` =  '185',
AND `interaction_id` =  'Question2_1'
FOR UPDATE ;
UPDATE `scorm_interactions` SET
    `interaction_type` = 'choice',
    `time` = '10:45:31',
    `weighting` = '1',
    `correct_response` = 'Knees*',
    `learner_response` = 'Knees*',
    `result` = 'correct',
    `latency` = '0000:00:02.11',
    `objectives` = 'Question2_1',
    `description` = ''
WHERE `mod_id` =  '4'
AND `course_id` =  '5',
AND `user_id` =  '185',
AND `interaction_id` =  'Question2_1'
;


EPILOGUE

As a safety cushion, try increasing innodb_lock_wait_timeout if the traffic is write-heavy. See my StackOverflow post : How to debug Lock wait timeout exceeded?

Give it a Try !!!