【求助】mysql事务回滚的问题

[taishan1533]

建立两个表test1，test2,表test1的userid字段长度是8，表test2的userid字段长度是6
create table test1
(
idx int AUTO_INCREMENT,
userid varchar(8) not null,
primary key (idx)
)
ENGINE = InnoDB AUTO_INCREMENT=1;

create table test2
(
idx int AUTO_INCREMENT,
userid varchar(6) not null,
primary key (idx)
)
ENGINE = InnoDB AUTO_INCREMENT=1;

建立一个存储过程proc_test,原意是向两个表test1,test2分别插入记录，如果其中任何一个插入失败就全部回滚。
DELIMITER //
CREATE PROCEDURE proc_test
(IN i_userid VARCHAR(32))
begin
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 ;

使用存储过程，插入“test”
mysql> call proc_test('test');
+------+
| @ret |
+------+
| 0 |
+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select * from test1;
+-----+--------+
| idx | userid |
+-----+--------+
| 1 | test |
+-----+--------+
1 row in set (0.01 sec)

mysql> select * from test2;
+-----+--------+
| idx | userid |
+-----+--------+
| 1 | test |
+-----+--------+
1 row in set (0.00 sec)
查询两个表都是正常的，没有问题

使用存储过程，插入“test1234”
mysql> call proc_test('test1234');
ERROR 1265 (01000): Data truncated for column 'userid' at row 1
“test12345” 的长度超过了表test2中字段userid定义的长度，插入失败，查询结果如下
可是这样就没有达到我预想的结果，插入任何一个表失败后，所有的操作都要回滚
请各位帮忙看看哪里的问题，谢谢了。

mysql> select * from test1;
+-----+----------+
| idx | userid |
+-----+----------+
| 1 | test |
| 2 | test1234 |
+-----+----------+
2 rows in set (0.00 sec)

mysql> select * from test2;
+-----+--------+
| idx | userid |
+-----+--------+
| 1 | test |
+-----+--------+
1 row in set (0.00 sec)

[benluobo]

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 ;

[benluobo]

你的 insert into test2(userid) values(i_userid); if row_count() < 1 then 这里已经出错，后面是不会执行的，也就是不会走到rollback 你需要定义continue handle 处理可能出错的地方

[rabitsky]

给你一个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;    
    
END;

[rabitsky]

是这样，mysql默认是自动提交事务的。你如果想回滚整个事务，那就要显示设置一下，手动提交事务。 在事务开始之前，启用SET @@autocommit = 0;

[rabitsky]

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 ;

[taishan1533]

先谢谢楼上的朋友 SET @@autocommit = 0; 这一句我加上了 问题还是依旧