MySQL deadlock - cannot restart normally?

Question

MySQL version 5.5.13-1

A snippet from the SHOW ENGINE INNODB STATUS\G:

LATEST DETECTED DEADLOCK
------------------------
111218 10:22:34
*** (1) TRANSACTION:
TRANSACTION 1318D95B, ACTIVE 0 sec starting index read
mysql tables in use 6, locked 6
LOCK WAIT 53 lock struct(s), heap size 14776, 77 row lock(s)
MySQL thread id 60933, query id 124472414 192.168.6.31 thanhnt Copying to tmp table
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`
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 222397 page no 2576 n bits 304 index `GEN_CLUST_INDEX` of table `reportingdb`.`v3_cam_date` /* Partition `p3` */ trx id 1318D95B lock mode S locks rec but not gap waiting
Record lock, heap no 170 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
 0: len 6; hex 0000a61821ff; asc     ! ;;
 1: len 6; hex 00000b168ac0; asc       ;;
 2: len 7; hex 5200035d760432; asc R  ]v 2;;
 3: len 3; hex 8f661d; asc  f ;;
 4: len 3; hex 0000ac; asc    ;;
 5: len 4; hex 000cc08c; asc     ;;
 6: len 3; hex 0000ac; asc    ;;
 7: len 3; hex 000000; asc    ;;
 8: len 4; hex 000852a6; asc   R ;;
 9: len 3; hex 0519d2; asc    ;;
 10: len 3; hex 00009b; asc    ;;
 11: len 3; hex 8fb747; asc   G;;
 12: len 3; hex 800000; asc    ;;
 13: len 4; hex 00000000; asc     ;;
 14: len 4; hex 00249eda; asc  $  ;;

    ...

*** (2) TRANSACTION:
TRANSACTION 1318D958, ACTIVE 1 sec inserting, thread declared inside InnoDB 123
mysql tables in use 3, locked 3
4808 lock struct(s), heap size 555448, 1122860 row lock(s), undo log entries 7058
MySQL thread id 130380, query id 124472416 192.168.6.31 thanhnt
LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date FIELDS TERMINATED BY  ','
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 222397 page no 2576 n bits 304 index `GEN_CLUST_INDEX` of table `reportingdb`.`v3_cam_date` /* Partition `p3` */ trx id 1318D958 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 222397 page no 6296 n bits 1000 index `ix_campaignid_dt` of table `reportingdb`.`v3_cam_date` /* Partit
ion `p3` */ trx id 1318D958 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 213 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 3; hex 8f63a5; asc  c ;;
 1: len 3; hex 8fb73b; asc   ;;;
 2: len 6; hex 0000a617374f; asc     7O;;

*** WE ROLL BACK TRANSACTION (1)

Sometime, it combines with a huge of "Waiting for global read lock" in the SHOW FULL PROCESSLIST\G and my database is locked completely. Cannot restart with the init script, so I must do a kill -9 <PID> (it causes a lot of crashed tables).

InnoDB Lock Monitor was enabled follow this guide. information_schema.innodb_locks is empty and belows is the innodb_trx table:

*************************** 1. row ***************************
                    trx_id: 135937A0
                 trx_state: RUNNING
               trx_started: 2011-12-19 11:16:04
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 43929
       trx_mysql_thread_id: 257731
                 trx_query: INSERT INTO `__tZoneTempTable` (zoneid, rc, v, dt)  
    (SELECT a.`zoneid`, sum(a.`realclick`) as rc, sum(a.`totalview`) as v , a.`dt` FROM `v3_zone_date` a INNER JOIN `ox_zones` b ON a.`zoneid` = b.`zoneid` where b.`is_smallbiz` = 3 AND b.`deleted` = 0 AND a.`zoneid` > 0 AND a.`dt` =  NAME_CONST('_date',_latin1'2011-12-17' COLLATE 'latin1_swedish_ci') GROUP BY a.`zoneid`)
       trx_operation_state: fetching rows
         trx_tables_in_use: 10
         trx_tables_locked: 9
          trx_lock_structs: 43929
     trx_lock_memory_bytes: 6093240
           trx_rows_locked: 15961781
         trx_rows_modified: 0
   trx_concurrency_tickets: 359
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
*************************** 2. row ***************************
                    trx_id: 13590BD9
                 trx_state: RUNNING
               trx_started: 2011-12-19 11:15:23
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 43929
       trx_mysql_thread_id: 257456
                 trx_query: INSERT INTO `__tZoneTempTable` (zoneid, rc, v, dt)  
    (SELECT a.`zoneid`, sum(a.`realclick`) as rc, sum(a.`totalview`) as v , a.`dt` FROM `v3_zone_date` a INNER JOIN `ox_zones` b ON a.`zoneid` = b.`zoneid` where b.`is_smallbiz` = 3 AND b.`deleted` = 0 AND a.`zoneid` > 0 AND a.`dt` =  NAME_CONST('_date',_latin1'2011-12-17' COLLATE 'latin1_swedish_ci') GROUP BY a.`zoneid`)
       trx_operation_state: fetching rows
         trx_tables_in_use: 10
         trx_tables_locked: 9
          trx_lock_structs: 43929
     trx_lock_memory_bytes: 6093240
           trx_rows_locked: 15961781
         trx_rows_modified: 0
   trx_concurrency_tickets: 359
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
*************************** 3. row ***************************
                    trx_id: 1358EA92
                 trx_state: RUNNING
               trx_started: 2011-12-19 11:14:57
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 257363
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 376
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000

As @Chris suggested, I would like to provide the SHOW CREATE TABLE outputs:

mysql> show create table v3_cam_date\G
*************************** 1. row ***************************
       Table: v3_cam_date
Create Table: CREATE TABLE `v3_cam_date` (
  `campaignid` mediumint(9) NOT NULL DEFAULT '0',
  `totalclick` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `totalview` int(11) unsigned NOT NULL DEFAULT '0',
  `realclick` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `clickcharge` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `viewcharge` int(11) unsigned NOT NULL DEFAULT '0',
  `uv` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `uc` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `dt` date NOT NULL DEFAULT '0000-00-00',
  `ctr` decimal(5,3) NOT NULL DEFAULT '0.000' COMMENT '=-1: meaning not available(N/A)',
  `moneyc` int(11) unsigned NOT NULL DEFAULT '0',
  `moneyv` int(11) unsigned NOT NULL DEFAULT '0',
  KEY `ix_campaignid_dt` (`campaignid`,`dt`),
  KEY `ix_dt` (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(dt))
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p01 VALUES LESS THAN (734502) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (734683) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

This deadlock situation happens frequently. I want to find out what causes this to avoid in the future.

Answer 1

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.

Answer 2

DEADLOCK EXPLANATION

If the underlying tables use the InnoDB Storage Engine, row locks (even on the same row) can never block reads, but deadlocks are still possible during writes. With AUTOCOMMIT=0 set in /etc/my.cnf, each DML statement (INSERT,UPDATE, and DELETE) will be executed as a single line transaction. Individual row locks are issued. Thus, 50 DB Connections can go after 50 different rows and nothing tragic happens.

Where can deadlocks come in?

Since the PRIMARY KEY of InnoDB tables is contained within the Clustered Index (internally known as the gen_clust_index), the row data is tightly coupled with the index entries. Any index made against columns not part of the PRIMARY KEY are cataloged with tow basic items, the column value and the gen_clust_index key. At times, update indexes columns in InnoDB may cause what I jokingly call index constipation. That's occurs when two or more locks are generated on index entries stored close to one another. This is possible in a heavily trafficked website.

I once helped a Developer see why this can happen in the DBA StackExchange. That developer made code changes afterwards. Here were those posts:

• Will these two queries result in a deadlock if executed in sequence?
• Trouble deciphering a deadlock in an innodb status log
• Reasons for occasionally slow queries?

YOUR QUESTION

In the snippet you provided, you have this:

/* Partition p3 */ trx id 1318D95B lock mode S locks rec but not gap waiting

This message show a Shared Row Locked (lock mode S) in this Partition. Somewhere in the SHOW ENGINE INNODB STATUS\G, you should also have seen another Row Lock tagged as Exclusive (lock mode X).