DELIMITER // CREATE PROCEDURE proc_test (IN i_userid VARCHAR(32)) begin SET @@autocommit = 0; // 添加这一句,设置为手工提交、回滚事务 loop_label: loop start transaction; insert into test1(userid) values(i_userid); if row_count() < 1 then set @ret = -1; rollback; leave loop_label; end if; insert into test2(userid) values(i_userid); if row_count() < 1 then set @ret = -1; rollback; leave loop_label; else set @ret = 0; commit; leave loop_label; end if; end loop; select @ret; end // DELIMITER ;
先谢谢楼上的朋友 SET @@autocommit = 0; 这一句我加上了 问题还是依旧
DELIMITER // CREATE PROCEDURE proc_test (IN i_userid VARCHAR(32)) begin declare out_status varchar(10); DECLARE CONTINUE HANDLER FOR 1265 SET out_status='Truncated'; loop_label: loop start transaction; insert into test1(userid) values(i_userid); if out_status = 'Duplicate' then rollback; leave loop_label; end if; insert into test2(userid) values(i_userid); if out_status = 'Duplicate' then set @ret = -1; rollback; leave loop_label; else set @ret = 0; commit; leave loop_label; end if; end loop; select @ret; end // DELIMITER ;
你的 insert into test2(userid) values(i_userid); if row_count() < 1 then 这里已经出错,后面是不会执行的,也就是不会走到rollback你需要定义continue handle 处理可能出错的地方
给你一个MSQL的例子吧,很久以前写的,能够正常运行,你参考一下DROP PROCEDURE IF EXISTS chenwei.pr_trans_money; CREATE PROCEDURE chenwei.pr_trans_money ( IN iFrom VARCHAR(50), -- 转账账户 IN iTo VARCHAR(50), -- 转入账户 IN iMoney DECIMAL, -- 转账金额 OUT error_code INT -- 错误代码 ) SQL SECURITY INVOKER label_flag: BEGIN DECLARE vCount INT; DECLARE vState INT;
SET @@autocommit = 0;
-- 如果转账账户为空 IF ( iFrom IS NULL ) THEN SET error_code = 1000; LEAVE label_flag; END IF;
-- 如果转账账户不存在 IF NOT EXISTS ( SELECT * FROM tb_user_act WHERE userid = iFrom ) THEN SET error_code = 1001; LEAVE label_flag; END IF;
SELECT act_state INTO vState FROM tb_user_act WHERE userid = iFrom;
-- 如果转账账户已冻结 IF ( vState = 9 ) THEN SET error_code = 1002; LEAVE label_flag; END IF;
-- 如果转账账户余额不足 IF NOT EXISTS ( SELECT * FROM tb_user_act WHERE userid = iFrom AND balance >= iMoney ) THEN SET error_code = 1003; LEAVE label_flag; END IF;
-- 如果转入账户为空 IF ( iTo IS NULL ) THEN SET error_code = 2000; LEAVE label_flag; END IF;
-- 如果转入账户不存在 IF NOT EXISTS ( SELECT * FROM tb_user_act WHERE userid = iTo ) THEN SET error_code = 2001; LEAVE label_flag; END IF; SELECT act_state INTO vState FROM tb_user_act WHERE userid = iTo;
-- 如果转入账户已冻结 IF ( vState = 9 ) THEN SET error_code = 2002; LEAVE label_flag; END IF;
-- 如果转账金额不正确 IF ( iMoney <=0 ) THEN SET error_code = 1004; LEAVE label_flag; END IF;
-- 如果通过各项验证,下面开始转账 START TRANSACTION;
SAVEPOINT point1;
-- 从转账账户扣钱 UPDATE tb_user_act SET balance = balance - iMoney WHERE userid = iFrom;
-- 如果转账账户扣钱成功 IF ( ROW_COUNT() = 1 ) THEN -- 向转入账户加钱 UPDATE tb_user_act SET balance = balance + iMoney WHERE userid = iTo;
IF ( ROW_COUNT() = 1 ) THEN COMMIT; SET error_code = 0; ELSE ROLLBACK TO SAVEPOINT point1; SET error_code = 9999; END IF; ELSE SET error_code = 9999; END IF;
在事务开始之前,启用SET @@autocommit = 0;
DELIMITER //
CREATE PROCEDURE proc_test
(IN i_userid VARCHAR(32))
begin
SET @@autocommit = 0; // 添加这一句,设置为手工提交、回滚事务
loop_label: loop
start transaction;
insert into test1(userid) values(i_userid);
if row_count() < 1 then
set @ret = -1;
rollback;
leave loop_label;
end if;
insert into test2(userid) values(i_userid);
if row_count() < 1 then
set @ret = -1;
rollback;
leave loop_label;
else
set @ret = 0;
commit;
leave loop_label;
end if;
end loop;
select @ret;
end
//
DELIMITER ;
SET @@autocommit = 0;
这一句我加上了
问题还是依旧
CREATE PROCEDURE proc_test
(IN i_userid VARCHAR(32))
begin
declare out_status varchar(10);
DECLARE CONTINUE HANDLER FOR 1265 SET out_status='Truncated';
loop_label: loop
start transaction;
insert into test1(userid) values(i_userid);
if out_status = 'Duplicate' then
rollback;
leave loop_label;
end if;
insert into test2(userid) values(i_userid);
if out_status = 'Duplicate' then
set @ret = -1;
rollback;
leave loop_label;
else
set @ret = 0;
commit;
leave loop_label;
end if;
end loop;
select @ret;
end
//
DELIMITER ;
CREATE PROCEDURE chenwei.pr_trans_money
( IN iFrom VARCHAR(50), -- 转账账户
IN iTo VARCHAR(50), -- 转入账户
IN iMoney DECIMAL, -- 转账金额
OUT error_code INT -- 错误代码
)
SQL SECURITY INVOKER
label_flag:
BEGIN
DECLARE vCount INT;
DECLARE vState INT;
SET @@autocommit = 0;
-- 如果转账账户为空
IF ( iFrom IS NULL ) THEN
SET error_code = 1000;
LEAVE label_flag;
END IF;
-- 如果转账账户不存在
IF NOT EXISTS ( SELECT * FROM tb_user_act WHERE userid = iFrom ) THEN
SET error_code = 1001;
LEAVE label_flag;
END IF;
SELECT act_state
INTO vState
FROM tb_user_act
WHERE userid = iFrom;
-- 如果转账账户已冻结
IF ( vState = 9 ) THEN
SET error_code = 1002;
LEAVE label_flag;
END IF;
-- 如果转账账户余额不足
IF NOT EXISTS ( SELECT * FROM tb_user_act WHERE userid = iFrom AND balance >= iMoney ) THEN
SET error_code = 1003;
LEAVE label_flag;
END IF;
-- 如果转入账户为空
IF ( iTo IS NULL ) THEN
SET error_code = 2000;
LEAVE label_flag;
END IF;
-- 如果转入账户不存在
IF NOT EXISTS ( SELECT * FROM tb_user_act WHERE userid = iTo ) THEN
SET error_code = 2001;
LEAVE label_flag;
END IF;
SELECT act_state
INTO vState
FROM tb_user_act
WHERE userid = iTo;
-- 如果转入账户已冻结
IF ( vState = 9 ) THEN
SET error_code = 2002;
LEAVE label_flag;
END IF;
-- 如果转账金额不正确
IF ( iMoney <=0 ) THEN
SET error_code = 1004;
LEAVE label_flag;
END IF;
-- 如果通过各项验证,下面开始转账
START TRANSACTION;
SAVEPOINT point1;
-- 从转账账户扣钱
UPDATE tb_user_act
SET balance = balance - iMoney
WHERE userid = iFrom;
-- 如果转账账户扣钱成功
IF ( ROW_COUNT() = 1 ) THEN
-- 向转入账户加钱
UPDATE tb_user_act
SET balance = balance + iMoney
WHERE userid = iTo;
IF ( ROW_COUNT() = 1 ) THEN
COMMIT;
SET error_code = 0;
ELSE
ROLLBACK TO SAVEPOINT point1;
SET error_code = 9999;
END IF;
ELSE
SET error_code = 9999;
END IF;
END;