如下是
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;