xml地图|网站地图|网站标签 [设为首页] [加入收藏]
MySql事务无法回滚的原因有哪些_Mysql_脚本之家,
分类:数据库

优化原则:小表驱动大表,即小的数据集驱动大的数据集。

BEGIN -- 声明变量 DECLARE v_addtime_begin varchar; DECLARE v_addtime_end varchar; DECLARE v_borrow_id int; DECLARE v_count int; DECLARE s1 int; /** 声明游标,并将查询结果存到游标中 **/ DECLARE c_borrow CURSOR FOR SELECT ID from rocky_borrow WHERE BORROWTYPE = 2 AND PUBLISH_TIME >= UNIX_TIMESTAMP AND PUBLISH_TIME <= UNIX_TIMESTAMP ORDER by ID ASC; /** 获取查询数量 **/ SELECT count INTO v_count from rocky_borrow WHERE BORROWTYPE = 2 AND PUBLISH_TIME >= UNIX_TIMESTAMP AND PUBLISH_TIME <= UNIX_TIMESTAMP ORDER by ID ASC; SET s1 = 1; -- 开始事务 START TRANSACTION; -- 打开游标 OPEN c_borrow; -- 循环游标 WHILE s1 < v_count+1 DO -- 遍历游标 FETCH c_borrow INTO v_borrow_id; SELECT t1.addtime INTO v_addtime_begin FROM (SELECT * FROM rocky_b_tenderrecord bt WHERE BORROW_ID = v_borrow_id AND tender_type = 1 ORDER BY ID ASC) t1 GROUP BY t1.borrow_id; SELECT t1.addtime INTO v_addtime_end FROM (SELECT * FROM rocky_b_tenderrecord bt WHERE BORROW_ID = v_borrow_id AND tender_type = 1 ORDER BY ID DESC) t1 GROUP BY t1.borrow_id; IF (v_addtime_begin IS NOT NULL) && (v_addtime_end IS NOT NULL) THEN -- 嵌套使用游标 BEGIN DECLARE v_id int; DECLARE v_user_id int; DECLARE v_type varchar; DECLARE v_total decimal DEFAULT 0; DECLARE v_money decimal DEFAULT 0; DECLARE v_use_money decimal DEFAULT 0; DECLARE v_no_use_money decimal DEFAULT 0; DECLARE v_collection decimal DEFAULT 0; DECLARE v_to_user int; DECLARE v_remark VARCHAR; DECLARE v_addtime varchar; DECLARE v_addip varchar; DECLARE v_first_borrow_use_money decimal DEFAULT 0; DECLARE done VARCHAR DEFAULT ''; DECLARE t_error int DEFAULT 0; DECLARE c_accountlog CURSOR FOR SELECT ID,USER_ID,TYPE,TOTAL,MONEY,USE_MONEY,NO_USE_MONEY,COLLECTION,TO_USER,REMARK,ADDTIME,ADDIP,FIRST_BORROW_USE_MONEY FROM ( SELECT ID,USER_ID,TYPE,TOTAL,MONEY,USE_MONEY,NO_USE_MONEY,COLLECTION,TO_USER,REMARK,ADDTIME,ADDIP,FIRST_BORROW_USE_MONEY FROM rocky_accountlog WHERE ADDTIME >= v_addtime_begin AND ADDTIME <= v_addtime_end AND (type = 'tender_cold' or type= 'repayment_deduct') ) t GROUP BY t.user_id HAVING count > 1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = NULL; OPEN c_accountlog; FETCH c_accountlog INTO v_id,v_user_id,v_type,v_total,v_money,v_use_money,v_no_use_money,v_collection,v_to_user,v_remark,v_addtime,v_addip,v_first_borrow_use_money; WHILE  DO INSERT INTO rocky_accountlog_test2 (ACCOUNTLOG_ID,USER_ID,TYPE,TOTAL,MONEY,USE_MONEY,NO_USE_MONEY,COLLECTION,TO_USER,REMARK,ADDTIME,ADDIP,FIRST_BORROW_USE_MONEY,BORROW_ID) VALUES (v_id,v_user_id,v_type,v_total,v_money,v_use_money,v_no_use_money,v_collection,v_to_user,v_remark,v_addtime,v_addip,v_first_borrow_use_money,v_borrow_id); FETCH c_accountlog INTO v_id,v_user_id,v_type,v_total,v_money,v_use_money,v_no_use_money,v_collection,v_to_user,v_remark,v_addtime,v_addip,v_first_borrow_use_money; END WHILE; CLOSE c_accountlog; END; END IF; SET s1 = s1 + 1; END WHILE; CLOSE c_borrow; COMMIT; -- 事务提交 END

使用MySQL时,如果发现事务无法回滚,但Hibernate、Spring、JDBC等配置又没有明显问题时,不要苦恼,先看看MySQL创建的表有没有问题,即表的类型。

############# 原理 #####################

InnoDB和MyISAM是在使用MySQL最常用的两个表类型,各有优缺点,视具体应用而定。基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持已经外部键等高级数据库功能。

select * from A where id in 等价于:for select id from Bfor select * from A where A.id = B.id

MyIASM是IASM表的新版本,有如下扩展:

当B表的数据集必须小于A表的数据集时,用in优于exists。

二进制层次的可移植性。

select * from A where exists (select 1 from B where B.id = A.id)等价于for select * from Afor select * from B where B.id = A.id

对变长行比ISAM表有更少的碎片。

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:MySql事务无法回滚的原因有哪些_Mysql_脚本之家,

上一篇:MySQL: LEAVE Statement 下一篇:update语句的用法详解_Mysql_脚本之家,daemon错误的
猜你喜欢
热门排行
精彩图文