Mysql – How I prevent deadlock occurrence in the application

deadlockinnodbMySQL

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?

Best Answer

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` = ''
;

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

Related Solutions

MySQL Deadlock – How to Resolve and Restart Normally?

This particular deadlock is being caused by attempting to read from your v3_cam_date table for inserting rows into your usertmp table while another thread is updating the v3_cam_date table. Indicated by this statement:

LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date FIELDS TERMINATED BY  ','

This documentation page has some useful steps in dealing with deadlocks, but perhaps the most useful tip in your situation might be to use the READ COMMITTED transaction isolation level when doing your insert into usrtmp table.

This basically means that what you are selecting is only the committed values, not any changes that are happening from your LOAD DATA INFILE statement if it hasn't finished committing.

If that is acceptable to you, something like this might work^*:

-- SET SESSION TO READ COMMITTED --
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- ISSUE THE INSERT...SELECT --
INSERT INTO usertmp(userid,npayvalue,balance)       
SELECT B.`userid`, SUM(C.`moneyv`) a,(B.`balance` + B.`promotions`+ B.`promotions1`+ B.`overdraft`) b
FROM `ox_campaigns` A
INNER JOIN `v3_cam_date` C ON C.`campaignid` = A.`campaignid`
INNER JOIN `selfserving_users_balances` B ON B.`userid` = A.`uid`
INNER JOIN `selfserving_users` F ON F.`user_id` = B.`userid`
INNER JOIN `selfserving_users_group_balances` E ON E.`groupid` = F.`groupid` AND E.`ispostpaid` = 0
WHERE A.`revenue_type` = 6 AND C.`dt` BETWEEN A.`activate` AND A.`expire` AND C.`dt` >=  (SELECT DATE_ADD( A.ssv_payment_date_lastest, INTERVAL 1 DAY)  FROM `000_sys_params_v3` A)
AND C.`viewcharge` >0 
GROUP BY B.`userid`

-- SET BACK TO 'DEFAULT' --
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

_{^* Note, I haven't personally tried the above to ensure READ COMMITTED will stop the deadlock.}

But how long does it take to load this file? You might add a table lock around the LOAD DATA INFILE, but of course that will make the INSERT wait for the file to load all the time.

Mysql – How to analyse innodb status on deadlock in insert Query

OBSERVATION #1

The locking of the PRIMARY KEY is much more involved that you think. It has nothing to do with the default values because the three columns define the uniqueness of the key.

OBSERVATION #2

Your PRIMARY KEY has three columns. This makes great demands on the gen_clust_index, the Clustered Index. The entire row is basically stored in the index. The locking has to serialize the creation of rowids.

OBSERVATION #3

With the large column (realm) as part of the PRIMARY KEY, this makes for a lot of BTREE operations. I say this because the max row size is 263 bytes. Assuming every row is 263 bytes, and the size of a data page is 16K, this makes for each BTREE node holding about 255 keys before splitting. I recommend storing the MD5 or SHA1 of the realm as follows:

ALTER TABLE domain_access ADD realmkey CHAR(40);
CREATE TABLE domain_access_new
(
    nid int(10) unsigned,
    gid int(10) unsigned,
    realmkey char(40),
    realm varchar(255),
    PRIMARY KEY (nid,gid,realmkey)
    KEY (realm)
) ENGINE=InnoDB;
INSERT INTO domain_access_new
SELECT nid,gid,SHA1(realm),realm FROM domain_access;
ALTER TABLE domain_access RENAME domain_access_old;
ALTER TABLE domain_access_new RENAME domain_access;
DROP TABLE domain_access_old;

With this, your PRIMARY KEY would be 48 bytes instead of 263. You should look further into make the PRIMARY KEY even smaller.

EPILOGUE

Locking of the PRIMARY KEY must occur for INSERTs, UPDATEs, and DELETEs. I remember a hefty deadlocking exercise I went through with RedBlueThing

Jun 14, 2011 : Reasons for occasionally slow queries?
Jun 08, 2011 : Will these two queries result in a deadlock if executed in sequence?
Jun 06, 2011 : Trouble deciphering a deadlock in an innodb status log

Best Answer

DISCLAIMER : Not a Codeigniter Person, Just a MySQL DBA

MECHANISM #1 : REPLACE INTO

MECHANISM #2 : INSERT ... ON DUPLICATE KEY

MECHANISM #3 : SELECT ... FOR UPDATE

EPILOGUE

Related Solutions

MySQL Deadlock – How to Resolve and Restart Normally?

Mysql – How to analyse innodb status on deadlock in insert Query

OBSERVATION #1

OBSERVATION #2

OBSERVATION #3

EPILOGUE

Related Question