MYSQL 死锁问题

twayne
  • 51
CREATE TABLE `t_credit_record_summary` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `credit_record_summary_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '积分汇总ID',
  `user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户ID',
  `credit_rule_code` varchar(32) NOT NULL DEFAULT '' COMMENT '规则编码',
  `sum_credit` int(11) NOT NULL DEFAULT '0' COMMENT '积分合计',
  `sum_count` int(11) NOT NULL DEFAULT '0' COMMENT '次数合计',
  `date` int(8) NOT NULL DEFAULT '0' COMMENT '日期',
  PRIMARY KEY (`id`),
  UNIQUE KEY `credit_record_summary_id_UNIQUE` (`credit_record_summary_id`),
  UNIQUE KEY `user_code_date_UNIQUE` (`user_id`,`credit_rule_code`,`date`)
) ENGINE=InnoDB AUTO_INCREMENT=194 DEFAULT CHARSET=utf8;



DELIMITER $$
CREATE DEFINER=`admin`@`%` PROCEDURE `p_credit_record_summary_insert`(IN `v_credit_record_summary_id` BIGINT(20), IN `v_user_id` BIGINT(20), IN `v_credit_rule_code` VARCHAR(32), IN `v_credit` INT(11), IN `v_date` INT(8))
BEGIN
    DECLARE v_sum_count INT DEFAULT 0;
    DECLARE v_sum_credit INT DEFAULT 0;

    SELECT s.sum_count, s.sum_credit INTO v_sum_count, v_sum_credit FROM t_credit_record_summary AS s WHERE s.user_id = v_user_id AND s.credit_rule_code = v_credit_rule_code AND s.`date` = v_date FOR UPDATE;
    IF v_sum_count = 0 THEN
        #Routine body goes here...
        INSERT INTO t_credit_record_summary (credit_record_summary_id, user_id, credit_rule_code, sum_credit, sum_count, `date`) VALUES (v_credit_record_summary_id, v_user_id, v_credit_rule_code, v_credit, 1, v_date);
    ELSE
        SET v_sum_count = v_sum_count + 1;
        SET v_sum_credit = v_sum_credit + v_credit;
        UPDATE t_credit_record_summary AS s SET s.sum_count = v_sum_count, s.sum_credit = v_sum_credit WHERE s.user_id = v_user_id AND s.credit_rule_code = v_credit_rule_code AND s.`date` = v_date;
    END IF;
END$$
DELIMITER ;

在有并发的情况下调用p_credit_record_summary_insert,出现死锁:Deadlock found when trying to get lock; try restarting transaction Query:
如何解决?

回复
阅读 1.8k
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
宣传栏