mysql存储过程中事务操作

转自：http://hi.baidu.com/yangjie312/blog/item/ef67d9f9e6daa810a8d311d1.html

案例一

1. mysql> DELIMITER $$
2. mysql> DROP PROCEDURE IF EXISTS `transaction1`$$
3. Query OK, 0 rows affected (0.00 sec)
4.
5. mysql> CREATE PROCEDURE transaction1()
6. -> BEGIN
7. -> set autocommit = 0;
8. -> insert IGNORE into t3 (id) values (1);
9. -> insert IGNORE into t3 (idrr) values (2);
10. -> if @@warning_count <> 0 then
11. -> rollback;
12. -> else
13. -> commit;
14. -> end if;
15. -> END$$
16. Query OK, 0 rows affected (0.00 sec)
17.
18. mysql> DELIMITER ;
19. mysql> call transaction1();
20. Query OK, 0 rows affected, 1 warning (0.06 sec)
21.
22. mysql> select * from t3;
23. Empty set (0.00 sec)
24.
25. mysql>

案例二

1. mysql> DELIMITER $$
2. mysql> DROP PROCEDURE IF EXISTS `transaction1`$$
3. Query OK, 0 rows affected (0.00 sec)
4.
5. mysql> CREATE PROCEDURE transaction1()
6. -> BEGIN
7. -> set autocommit = 0;
8. -> insert IGNORE into t3 (id) values (1);
9. -> insert IGNORE into t3 (idrr) values (2);
10. -> if @@warning_count <> 0 ||@@error_count>0 then
11. -> rollback;
12. -> else
13. -> commit;
14. -> end if;
15. -> END$$
16. Query OK, 0 rows affected (0.00 sec)
17.
18. mysql> DELIMITER ;
19. mysql> call transaction1();
20. Query OK, 0 rows affected, 1 warning (0.06 sec)
21.
22. mysql> select * from t3;
23. Empty set (0.00 sec)
24.
25. mysql>

要使mysql支持事务,表类型要用: InnoDB


1、 隔离级别


SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED |REPEATABLE READ | SERIALIZABLE}

默认为:REPEATABLE READ

在SQL99中，为了实现事务的完全隔离，定义了三种必须避免的现象：


1、读"脏"数据（dirty read）：即事务在运行中读到了其它事务未提交的数据。
2、不可重复读（unrepeatable read）：即事务在运行中再次读取同一数据时，可发现其它事务的更新。
3、"幻象"读（phantom read）：即事务在运行中再次执行同一查询时，发现其它事务的更新。

只有避免上面三种现象，才能实现真正意义上的隔离，或者称是可串行的。但是，此时事务之间因为冲突而等待的机会非常高，系统的性能可能难以达到预期的目标。 为了实现隔离性与系统性能之间的平衡，SQL99定义了四种隔离级别， 允许应用根据实际需要选择合适的隔离级别 馑闹指衾爰侗鸬暮 迦缦拢?nbsp;

读"脏"数据 不可重复读 "幻象"读
未提交读READ UNCOMMITTED N N N
提交读READ COMMITTED Y N N
可重复读REPEATABLE READ Y Y N
可串行SERIALIZABLE Y Y Y

其中"N"表示在这种隔离级别下，对应的现象不可避免，而"Y"则 表示可以避免。

2、 事务语句


START TRANSACTION

开始一个事务,如果已存在一个事务,会先立即commit.另外,autocommit属性会被设为0,直到事务结束

COMMIT

提交所有数据,释放锁

ROLLBACK

回滚事务

SAVEPOINT savepoint_name

设点存盘点

ROLLBACK TO SAVEPOINT savepoint_name

回滚至savepoint_name

SET TRANSACTION

设置隔离级别(SET TRANSACTION ISOLATION LEVEL)

LOCK TABLES

锁定表,此操作会导致当前打开的事务立即开关闭,所以应在此之前,执行commit或rollback


3、 定义事务

要开启事务处理，有如下两个方法:
a、 将autocommit设为0
b、 使用START TRANACTION语句(建议使用)

注：以下语句会导致即时commit当前事务
ALTER FUNCTION ALTER PROCEDURE ALTER TABLE
BEGIN CREATE DATABASE CREATE FUNCTION
CREATE INDEX CREATE PROCEDURE CREATE TABLE
DROP DATABASE DROP FUNCTION DROP INDEX
DROP PROCEDURE DROP TABLE UNLOCK TABLES
LOAD MASTER DATA LOCK TABLES RENAME TABLE
trUNCATE TABLE SET AUTOCOMMIT=1 START TRANSACTION