MySQL deadlock - cannot restart normally?

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 $ ;;

...

***

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.

StackExchange Database Administrators Q#9592, answer score: 4