mysql存储过程出现锁表锁行的情况怎么解决?如:

如下是 mysql 的错误信息:

Deadlock found when trying to get lock; try restarting transaction

以下是相关的两个mysql存储过程代码:

1:

REATE DEFINER=`ab`@`%` PROCEDURE `pay_created_orders`(IN `_id` VARCHAR(32), IN `_goods_id` VARCHAR(32), IN `_uid` BIGINT(20), IN `_platform` VARCHAR(10), IN `_money` FLOAT, IN `_discount_id` BIGINT(20), IN `_target_uid` BIGINT(20), IN `_live_id` BIGINT(10), IN `_count` INT(10))
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
BEGIN
    DECLARE id BIGINT DEFAULT 0;
    DECLARE t_err SMALLINT DEFAULT 0;
    DECLARE goods_count INT DEFAULT 0;
    DECLARE var_goods_type SMALLINT DEFAULT 0;
    DECLARE var_top_card_sums INT DEFAULT 0;
    DECLARE var_count SMALLINT DEFAULT 0;
    DECLARE var_date VARCHAR(10) DEFAULT '';

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_err=-1;

    START TRANSACTION;

    SET var_goods_type = (SELECT `ops` FROM `goods` WHERE `goods_id`=`_goods_id` LIMIT 1);

    SET var_date = FROM_UNIXTIME(UNIX_TIMESTAMP(CURDATE()), '%Y%m%d');
    IF var_goods_type = 1 THEN
      SET var_top_card_sums = (SELECT topcard_sum AS var_top_card_sums FROM users_topcard WHERE  `uid`=`_target_uid` AND `date`= var_date FOR UPDATE);

      IF var_top_card_sums >= 10 THEN
        SET t_err = -1;
        SET id = -3;
      END IF;

    END IF;


    SET var_count = (SELECT COUNT(`uid`) AS var_count FROM `users_ios_sandbox` WHERE `uid`=`_uid`);

    IF var_count > 0 THEN
      SET `_platform` = 'iossandbox';
    END IF;

    SET goods_count = (SELECT `count` FROM goods WHERE `goods_id`=`_goods_id` LIMIT 1 FOR UPDATE);
    INSERT INTO `users_orders`(
      `id`, `uid`, `goods_id`, `platform`, `beans`, `discount_id`,
      `pay_time`, `datetime`, `status`, `target_uid`, `live_id`,
      `count`
    ) VALUES (
      `_id`, `_uid`, `_goods_id`, `_platform`, `_money`, `_discount_id`,
             0, UNIX_TIMESTAMP(), 0, `_target_uid`, `_live_id`,
      `_count`
    );

    IF goods_count > 0 THEN

      UPDATE `goods` SET
        `count`=`count`-1
      WHERE `goods_id`=`_goods_id`;

    END IF;

    IF goods_count = 0 OR goods_count < -1 THEN
      SET id=-2;
      SET t_err = -1;
    END IF;

    IF t_err = -1 THEN
      ROLLBACK;
    ELSE
      COMMIT;
    END IF;

    SELECT `id` AS `id`;
  END;

2:


CREATE DEFINER=`ab`@`%` PROCEDURE `pay_modified_orders`(
  IN `_id` VARCHAR(32),
  IN `_status` INT(10),
  IN `_consumes_id` VARCHAR(32) CHARSET UTF8
)
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
BEGIN
    DECLARE gid BIGINT DEFAULT 0;
    DECLARE g_count INT DEFAULT 0;
    DECLARE t_err SMALLINT DEFAULT 0;
    DECLARE var_goods_id INT DEFAULT 0;
    DECLARE var_goods_beans DOUBLE DEFAULT 0;
    DECLARE var_goods_ratio DOUBLE DEFAULT 100;
    DECLARE var_after_beans DOUBLE DEFAULT 0;
    DECLARE var_uid BIGINT DEFAULT 0;
    DECLARE var_target_uid BIGINT DEFAULT 0;
    DECLARE var_live_id  INT DEFAULT 0;
    DECLARE var_goods_count INT DEFAULT 0;
    DECLARE var_expenses_id BIGINT DEFAULT 0;
    DECLARE var_top_card_sums SMALLINT DEFAULT 0;
    DECLARE var_date VARCHAR(10) DEFAULT '';
    DECLARE var_goods_type SMALLINT DEFAULT 0;

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_err=-1;

    START TRANSACTION;

    SELECT COUNT(id) AS g_count,goods_id,beans,uid,target_uid,live_id,count INTO g_count, var_goods_id, var_goods_beans, var_uid, var_target_uid, var_live_id, var_goods_count
    FROM `users_orders`
    WHERE `id`=`_id` LIMIT 1 FOR UPDATE;
    #SET g_count=(SELECT count(*) FROM `users_orders` WHERE `id`=`_id` LIMIT 1 FOR UPDATE);

    IF g_count <> 1 THEN
      SET t_err=-1;
    END IF;
    SET var_date = FROM_UNIXTIME(UNIX_TIMESTAMP(CURDATE()), '%Y%m%d');

  SET var_goods_type = (SELECT `ops` FROM `goods` WHERE `goods_id`=`var_goods_id` LIMIT 1);

    IF `_status` = 1 THEN
      SET var_expenses_id = (SELECT expenses_id FROM users_expenses WHERE order_id=`_id` LIMIT 1);
      IF var_expenses_id <= 1 OR ISNULL(var_expenses_id) THEN

        SET var_goods_ratio = (SELECT ratio FROM `goods_ratio` WHERE `goods_id`=`var_goods_id` AND `start_time` <= UNIX_TIMESTAMP() AND `end_time` >= UNIX_TIMESTAMP()  LIMIT 1);
        IF ISNULL(var_goods_ratio) OR var_goods_ratio<=0 THEN
          SET var_goods_ratio = 100;
        END IF;
        SET var_after_beans = var_goods_beans * (var_goods_ratio / 100);

        INSERT INTO `users_expenses`(
          `uid`, `target_uid`, `live_id`, `order_id`, `consume_id`,
          `goods_id`, `beans`,  `after_beans`, `goods_count`,
          `timestamp`, `type`, `extra`) VALUE (
          `var_uid`, `var_target_uid`, `var_live_id`, `_id`, `_consumes_id`,
                     `var_goods_id`, `var_goods_beans`, `var_after_beans`, `var_goods_count`,
                     UNIX_TIMESTAMP(), 1, ''
        );

        IF var_goods_type = 1 THEN 
          SET var_top_card_sums = (SELECT topcard_sum AS var_top_card_sums FROM users_topcard WHERE  `uid`=`var_target_uid` AND `date`= var_date FOR UPDATE);
  
          IF var_top_card_sums >= 10 THEN 
            SET t_err = -1;
          END IF;
          IF var_top_card_sums = 0 OR ISNULL(var_top_card_sums) THEN
            INSERT INTO users_topcard (
              `uid`, `date`, `topcard_sum`, `last_time`)
            VALUES (
              `var_target_uid`, var_date, 1, UNIX_TIMESTAMP());
          ELSEIF var_top_card_sums > 0 AND var_top_card_sums <10 THEN
            UPDATE users_topcard SET topcard_sum=topcard_sum+1,last_time=UNIX_TIMESTAMP() WHERE uid=`var_target_uid`  AND `date`=var_date;
          END IF;
        END IF;
      END IF;
    END IF;

    UPDATE `users_orders` SET
      `pay_time`=UNIX_TIMESTAMP(),
      `status`=`_status`,
      `consumes_id`=`_consumes_id`
    WHERE `id`=`_id`;

    IF t_err = -1 THEN
      ROLLBACK;
      SET gid = -1;
    ELSE
      COMMIT;
      SET gid = `_id`;
    END IF;

    SELECT `gid` AS `id`;
  END;
查看回复