表什么引擎,INNODB才支持事务
select @@autocommit;
如果SET AUTOCOMMIT=0;也就是关闭了自动提交,那么任何commit或rallback语句都可以触发事务提交。
select @@autocommit;
如果SET AUTOCOMMIT=0;也就是关闭了自动提交,那么任何commit或rallback语句都可以触发事务提交。
解决方案 »
- 多个数据库的问题
- mysql去除重复record
- 新手急问:用VC+C API 怎么程序里动态更改MYSQL数据文件的路径啊?
- 为什么服务器上面Innodb_buffer_pool_read_ahead_evicted这个会比Innodb_buffer_pool_read_ahead大
- 【请高手支招】mysql 触发器读取多表更新 问题??
- 关于MYSQL效率的问题,100分诚恳赐教!!
- mysql错误“multiple-step operation generated error.....”究竟能不能解决啊
- linux mysql备份问题
- 高手们请进来看看,mysql见表
- SQL查询,跪求高人指点~~~~~
- 写项目遇到一个sql不会了,求教下
- sql触发器
隔离事务级别
START TRANSACTION; 的作用不是和 SET AUTOCOMMIT=0; 相同吗?
我又做了一个实验,假设students表中包含
+-----+-------------+-------+
| uid | studentname | grade |
+-----+-------------+-------+
| 1 | xiao ming | 1 |
| 100 | xiao jun | 1 |此时新创建一个过程调用DELIMITER //
CREATE PROCEDURE addstudent2()
BEGIN
START TRANSACTION;
INSERT INTO students(studentname) VALUES("xiao ming");
INSERT INTO students(uid, studentname) VALUES(100, "xiao jun");
ROLLBACK;
END//
DELIMITER ; 然后执行CALL addstudent2();
SELECT * FROM students;发现表中又插入了“xiao ming”的行,ROLLBACK好像没起作用,搞得头都大了!
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.08 sec)
INSERT INTO students(studentname) VALUES("xiao ming");
INSERT INTO students(uid, studentname) VALUES(100, "xiao jun");
ROLLBACK;
SELECT * FROM students;表中并没有插入行,但是执行CALL addstudent2();就会插入“xiao ming”的行
mysql> SELECT * FROM students;
+-----+-------------+-------+
| uid | studentname | grade |
+-----+-------------+-------+
| 1 | xiao ming | 1 |
| 100 | xiao jun | 1 |
+-----+-------------+-------+
2 rows in set (0.00 sec)mysql> CALL addstudent2();
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'
mysql>
mysql> SELECT * FROM students;
+-----+-------------+-------+
| uid | studentname | grade |
+-----+-------------+-------+
| 1 | xiao ming | 1 |
| 100 | xiao jun | 1 |
| 132 | xiao ming | 1 |
+-----+-------------+-------+
3 rows in set (0.00 sec)mysql>
mysql>
mysql>
mysql>
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO students(studentname) VALUES("xiao ming");
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO students(uid, studentname) VALUES(100, "xiao jun");
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'
mysql> ROLLBACK;
Query OK, 0 rows affected (0.03 sec)mysql> SELECT * FROM students;
+-----+-------------+-------+
| uid | studentname | grade |
+-----+-------------+-------+
| 1 | xiao ming | 1 |
| 100 | xiao jun | 1 |
| 132 | xiao ming | 1 |
+-----+-------------+-------+
3 rows in set (0.00 sec)mysql>
mysql>
mysql>
mysql>
mysql>
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO students(studentname) VALUES("xiao ming");
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO students(uid, studentname) VALUES(100, "xiao jun");
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM students;
+-----+-------------+-------+
| uid | studentname | grade |
+-----+-------------+-------+
| 1 | xiao ming | 1 |
| 100 | xiao jun | 1 |
| 132 | xiao ming | 1 |
+-----+-------------+-------+
3 rows in set (0.00 sec)mysql>
mysql>
CREATE PROCEDURE addstudent2()
BEGIN
START TRANSACTION;
INSERT INTO students(studentname) VALUES("xiao ming");
INSERT INTO students(uid, studentname) VALUES(100, "xiao jun");
ROLLBACK;
END//
DELIMITER ;
+-----------+
| version() |
+-----------+
| 5.6.15 |
+-----------+
1 row in set (0.00 sec)
CREATE PROCEDURE addstudent()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;
INSERT INTO students(studentname) VALUES("xiao ming");
INSERT INTO students(uid, studentname) VALUES(100, "xiao jun");
SELECT * FROM students;
COMMIT;
END//
DELIMITER ;
CREATE PROCEDURE addstudent()
BEGIN
DECLARE txn_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET txn_error=1; START TRANSACTION;
INSERT INTO students(studentname) VALUES("xiao ming");
INSERT INTO students(uid, studentname) VALUES(100, "xiao jun");
SELECT * FROM students;
IF txn_error=1 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END//
DELIMITER ;mysql一直提示ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' COMMIT;
ELSE
ROLLBACK;
END IF;
END' at line 12刚学mysql, google也没收到结果,望版主解答啊,谢谢啦!
DROP PROCEDURE IF EXISTS addstudent//
CREATE PROCEDURE addstudent()
BEGIN
DECLARE txn_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET txn_error=1;
START TRANSACTION;
INSERT INTO students(studentname) VALUES("xiao ming");
INSERT INTO students(uid, studentname) VALUES(100, "xiao jun");
SELECT * FROM students;
IF txn_error=1 THEN COMMIT;
ELSE ROLLBACK;
END IF;
END //
DELIMITER ;
在5.5下测试通过
ROLLBACK;
ELSE
COMMIT;
END IF;需要写成IF txn_error=1 THEN ROLLBACK;
ELSE COMMIT;
END IF;错误的原因终于搞清楚了,代码START TRANSACTION;
INSERT INTO students(studentname) VALUES("xiao ming");
INSERT INTO students(uid, studentname) VALUES(100, "xiao jun");
ROLLBACK;
SELECT * FROM students;如果发生异常,ROLLBACK根本就没有执行,所以“xiao ming”被插入数据库。再次感谢 WWWWA