线上一次死锁分析
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-09-07 20:29:31 0x7f3459881700
*** (1) TRANSACTION:
TRANSACTION 4882261, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 88870, OS thread handle 139863061001984, query id 6605667 event_scheduler Sending data
update Room A inner join(select roomid,lrcurrent,robertnum from temp3 B) c on A.roomid = c.roomid
set A.lrCurrent = c.lrcurrent,A.robertnum=c.robertnum,A.allnum=c.lrcurrent+c.robertnum
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 246 page no 30 n bits 104 index PRIMARY of table `xinxinlive`.`Room` trx id 4882261 lock_mode X locks rec but not gap waiting
Record lock, heap no 36 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 4; hex 80000748; asc H;;
1: len 6; hex 0000004a7f2a; asc J *;;
2: len 7; hex 59000001320320; asc Y 2 ;;
3: len 4; hex 8131318f; asc 11 ;;
4: len 4; hex 8131318f; asc 11 ;;
5: len 4; hex 80000001; asc ;;
6: len 30; hex 687474703a2f2f746869726477782e716c6f676f2e636e2f6d6d6f70656e; asc http://thirdwx.qlogo.cn/mmopen; (total 128 bytes);
7: len 4; hex 8000001a; asc ;;
8: len 4; hex 7fffffff; asc ;;
9: len 4; hex 80000065; asc e;;
10: len 4; hex 80000000; asc ;;
11: len 4; hex 80000064; asc d;;
12: len 4; hex 80000000; asc ;;
13: len 4; hex 5b926f1e; asc [ o ;;
14: len 4; hex 80000000; asc ;;
15: len 4; hex 7fffffff; asc ;;
16: len 30; hex 72746d703a2f2f6c697665777370756c6c2e353178756568616f2e636f6d; asc rtmp://livewspull.51xuehao.com; (total 44 bytes);
17: len 30; hex 72746d703a2f2f6c6976657773707573682e353178756568616f2e636f6d; asc rtmp://livewspush.51xuehao.com; (total 44 bytes);
18: len 9; hex e696b0e4b9a1e5b882; asc ;;
*** (2) TRANSACTION:
TRANSACTION 4882257, ACTIVE 0 sec starting index read
mysql tables in use 6, locked 6
45 lock struct(s), heap size 8400, 1689 row lock(s), undo log entries 17
MySQL thread id 87520, OS thread handle 139862817117952, query id 6605663 192.168.10.44 db_live_shop_JavaGo_mofan_v1 Sending data
CREATE TEMPORARY TABLE temp_a SELECT ll.useridx,ll.gender,ll.bigpic,ll.city,
ll.curexp,ll.grade,ll.level,ll.myname,
ll.province,ll.signatures,ll.smallpic,
ru.roomid,ru.photo
,ifnull(ro.rtmp,'') as rtmp
,ifnull(ro.location,'') as location
,ifnull(ro.lrCurrent,0) as lrCurrent
,ifnull(ro.allnum,0) as allnum
,ifnull(s.ip,'') as ip
,ifnull(s.sport,'') as sport
,ifnull(s.hk_serverip,'') as hk_serverip
,ifnull(rh.timestamp,0) as timestamp
,rh.isStick
FROM Room_hotrank rh
INNER join RoomOnLineUser ru ON rh.roomid = ru.roomid
-- inner JOIN UserWhitelist w on rh.useridx=w.userIdx
left join Room ro on ro.useridx = rh.useridx
left join Live_UserInfo ll on ll.useridx = rh.useridx
LEFT JOIN Live_ServerIpInfo s on s.id=ro.serverid
where rh.useridx=ru.useridx and rh.useridx not in(20002293,20002313)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 246 page no 30 n bits 104 index PRIMARY of table `xinxinlive`.`Room` trx id 4882257 lock mode S locks rec but not gap
Record lock, heap no 36 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 4; hex 80000748; asc H;;
1: len 6; hex 0000004a7f2a; asc J *;;
2: len 7; hex 59000001320320; asc Y 2 ;;
3: len 4; hex 8131318f; asc 11 ;;
4: len 4; hex 8131318f; asc 11 ;;
5: len 4; hex 80000001; asc ;;
6: len 30; hex 687474703a2f2f746869726477782e716c6f676f2e636e2f6d6d6f70656e; asc http://thirdwx.qlogo.cn/mmopen; (total 128 bytes);
7: len 4; hex 8000001a; asc ;;
8: len 4; hex 7fffffff; asc ;;
9: len 4; hex 80000065; asc e;;
10: len 4; hex 80000000; asc ;;
11: len 4; hex 80000064; asc d;;
12: len 4; hex 80000000; asc ;;
13: len 4; hex 5b926f1e; asc [ o ;;
14: len 4; hex 80000000; asc ;;
15: len 4; hex 7fffffff; asc ;;
16: len 30; hex 72746d703a2f2f6c697665777370756c6c2e353178756568616f2e636f6d; asc rtmp://livewspull.51xuehao.com; (total 44 bytes);
17: len 30; hex 72746d703a2f2f6c6976657773707573682e353178756568616f2e636f6d; asc rtmp://livewspush.51xuehao.com; (total 44 bytes);
18: len 9; hex e696b0e4b9a1e5b882; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 246 page no 22 n bits 128 index PRIMARY of table `xinxinlive`.`Room` trx id 4882257 lock mode S locks rec but not gap waiting
Record lock, heap no 55 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 4; hex 800006c6; asc ;;
1: len 6; hex 0000004a7f12; asc J ;;
2: len 7; hex 45000001ac1843; asc E C;;
3: len 4; hex 81313106; asc 11 ;;
4: len 4; hex 81313106; asc 11 ;;
5: len 4; hex 80000001; asc ;;
6: len 30; hex 687474703a2f2f746869726477782e716c6f676f2e636e2f6d6d6f70656e; asc http://thirdwx.qlogo.cn/mmopen; (total 127 bytes);
7: len 4; hex 8000001a; asc ;;
8: len 4; hex 7fffffff; asc ;;
9: len 4; hex 80000065; asc e;;
10: len 4; hex 80000000; asc ;;
11: len 4; hex 80000064; asc d;;
12: len 4; hex 80000000; asc ;;
13: len 4; hex 5b926f0d; asc [ o ;;
14: len 4; hex 80000000; asc ;;
15: len 4; hex 7fffffff; asc ;;
16: len 30; hex 72746d703a2f2f6c697665777370756c6c2e353178756568616f2e636f6d; asc rtmp://livewspull.51xuehao.com; (total 44 bytes);
17: len 30; hex 72746d703a2f2f6c6976657773707573682e353178756568616f2e636f6d; asc rtmp://livewspush.51xuehao.com; (total 44 bytes);
18: len 12; hex e69da5e887aae781abe6989f; asc ;;
从上可以看出CREATE TEMPORARY TABLE temp_a SELECT这个存储过程正在持有一个S锁（共享锁）。
定时任务update Room A inner join(select roomid,lrcurrent,robertnum from temp3 B) c on A.roomid = c.roomid set A.lrCurrent = c.lrcurrent,A.robertnum=c.robertnum,A.allnum=c.lrcurrent+c.robertnum，要给index PRIMARY of table `xinxinlive`.`Room`这张表的主键上一个X（排他锁），所以造成了锁等待，从而死锁
重点优化在存储过程，因为存储过程占有了1689 row lock(s)，1689行锁，所以最先优化的是他。
一般来说解决锁的方法就是减少持有时间与资源和减少循环竞争。
拆分CREATE TEMPORARY TABLE temp_a SELECT语句，重写语句，先SELECT出来变量，然后新建一张表，进行批量插入操作。这样可以减少锁占用行数。并且改写批量插入可以减少锁的持有时间。
