mysql 存储过程 锁_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;