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 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 & 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=>$interact_value)
    {
        $select_query = $this->db->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->row_array();

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

share|improve this question

1 Answer 1

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 !!!

share|improve this answer
    
Thanks for replying. I increased innodb_lock_wait_timeout to 120 sec as you said. I also read your StackOverflow Post: How to debug Lock wait timeout exceeded? I have used mechanism no 3.....i.e. SELECT... FOR UPDATE. But I am getting syntax error. I mentioned a error below. –  Rohit Gaikwad Mar 13 '13 at 10:05
    
A Database Error Occurred.Error Number: 1064.You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE scorm_interactions SET interaction_type = 'choice', time = '10:45:3' at line 1. –  Rohit Gaikwad Mar 13 '13 at 10:05
    
I updated my answer. I had missed a comma in the SELECT just after mod_id. I also forgot the actual FOR UPDATE clause. –  RolandoMySQLDBA Mar 13 '13 at 10:13

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.