MYSQL事务处理之学习笔记

事务处理是mysql中一个非常好功能,如在删除大量数据时如果不使用事务处理可能有些删除失败导致数据不完整,如果使用事务处理就不会出现此类问题,下面我们一起来看看mysql事务处理用法.

mysql事务处理的几个步骤:

1.关闭自动提交

2.开启事务处理

3.有异常就自动抛出异常提示再回滚

4.开启自动提交

注意:mysql只有这个InnoDB驱动是支持事务处理的,默认MyIsAM驱动不支持,由于项目设计里面,牵扯到了金钱的转移,于是就要用到MYSQL的事务处理,来保证一组处理结果的正确性,用了事务,就不可避免的要牺牲一部分速度,来保证数据的正确性.

只有InnoDB支持事务

事务 ACID Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

1、事务的原子性

一组事务,要么成功,要么撤回.

2、稳定性

有非法数据(外键约束之类),事务撤回.

3、隔离性

事务独立运行,一个事务处理后的结果,影响了其他事务,那么其他事务会撤回,事务的100%隔离,需要牺牲速度.

4、可靠性

软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改,可靠性和高速度不可兼得,innodb_flush_log_at_trx_commit选项,决定什么时候吧事务保存到日志里.

开启事务:START TRANSACTION 或 BEGIN

提交事务(关闭事务):COMMIT

放弃事务(关闭事务):ROLLBACK

折返点:SAVEPOINT adqoo_1,ROLLBACK TO SAVEPOINT adqoo_1

发生在折返点 adqoo_1 之前的事务被提交,之后的被忽略.

事务的终止

设置“自动提交”模式:SET AUTOCOMMIT = 0

每条SQL都是同一个事务的不同命令,之间由 COMMIT 或 ROLLBACK隔开,掉线后,没有 COMMIT 的事务都被放弃.

事务锁定模式

系统默认:不需要等待某事务结束,可直接查询到结果,但不能再进行修改、删除.

缺点:查询到的结果,可能是已经过期的.

优点:不需要等待某事务结束,可直接查询到结果.

需要用以下模式来设定锁定模式

1、SELECT …… LOCK IN SHARE MODE(共享锁)

查询到的数据,就是数据库在这一时刻的数据(其他已commit事务的结果,已经反应到这里了.

SELECT 必须等待，某个事务结束后才能执行

2、SELECT …… FOR UPDATE（排它锁）

例如 SELECT * FROM tablename WHERE id<200

那么id<200的数据，被查询到的数据，都将不能再进行修改、删除、SELECT …… LOCK IN SHARE MODE操作一直到此事务结束

共享锁 和 排它锁的区别:在于是否阻断其他客户发出的 SELECT …… LOCK IN SHARE MODE命令

3、INSERT / UPDATE / DELETE

所有关联数据都会被锁定，加上排它锁

4、防插入锁

例如 SELECT * FROM tablename WHERE id>200,那么id>200的记录无法被插入.

5、死锁

自动识别死锁,先进来的进程被执行,后来的进程收到出错消息,并按ROLLBACK方式回滚:

innodb_lock_wait_timeout = n 来设置最长等待时间，默认是50秒

事务隔离模式:

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL

READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE

1、不带SESSION、GLOBAL的SET命令,只对下一个事务有效.

2、SET SESSION,为当前会话设置隔离模式

3、SET GLOBAL,为以后新建的所有MYSQL连接设置隔离模式(当前连接不包括在内)

隔离模式:READ UNCOMMITTED

不隔离SELECT:其他事务未完成的修改(未COMMIT),其结果也考虑在内.

READ COMMITTED:把其他事务的 COMMIT 修改考虑在内,同一个事务中,同一 SELECT 可能返回不同结果.

REPEATABLE READ（默认）

不把其他事务的修改考虑在内,无论其他事务是否用COMMIT命令提交过同一个事务中,同一 SELECT 返回同一结果(前提是本事务)不修改.

SERIALIZABLE:和REPEATABLE READ类似,给所有的SELECT都加上了共享锁.

出错处理:根据出错信息,执行相应的处理,事务处理在各种管理系统中都有着广泛的应用,比如人员管理系统,很多同步数据库操作大都需要用到事务处理,比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务！

删除的SQL语句,代码如下:

1. delete from userinfo where ~~~ 
2. delete from mail where ~~ 
3. delete from article where~~ 

~~如果没有事务处理，在你删除的过程中,假设出错了,只执行了第一句,那么其后果是难以想象的,但用事务处理,如果删除出错,你只要rollback就可以取消删除操作（其实是只要你没有commit你就没有确实的执行该删除操作）

一般来说,在商务级的应用中,都必须考虑事务处理的,查看inodb信息,代码如下:

shell> /usr/local/mysql -u root -p

mysql> show variables like "have_%"

系统会提示:

1. +-------------------+--------+  
2. | Variable_name     | Value |  
3. +-------------------+--------+  
4. | have_bdb          | YES    |  
5. | have_crypt        | YES    |  
6. | have_innodb       | YES    |  
7. | have_isam         | YES    |  
8. | have_raid         | YES    |  
9. | have_symlink      | YES    |  
10. | have_openssl      | NO     |  
11. | have_query_cache  | YES    |  
12. +-------------------+--------+  
13. 8 rows in set (0.05 sec) 

如果是这样的,那么我们就可以创建一张支持事务处理的表来试试了.

MYSQL的事务处理功能

一直以来我都以为MYSQL不支持事务处理，所以在处理多个数据表的数据时，一直都很麻烦（我是不得不将其写入文本文件，在系统重新加载得时候才写入数据库以防出错）～今天发现MYSQL数据库从4.1就开始支持事务功能，5.0引入了存储过程^_^

先简单介绍一下事务吧！事务是DBMS得执行单位。它由有限得数据库操作序列组成得。但不是任意得数据库操作序列都能成为事务。

一般来说,事务是必须满足4个条件(ACID):

原子性（Autmic）:事务在执行性，要做到“要么不做，要么全做！”，就是说不允许事务部分得执行。即使因为故障而使事务不能完成，在rollback时也要消除对数据库得影响！

一致性（Consistency）:事务得操作应该使使数据库从一个一致状态转变倒另一个一致得状态！就拿网上购物来说吧，你只有即让商品出库，又让商品进入顾客得购物篮才能构成事务！

隔离性（Isolation）:如果多个事务并发执行，应象各个事务独立执行一样！

持久性（Durability）:一个成功执行得事务对数据库得作用是持久得，即使数据库应故障出错，也应该能够恢复！

MYSQL的事务处理主要有两种方法:

用begin, rollback, commit来实现:begin 开始一个事务,rollback 事务回滚,commit  事务确认,直接用set来改变mysql的自动提交模式.

MYSQL默认是自动提交的,也就是你提交一个QUERY,它就直接执行,我们可以通过.

set autocommit=0   禁止自动提交

set autocommit=1 开启自动提交

但注意当你用 set autocommit=0 的时候，你以后所有的SQL都将做为事务处理，直到你用commit确认或rollback结束，注意当你结束这个事务的同时也开启了个新的事务！按第一种方法只将当前的作为一个事务！

个人推荐使用第一种方法！

MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理,其他的类型是不支持的,切记,测试,SQL代码如下:

1. mysql> use test;  
2. Database changed  
3.    
4. mysql> CREATE TABLE `dbtest`(  
5.     -> id int(4)  
6.     -> ) TYPE=INNODB;  
7. Query OK, 0 rows affected, 1 warning (0.05 sec)  
8.    
9. mysql> SELECT * FROM `dbtest`;  
10. Empty set (0.01 sec)  
11.    
12. mysql> begin;  
13. Query OK, 0 rows affected (0.00 sec)  
14.    
15. mysql> INSERT INTO `dbtest` VALUES(5);  
16. Query OK, 1 row affected (0.00 sec)  
17.    
18. mysql> INSERT INTO `dbtest` VALUES(6);  
19. Query OK, 1 row affected (0.00 sec)  
20.    
21. mysql> commit;  
22. Query OK, 0 rows affected (0.00 sec)  
23.    
24. mysql> select * from dbtest;  
25. +------+  
26. | id   |  
27. +------+  
28. |  5   |  
29. |  6   |  
30. +------+  
31. 2 rows in set (0.00 sec)  
32.    
33. mysql> begin;  
34. Query OK, 0 rows affected (0.00 sec)  
35.    
36. mysql> INSERT INTO `dbtest` VALUES(7);  
37. Query OK, 1 row affected (0.00 sec)  
38.    
39. mysql> rollback;  
40. Query OK, 0 rows affected (0.00 sec)  
41.    
42. mysql> select * from dbtest;  
43. +------+  
44. | id   |  
45. +------+  
46. |  5   |  
47. |  6   |  
48. +------+  
49. 2 rows in set (0.00 sec)  
50. mysql>

php函数,代码如下:

1. function Tran ($sql)  
2. {  
3.     $judge = 1;  
4.     mysql_query('begin');  
5.     foreach ($sql as $v)  
6.     {  
7.         if (!mysql_query($v))  
8.         {  
9.             $judge = 0;  
10.         }  
11.     }  
12.    
13.     if ($judge == 0)  
14.     {  
15.         mysql_query('rollback');  
16.         return false;  
17.     }  
18.     elseif ($judge == 1)  
19.     {  
20.         mysql_query('commit');  
21.         return true;  
22.     }  
23. } 

PHP回滚,代码如下:

1. <?php  
2. $handler = mysql_connect('localhost', 'root', '');  
3. mysql_select_db('task');  
4. mysql_query('SET AUTOCOMMIT=0');    //设置为不自动提交，因为MYSQL默认立即执行  
5. mysql_query('BEGIN');               //开始事务定义  
6.    
7. if(!mysql_query('INSERT INTO `trans` (`id`) VALUES (2);'))  
8. {  
9.     mysql_query('ROOLBACK');        //判断当执行失败时回滚  
10. }  
11.    
12. if(!mysql_query('INSERT INTO `trans` (`id`) VALUES (4);'))  
13. {  
14.     mysql_query('ROOLBACK');        //判断执行失败回滚  
15. }  //Vevb.com 
16.    
17. mysql_query('COMMIT');              //执行事务  
18. mysql_close($handler);  
19. ?>