1200字范文 > 记录MYSQL死锁日志分析

记录MYSQL死锁日志分析

时间：2023-07-16 13:02:05

相关推荐

记录MYSQL死锁日志分析

项目场景：

线上环境中在写库过程中出现的一次player_property死锁报警记录

其中player_property中d_last_onlin是个耳机索引

问题描述：

关键日志记录

------------------------LATEST DETECTED DEADLOCK------------------------/**第一个事务*/-06-09 09:41:22 7f32c3a1c700*** (1) TRANSACTION:TRANSACTION 354412269, ACTIVE 0 sec fetching rowsmysql tables in use 4, locked 4// 该事务持有1650个行锁LOCK WAIT 1084 lock struct(s), heap size 128552, 1650 row lock(s), undo log entries 412MySQL thread id 25437818, OS thread handle 0x7f334427c700, query id 10260869621 10.154.9.41 *** Sending dataINSERT INTO insert_table_aSELECT NAME_CONST('i_batch_no',_utf8'0609093903' COLLATE 'utf8_general_ci'),pp.n_roleid,'rewardByCondition',NOW(), NAME_CONST('i_reward',_utf8'3_71014_30,3_8506_2,3_20_20,3_8302_2' COLLATE 'utf8_general_ci') FROM player_roles pr LEFT JOIN player_property pp ON pr.n_roleid = pp.n_roleid LEFT JOIN player_vip vip ON pr.n_roleid = vip.n_roleid WHERE pr.d_create >= NAME_CONST('i_create_begin',_latin1'-01-01 00:00:00' COLLATE 'latin1_swedish_ci') AND pr.d_create <= NAME_CONST('i_create_end',_latin1'-06-09 09:41:22' COLLATE 'latin1_swedish_ci') AND pp.n_rank >= NAME_CONST('i_rank',0) AND pp.n_lv >= NAME_CONST('i_lv',30)AND pp.d_last_online >= NAME_CONST('i_last_online',_latin1'-05-10 00:00:00' COLLATE 'latin1_swedish_ci') AND IFNULL(vip.n_lv,0) >= NAME_CONST('i_vip_lv',0)/*** lock type : RECORD LOCKS（锁的类型）* space id : 锁对象的space id* page no ：事务锁定页的数量，若是表锁，该值为null* index ：锁住的索引* table：要加锁的表* trx id：事务id* lock_mode : 锁的模式等待主键上S的锁释放*/*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 45252 page no 3567 n bits 136 index `PRIMARY` of table `DATABASE1`.`player_property` trx id 354412269 lock mode S locks rec but not gap waitingRecord lock, heap no 70 PHYSICAL RECORD: n_fields 47; compact format; info bits 0/**事务2*/*** (2) TRANSACTION:TRANSACTION 354412274, ACTIVE 0 sec starting index readmysql tables in use 1, locked 1// 持有6个行锁7 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 5MySQL thread id 25437870, OS thread handle 0x7f32c3a1c700, query id 10260869642 10.154.9.41 *** updatingupdate player_property set n_reinLv=3, n_age=0, n_back_ornament_id=0, n_chat_side=2248, s_longitude='108.300575', s_birthday='', s_head='1#u_1602066042', n_equip_smelt=75198, n_vcurrency=55471426, n_role_quality=6, n_constellation=0, n_head_side=1255, n_lv=341, s_latitude='22.830141', n_exploit=2053170, s_fashion='17556,70004,16158,19556', s_area='', s_vocation='5', s_last_mac='865809030070484', n_lovers_tree_id=102941290497, n_gender=2, s_role_skill='1_71521', d_last_online='-06-09 09:41:04', s_rank_speak='[]', s_character='1_55,2_55,3_49,4_69,5_63,6_52', s_official_slap_head='20001_1613738865', n_popular=4933148, n_exp=787543330, n_online_time=10473708, n_camp=2, s_online='1', n_last_channel=12, n_marriage_id=102941380497, n_fans=59, n_rank=17, n_charmnum=3101335, s_sign='@予我歌丶时光静好，与君语；细水流年，与君同；繁华落尽，与君老。', n_retinue_id=15002, n_fo// 持有主键上的X锁*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 45252 page no 3567 n bits 136 index `PRIMARY` of table `DATABASE1`.`player_property` trx id 354412274 lock_mode X locks rec but not gapRecord lock, heap no 70 PHYSICAL RECORD: n_fields 47; compact format; info bits 0/**等待X锁释放*/*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 45252 page no 2297 n bits 136 index `PRIMARY` of table `DATABASE1`.`player_property` trx id 354412274 lock_mode X locks rec but not gap waitingRecord lock, heap no 66 PHYSICAL RECORD: n_fields 47; compact format; info bits 0// 回滚*** WE ROLL BACK TRANSACTION (2)