存储过程调试过程

记一次MYSQL存储过程调试过程

···
以下存储过程在最近总是偶尔出现返回失败的情况，由于看不到异常信息，所以查找原因颇费周折。

DELIMITER $$

USE `IotProductDB`$$

DROP PROCEDURE IF EXISTS `PROC_PRODUCT_PERSRESULT_CARD`$$

CREATE DEFINER=`links`@`%` PROCEDURE `PROC_PRODUCT_PERSRESULT_CARD`(IN `IN_PERS_ICCID` VARCHAR(20),IN `IN_ICCID` VARCHAR(20),IN `IN_ASSET_ID` VARCHAR(20),
 IN `IN_RSTCODE` VARCHAR(4), IN `IN_RSTINFO` VARCHAR(100), IN `IN_TASKNO` VARCHAR(20), IN `IN_PDN` VARCHAR(20),OUT `OUT_RESULT` VARCHAR(2))
BEGIN
        #定义变量
        DECLARE V_MANUFACTURERCODE VARCHAR(10) DEFAULT NULL;
    DECLARE V_MANUFACTURERNAME VARCHAR(50) DEFAULT NULL;
    DECLARE V_PARTNERCODE VARCHAR(32) DEFAULT NULL;
    DECLARE V_PARTNERNAME VARCHAR(30) DEFAULT NULL;
    DECLARE V_ROAMSIM_CODE VARCHAR(50) DEFAULT NULL;
    DECLARE V_ROAMSIM_VERSION VARCHAR(20) DEFAULT NULL;
        DECLARE V_RODUCT_CODE VARCHAR(200) DEFAULT NULL;
        DECLARE V_CARD_MATERIAL_CODE VARCHAR(25) DEFAULT NULL;
        DECLARE V_PRODUCTTYPE VARCHAR(2) DEFAULT NULL;
        DECLARE V_M2MCARD_SCLIENT_DATE VARCHAR(5) DEFAULT NULL;
        DECLARE V_M2MCARD_STOP_DATE VARCHAR(5) DEFAULT NULL;
        DECLARE V_OTA_FLAG VARCHAR(2) DEFAULT NULL;
        DECLARE V_MORE_IMSI_FLAG VARCHAR(2) DEFAULT NULL;
        DECLARE V_INUSE_INDUSTRY VARCHAR(50) DEFAULT NULL;
        DECLARE V_OTA_PROTOCOL_VERSION VARCHAR(50) DEFAULT NULL;
        DECLARE V_LIFECYCLE VARCHAR(2) DEFAULT NULL;
    DECLARE V_ASSIGN_TIME VARCHAR(30) DEFAULT NULL;
    DECLARE V_LIFECYCLE_STARTTIME VARCHAR(30) DEFAULT NULL;
    DECLARE V_PRODUCT_TYPE VARCHAR(2) DEFAULT NULL;
    DECLARE V_BIP_PARAM_NAME VARCHAR(50) DEFAULT NULL;
        DECLARE V_DEVICE_STATUS VARCHAR(2) DEFAULT NULL;
    DECLARE V_TWICE_TYPE VARCHAR(2) DEFAULT NULL;
    DECLARE V_ASSET_SIGN VARCHAR(30) DEFAULT NULL;
    DECLARE V_CVT INT(2);

        # 定义游标遍历时，作为判断是否遍历完全部记录的标记
    DECLARE num INT DEFAULT 0;
    # 集合变量
    DECLARE V_ICCID VARCHAR(20) DEFAULT NULL;
    DECLARE V_IMSI VARCHAR(20) DEFAULT NULL;
    DECLARE V_NUMBERTYPE VARCHAR(2) DEFAULT NULL;
        DECLARE V_SUPPLIERCODE VARCHAR(12) DEFAULT NULL;
    DECLARE V_SUPPLIERNAME VARCHAR(100) DEFAULT NULL;
        DECLARE V_OPERATORCODE VARCHAR(4) DEFAULT NULL;
    DECLARE V_OPERATORNAME VARCHAR(50) DEFAULT NULL;
    DECLARE V_COVER_COUNTRY VARCHAR(2000) DEFAULT NULL;
    # 定义游标，并将sql结果集赋值到游标中
    DECLARE softsim_list CURSOR FOR SELECT `iccid`,`imsi`,`number_type`,`supplier_code`,`supplier_name`,`operator_code`,
           `operator_name`,`cover_country` FROM `production_log_t` WHERE asset_sign=(
            SELECT asset_sign FROM `production_log_t` WHERE pers_data_iccid=IN_PERS_ICCID AND task_no=IN_TASKNO
                    AND (asset_id=IN_ASSET_ID OR dispersion_factor=IN_ASSET_ID) LIMIT 1);
    # 声明当游标遍历完全部记录后将标志变量置成某个值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET num=1;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET OUT_RESULT='0';
    SET OUT_RESULT = '1';
    START TRANSACTION;#事务;
    SELECT `manufacturer_code`,`manufacturer_name`,`partner_code`,`partner_name`,`roamingsim_code`,`roamingsim_version`,`product_code`,
    `card_material_code`,`product_type`,`m2mcard_slient_date`,`m2mcard_stop_date`,`ota_flag`,`more_imsi_flag`,`inuse_industry`,
    `ota_protocol_version`,`product_type`,`twice_type` INTO V_MANUFACTURERCODE,V_MANUFACTURERNAME,V_PARTNERCODE,
    V_PARTNERNAME,V_ROAMSIM_CODE,V_ROAMSIM_VERSION,V_RODUCT_CODE,V_CARD_MATERIAL_CODE,V_PRODUCTTYPE,V_M2MCARD_SCLIENT_DATE,V_M2MCARD_STOP_DATE,
    V_OTA_FLAG,V_MORE_IMSI_FLAG,V_INUSE_INDUSTRY,V_OTA_PROTOCOL_VERSION,V_PRODUCT_TYPE,V_TWICE_TYPE
    FROM `simcard_production_task_t` WHERE task_no=IN_TASKNO LIMIT 1;

    IF '0000' = IN_RSTCODE THEN #生产成功
        # 如果异常数据表中有该记录的数据则删除
        DELETE FROM `simcard_product_abnormal_t`
        WHERE task_no=IN_TASKNO AND eficcid=IN_PERS_ICCID AND pdn=IN_PDN;

        SELECT asset_sign INTO V_ASSET_SIGN  FROM `production_log_t` WHERE pers_data_iccid=IN_PERS_ICCID AND task_no=IN_TASKNO
                AND (asset_id=IN_ASSET_ID OR dispersion_factor=IN_ASSET_ID) LIMIT 1;

        UPDATE production_log_t SET `status`='2', finish_time=NOW(), rst_code=IN_RSTCODE, rst_info=IN_RSTINFO
        WHERE task_no=IN_TASKNO AND asset_sign=V_ASSET_SIGN;

        # 打开游标
        OPEN softsim_list;
            # 将游标中的值赋值给变量，要注意sql结果列的顺序
             FETCH softsim_list INTO V_ICCID,V_IMSI,V_NUMBERTYPE,V_SUPPLIERCODE,V_SUPPLIERNAME,V_OPERATORCODE,V_OPERATORNAME,V_COVER_COUNTRY;
            # while循环
            WHILE num <> 1 DO
                IF '2'=V_NUMBERTYPE THEN
                 UPDATE softsim_resource_info_t SET `status`='02' WHERE iccid=V_ICCID;
                 IF '2'=V_OTA_FLAG || '1'=V_TWICE_TYPE THEN

                    INSERT INTO `asset_softsim_usage_t` (
                      `asset_id`,`iccid`,`imsi`,`status`,`create_date`,`cover_mcc`,`operator_code`,`operator_name`,`supplier_code`,`supplier_name`,
                      `multi_imsi_flag`,`soft_sim_type`
                     )
                     VALUES(
                       IN_ASSET_ID,V_ICCID,V_IMSI,'1',NOW(),V_COVER_COUNTRY,V_OPERATORCODE,V_OPERATORNAME,V_SUPPLIERCODE,V_SUPPLIERNAME,
                       V_MORE_IMSI_FLAG,'2'
                     );
                 END IF;
                    ELSE
                            IF '1'=V_OTA_FLAG THEN

                    INSERT INTO `asset_softsim_usage_t` (
                      `asset_id`,`iccid`,`imsi`,`status`,`create_date`,`cover_mcc`,`operator_code`,`operator_name`,`supplier_code`,`supplier_name`,
                      `multi_imsi_flag`,`soft_sim_type`
                     )
                     VALUES(
                       IN_ASSET_ID,V_ICCID,V_IMSI,'1',NOW(),V_COVER_COUNTRY,V_OPERATORCODE,V_OPERATORNAME,V_SUPPLIERCODE,V_SUPPLIERNAME,
                       V_MORE_IMSI_FLAG,'2'
                     );
                    END IF;
                    END IF;


              FETCH softsim_list INTO V_ICCID,V_IMSI,V_NUMBERTYPE,V_SUPPLIERCODE,V_SUPPLIERNAME,V_OPERATORCODE,V_OPERATORNAME,V_COVER_COUNTRY;

            END WHILE;
            # 关闭游标
           CLOSE softsim_list;


        IF V_PARTNERCODE='0' THEN
            SET V_LIFECYCLE='5';
        ELSE
            SET V_LIFECYCLE='0';
            SET V_ASSIGN_TIME=NOW();
        END IF;

        SET V_LIFECYCLE_STARTTIME=DATE_FORMAT(NOW(),'%Y%m%d');

        IF '4'=V_PRODUCT_TYPE && '2'=V_TWICE_TYPE THEN

           SET V_DEVICE_STATUS='1';
           UPDATE `asset_info_t` SET `roamingsim_code`=V_ROAMSIM_CODE,`version`=V_ROAMSIM_VERSION,lifecycle=V_LIFECYCLE,
           lifecycle_start_time=V_LIFECYCLE_STARTTIME,task_no=IN_TASKNO,manufacturer_code=V_MANUFACTURERCODE,
           manufacturer_name=V_MANUFACTURERNAME,in_time=NOW(),activate_flag='0',partner_name=V_PARTNERNAME,partner_code=V_PARTNERCODE,
           data_encrypt_factor=IN_ASSET_ID,assign_time=V_ASSIGN_TIME,is_online='0',silent_cycle=V_M2MCARD_SCLIENT_DATE,
           stop_service_period=V_M2MCARD_STOP_DATE,init_flag='0',ota_flag=V_OTA_FLAG,bip_flag='0',inuse_industry=V_INUSE_INDUSTRY,
           more_imsi_flag=V_MORE_IMSI_FLAG,device_form='1',manu_type=V_PRODUCT_TYPE,ota_protocol_version=V_OTA_PROTOCOL_VERSION,
           bip_param_name=V_BIP_PARAM_NAME,device_status=V_DEVICE_STATUS,card_material_code=V_CARD_MATERIAL_CODE
           WHERE asset_id=IN_ASSET_ID;

           UPDATE asset_lifecycle_t SET cyclelife=V_LIFECYCLE,cyclelife_start_time=V_LIFECYCLE_STARTTIME,STATUS='1',createtime=NOW()
           WHERE asset_id=IN_ASSET_ID;

        ELSE

                 SET V_DEVICE_STATUS='3';
                 UPDATE `asset_info_t` SET `roamingsim_code`=V_ROAMSIM_CODE,`version`=V_ROAMSIM_VERSION,lifecycle=V_LIFECYCLE,
           lifecycle_start_time=V_LIFECYCLE_STARTTIME,task_no=IN_TASKNO,manufacturer_code=V_MANUFACTURERCODE,
           manufacturer_name=V_MANUFACTURERNAME,in_time=NOW(),activate_flag='0',partner_name=V_PARTNERNAME,partner_code=V_PARTNERCODE,
           data_encrypt_factor=IN_ASSET_ID,assign_time=V_ASSIGN_TIME,is_online='0',silent_cycle=V_M2MCARD_SCLIENT_DATE,
           stop_service_period=V_M2MCARD_STOP_DATE,init_flag='0',ota_flag=V_OTA_FLAG,bip_flag='0',inuse_industry=V_INUSE_INDUSTRY,
           more_imsi_flag=V_MORE_IMSI_FLAG,device_form='1',manu_type=V_PRODUCT_TYPE,ota_protocol_version=V_OTA_PROTOCOL_VERSION,
           bip_param_name=V_BIP_PARAM_NAME,device_status=V_DEVICE_STATUS,card_material_code=V_CARD_MATERIAL_CODE
           WHERE asset_id=IN_ASSET_ID;

           UPDATE asset_lifecycle_t SET cyclelife=V_LIFECYCLE,cyclelife_start_time=V_LIFECYCLE_STARTTIME,STATUS='1',createtime=NOW()
           WHERE asset_id=IN_ASSET_ID;
        END IF;

        # 修改任务成功数量
        UPDATE simcard_production_task_t SET actual_quantity=actual_quantity+1 WHERE task_no=IN_TASKNO;

    ELSE # 生产失败

        SELECT COUNT(*) INTO V_CVT FROM simcard_product_abnormal_t
        WHERE task_no=IN_TASKNO AND eficcid=IN_PERS_ICCID AND pdn=IN_PDN;
        IF V_CVT=0 THEN #如果不存在则添加异常数据记录
            INSERT INTO `simcard_product_abnormal_t` (`task_no`, `pdn`, `eficcid`, `abnormal_code`, `abnormal_desc`, `create_time`)
            VALUES (IN_TASKNO, IN_PDN, IN_PERS_ICCID, IN_RSTCODE, IN_RSTINFO, NOW());
        END IF;

        #修改生产日志信息
        UPDATE `production_log_t` SET `status`='3' WHERE eficcid=IN_PERS_ICCID AND task_no=IN_TASKNO;

    END IF;

    SELECT COUNT(*) INTO V_CVT FROM production_log_t WHERE task_no=IN_TASKNO AND (`status`='0' OR `status`='1') FOR UPDATE;
    SELECT OUT_RESULT;
    IF 0=V_CVT THEN # 全部生产完毕


        SELECT COUNT(*) INTO V_CVT FROM simcard_production_task_t WHERE task_no=IN_TASKNO AND quantity=actual_quantity FOR UPDATE;
        IF 0 <> V_CVT THEN # 已经全部成功


            UPDATE simcard_production_task_t SET `status`='3', end_date=NOW()
            WHERE task_no=IN_TASKNO;


            INSERT INTO hist_simcard_production_task_t SELECT * FROM simcard_production_task_t WHERE task_no=IN_TASKNO;

            DELETE FROM simcard_production_task_t WHERE task_no=IN_TASKNO;


                        INSERT INTO `simcard_product_tracker_t`(`task_no`,`oper_type`,`oper_content`,`oper_name`,`oper_date`)
                        VALUES (IN_TASKNO,'4','系统结束生产任务','pms_gateway',SYSDATE());


            INSERT INTO `hist_production_log_t` SELECT * FROM `production_log_t` WHERE task_no=IN_TASKNO;
            DELETE FROM production_log_t WHERE task_no=IN_TASKNO;


            UPDATE device_info_t SET `status`='1',task_type='' WHERE manufacturer_name=V_MANUFACTURERNAME AND pdn = IN_PDN;

        ELSE
            UPDATE simcard_production_task_t SET end_date=NOW() WHERE task_no=IN_TASKNO;
        END IF;
    END IF;

    IF '0'=OUT_RESULT THEN
        ROLLBACK;#回滚
    ELSE
        COMMIT;#提交
    END IF;
    END$$

DELIMITER ;

存储过程写的非常复杂，最主要的一点是发生异常时直接回滚，返回OUT_RESULT=0，无法得知异常信息，所以首要的是先给存储过程加上异常处理。下边是一段存储过程异常处理的方式，直接插入数据库表中，当然实际生产时，除了要插入异常信息，还要和相关的业务信息进行关联。其于以下的方式对存储过程进行改造，改造后的就不帖了。

DROP PROCEDURE IF EXISTS proc_exception;
CREATE PROCEDURE proc_exception()
BEGIN
DECLARE v_commit INT DEFAULT 1; -- 定义事务用，1为正常，0为失败
DECLARE mysql_error_code TEXT;-- 记录错误信息
DECLARE mysql_error_msg TEXT;-- 记录错误信息
 mysql_error_code = RETURNED_SQLSTATE, mysql_error_msg = MESSAGE_TEXT;
-- 异常的时候msg捕获报错信息
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN get diagnostics CONDITION 1  mysql_error_code = RETURNED_SQLSTATE, mysql_error_msg = MESSAGE_TEXT;  SET v_commit = 0; END ;

START TRANSACTION;-- 设置事务

-- 业务相关语法
INSERT INTO ......;

IF v_commit = 0 THEN
ROLLBACK;
INSERT INTO proc_error_log VALUES (mysql_error_code,mysql_error_msg);
END IF ;

END;

因为失败是偶尔出现的，后来手动执行没啥问题，初步怀疑是并发问题。然后使用JMETER进行压力测试，第一次使用，还真是挺好用的。当进行20个用户发并调用存储过程时，只有第一个成功，其他都失败了。。。。。。，好惨啊！
查看proc_error_log表中的记录数据，显示
|code|message|
|—|—|
|1|2|
|40001|Deadlock found when trying to get lock; try restarting transaction|
|40001|Deadlock found when trying to get lock; try restarting transaction|
|40001|Deadlock found when trying to get lock; try restarting transaction|
|40001|Deadlock found when trying to get lock; try restarting transaction|
|40001|Deadlock found when trying to get lock; try restarting transaction|
|…|…|

好吧，死锁问题，使用命令 SHOW ENGINE INNODB STATUS 查看日志，查找死锁原因。（其他参考命令SHOW STATUS LIKE ‘innodb_row_lock%’;SHOW GLOBAL VARIABLES LIKE ‘innodb_lock_wait_timeout%’;）日志如下：

第一次压测后获取的日志：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-10-24 11:48:01 7f01e69cd700
*** (1) TRANSACTION:
TRANSACTION 60863969, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 12 lock struct(s), heap size 2936, 7 row lock(s), undo log entries 3
MySQL thread id 370111, OS thread handle 0x7f01e6253700, query id 10273081 123.58.107.133 iottest updating
UPDATE asset_lifecycle_t SET cyclelife=V_LIFECYCLE,cyclelife_start_time=V_LIFECYCLE_STARTTIME,STATUS='1',createtime=NOW()
           WHERE asset_id=IN_ASSET_ID
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6455 page no 4 n bits 232 index `PRIMARY` of table `IotProductDB`.`asset_lifecycle_t` trx id 60863969 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000001d9b9bd; asc       ;;
 2: len 7; hex d4000013250110; asc     %  ;;
 3: len 15; hex 383630353939303033303139313731; asc 860599003019171;;
 4: len 1; hex 32; asc 2;;
 5: len 8; hex 3230313930313239; asc 20190129;;
 6: SQL NULL;
 7: len 19; hex 323031392d30312d32392032303a35353a3336; asc 2019-01-29 20:55:36;;
 8: len 1; hex 31; asc 1;;
 9: len 8; hex 3230313930323238; asc 20190228;;

*** (2) TRANSACTION:
TRANSACTION 60863913, ACTIVE 6 sec fetching rows
mysql tables in use 1, locked 1
13203 lock struct(s), heap size 1291816, 1282571 row lock(s), undo log entries 5
MySQL thread id 370090, OS thread handle 0x7f01e69cd700, query id 10273182 123.58.107.133 iottest Sending data
SELECT COUNT(*) INTO V_CVT FROM production_log_t WHERE task_no=IN_TASKNO AND (`status`='0' OR `status`='1') FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6455 page no 4 n bits 232 index `PRIMARY` of table `IotProductDB`.`asset_lifecycle_t` trx id 60863913 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000001d9b9bd; asc       ;;
 2: len 7; hex d4000013250110; asc     %  ;;
 3: len 15; hex 383630353939303033303139313731; asc 860599003019171;;
 4: len 1; hex 32; asc 2;;
 5: len 8; hex 3230313930313239; asc 20190129;;
 6: SQL NULL;
 7: len 19; hex 323031392d30312d32392032303a35353a3336; asc 2019-01-29 20:55:36;;
 8: len 1; hex 31; asc 1;;
 9: len 8; hex 3230313930323238; asc 20190228;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7448 page no 6241 n bits 88 index `PRIMARY` of table `IotProductDB`.`production_log_t` trx id 60863913 lock_mode X waiting
Record lock, heap no 15 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
 0: len 8; hex 800000000003db53; asc        S;;
 1: len 6; hex 000003a0b5e1; asc       ;;
 2: len 7; hex 640000c01821ed; asc d    ! ;;
 3: len 16; hex 53494d50323031393130323132353838; asc SIMP201910212588;;
 4: SQL NULL;
 5: len 20; hex 3839383630323230313930353238303136313931; asc 89860220190528016191;;
 6: len 25; hex 53494d50323031393130323132353838343338393031323237; asc SIMP201910212588438901227;;
 7: len 20; hex 3839383532303030333633313036303539393836; asc 89852000363106059986;;
 8: len 15; hex 343534303036333130363035393938; asc 454006310605998;;
 9: len 1; hex 30; asc 0;;
 10: len 1; hex 31; asc 1;;
 11: len 4; hex 31303032; asc 1002;;
 12: len 3; hex 4e5454; asc NTT;;
 13: len 4; hex 38303035; asc 8005;;
 14: len 7; hex e5928ce8aeb033; asc       3;;
 15: len 12; hex 363446303030363446303130; asc 64F00064F010;;
 16: len 30; hex 3230323b3230343b3230363b3230383b3231323b3231333b3231343b3231; asc 202;204;206;208;212;213;214;21; (total 499 bytes);
 17: len 20; hex 3839383630323230313930353238303136313931; asc 89860220190528016191;;
 18: len 30; hex 393836383230303239313530383231303136313930383439343530303336; asc 986820029150821016190849450036; (total 134 bytes);
 19: len 20; hex 3938363832303032393135303832313031363139; asc 98682002915082101619;;
 20: len 8; hex 3442314534304343; asc 4B1E40CC;;
 21: SQL NULL;
 22: len 1; hex 32; asc 2;;
 23: len 19; hex 323031392d31302d32312031363a33333a3137; asc 2019-10-21 16:33:17;;
 24: len 19; hex 323031392d31302d32312031393a30313a3033; asc 2019-10-21 19:01:03;;
 25: len 19; hex 323031392d31302d32342031313a34373a3539; asc 2019-10-24 11:47:59;;
 26: len 4; hex 30303030; asc 0000;;
 27: len 7; hex 73756363657373; asc success;;
 28: SQL NULL;

*** WE ROLL BACK TRANSACTION (1)

第二次压测后获取的日志：

LATEST DETECTED DEADLOCK
------------------------
2019-10-24 17:19:49 7f00f773b700
*** (1) TRANSACTION:
TRANSACTION 60872312, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6822 lock struct(s), heap size 718376, 1154441 row lock(s), undo log entries 4
MySQL thread id 382928, OS thread handle 0x7f01e4c69700, query id 10489419 123.58.107.133 iottest updating
UPDATE simcard_production_task_t SET actual_quantity=actual_quantity+1 WHERE task_no=IN_TASKNO
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6713 page no 4 n bits 184 index `task_no` of table `IotProductDB`.`simcard_production_task_t` trx id 60872312 lock_mode X locks rec but not gap waiting
Record lock, heap no 111 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 16; hex 53494d50323031393130323132353838; asc SIMP201910212588;;
 1: len 8; hex 8000000000000346; asc        F;;

*** (2) TRANSACTION:
TRANSACTION 60872310, ACTIVE 5 sec fetching rows
mysql tables in use 1, locked 1
6355 lock struct(s), heap size 570920, 128099 row lock(s), undo log entries 1
MySQL thread id 382968, OS thread handle 0x7f00f773b700, query id 10489628 123.58.107.133 iottest Sending data
SELECT COUNT(*) INTO V_CVT FROM production_log_t WHERE task_no=IN_TASKNO AND (`status`='0' OR `status`='1') FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6713 page no 4 n bits 184 index `task_no` of table `IotProductDB`.`simcard_production_task_t` trx id 60872310 lock_mode X locks rec but not gap
Record lock, heap no 111 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 16; hex 53494d50323031393130323132353838; asc SIMP201910212588;;
 1: len 8; hex 8000000000000346; asc        F;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7448 page no 6253 n bits 80 index `PRIMARY` of table `IotProductDB`.`production_log_t` trx id 60872310 lock_mode X waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
 0: len 8; hex 800000000003db35; asc        5;;
 1: len 6; hex 000003a0d678; asc      x;;
 2: len 7; hex 3f000001770f1b; asc ?   w  ;;
 3: len 16; hex 53494d50323031393130323132353838; asc SIMP201910212588;;
 4: SQL NULL;
 5: len 20; hex 3839383630323230313930353238303135313938; asc 89860220190528015198;;
 6: len 25; hex 53494d50323031393130323132353838343338383938333632; asc SIMP201910212588438898362;;
 7: len 20; hex 3839383532303030333633313036303431323038; asc 89852000363106041208;;
 8: len 15; hex 343534303036333130363034313230; asc 454006310604120;;
 9: len 1; hex 30; asc 0;;
 10: len 1; hex 31; asc 1;;
 11: len 4; hex 31303032; asc 1002;;
 12: len 3; hex 4e5454; asc NTT;;
 13: len 4; hex 38303035; asc 8005;;
 14: len 7; hex e5928ce8aeb033; asc       3;;
 15: len 12; hex 363446303030363446303130; asc 64F00064F010;;
 16: len 30; hex 3230323b3230343b3230363b3230383b3231323b3231333b3231343b3231; asc 202;204;206;208;212;213;214;21; (total 499 bytes);
 17: len 20; hex 3839383630323230313930353238303135313938; asc 89860220190528015198;;
 18: len 30; hex 393836383230303239313530383231303135383930383439343530303336; asc 986820029150821015890849450036; (total 134 bytes);
 19: len 20; hex 3938363832303032393135303832313031353839; asc 98682002915082101589;;
 20: len 8; hex 4441424139303231; asc DABA9021;;
 21: SQL NULL;
 22: len 1; hex 32; asc 2;;
 23: len 19; hex 323031392d31302d32312031363a33333a3137; asc 2019-10-21 16:33:17;;
 24: len 19; hex 323031392d31302d32312031393a30303a3531; asc 2019-10-21 19:00:51;;
 25: len 19; hex 323031392d31302d32342031373a31393a3434; asc 2019-10-24 17:19:44;;
 26: len 4; hex 30303030; asc 0000;;
 27: len 7; hex 73756363657373; asc success;;
 28: SQL NULL;

*** WE ROLL BACK TRANSACTION (2)

其可以确定是这条语句出现死锁，但是出现顺序暂时没分析明白，明天再更新。

1	SELECT COUNT(*) INTO V_CVT FROM production_log_t WHERE task_no=IN_TASKNO AND (`status`='0' OR `status`='1') FOR UPDATE