MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关:

MyISAM:不支持事务,用于只读程序提高性能 

InnoDB:支持ACID事务、行级锁、并发 

Berkeley DB:支持事务

 

一个事务是一个连续的一组数据库操作,就好像它是一个单一的工作单元进行。换言之,永远不会是完整的事务,除非该组内的每个单独的操作是成功的。如果在事务的任何操作失败,则整个事务将失败。

实际上,会俱乐部许多SQL查询到一个组中,将执行所有的人都一起作为事务的一部分。


事务的特性: 

事务有以下四个标准属性的缩写ACID,通常被称为:

原子性: 确保工作单元内的所有操作都成功完成,否则事务将被中止在故障点,和以前的操作将回滚到以前的状态。

一致性: 确保数据库正确地改变状态后,成功提交的事务。

隔离性: 使事务操作彼此独立的和透明的。

持久性: 确保提交的事务的结果或效果的系统出现故障的情况下仍然存在。


在MySQL中,事务开始使用COMMIT或ROLLBACK语句开始工作和结束。开始和结束语句的SQL命令之间形成了大量的事务。


COMMIT & ROLLBACK: 

这两个关键字提交和回滚主要用于MySQL的事务。

当一个成功的事务完成后,发出COMMIT命令应使所有参与表的更改才会生效。

如果发生故障时,应发出一个ROLLBACK命令返回的事务中引用的每一个表到以前的状态。

可以控制的事务行为称为AUTOCOMMIT设置会话变量。如果AUTOCOMMIT设置为1(默认值),然后每一个SQL语句(在事务与否)被认为是一个完整的事务,并承诺在默认情况下,当它完成。 AUTOCOMMIT设置为0时,发出SET AUTOCOMMIT =0命令,在随后的一系列语句的作用就像一个事务,直到一个明确的COMMIT语句时,没有活动的提交。

可以通过使用mysql_query()函数在PHP中执行这些SQL命令。


通用事务例子 

这一系列事件是独立于所使用的编程语言,可以建立在任何使用的语言来创建应用程序的逻辑路径。

可以通过使用mysql_query()函数在PHP中执行这些SQL命令。


BEGIN WORK开始事务发出SQL命令

发出一个或多个SQL命令,如SELECT,INSERT,UPDATE或DELETE

检查是否有任何错误,一切都依据的需要。

如果有任何错误,那么问题ROLLBACK命令,否则发出COMMIT命令。


在MySQL中的事务安全表类型:

如果打算使用MySQL事务编程,那么就需要一种特殊的方式创建表。有很多支持事务但最流行的是InnoDB表类型。

从源代码编译MySQL时,InnoDB表支持需要特定的编译参数。如果MySQL版本没有InnoDB支持,请互联网服务提供商建立一个版本的MySQL支持InnoDB表类型,或者下载并安装Windows或Linux/UNIX的MySQL-Max二进制分发和使用的表类型在开发环境中。

如果MySQL安装支持InnoDB表,只需添加一个的TYPE=InnoDB 定义表创建语句。

例如,下面的代码创建InnoDB表tcount_tbl:

mysql> create table tcount_tbl
    -> (
    -> tutorial_author varchar(40) NOT NULL,
    -> tutorial_count  INT
    -> ) TYPE=InnoDB;

可以使用其他GEMINI或BDB表类型,但它取决于您的安装,如果它支持这两种类型。


由于项目设计里面,牵扯到了金钱的转移,于是就要用到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都加上了 共享锁


出错处理

根据出错信息,执行相应的处理


mysql事物处理实例

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

1.用begin,rollback,commit来实现

    begin开始一个事务

    rollback事务回滚

    commit 事务确认

2.直接用set来改变mysql的自动提交模式

    mysql默认是自动提交的,也就是你提交一个query,就直接执行!可以通过

    set autocommit = 0 禁止自动提交

    set autocommit = 1 开启自动提交

    来实现事务的处理。


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


MYSQL只有 INNODB和BDB类型的数据表才支持事务处理,其他的类型是不支持的!

mysql> use test;
Database changed
mysql> CREATE TABLE `dbtest`(
    -> id int(4)
    -> ) TYPE=INNODB;

mysql> select * from dbtest;
Empty set (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dbtest values(5);
Query OK, 1 row affected (0.00 sec)
mysql> insert into dbtest value(6);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dbtest;
+------+
| id   |
+------+
|    5 |
|    6 |
+------+
2 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dbtest values(7);
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dbtest;
+------+
| id   |
+------+
|    5 |
|    6 |
+------+
2 rows in set (0.00 sec)

mysql> mysql事务处理


php代码实现事务的处理可以通过PHP预定义类mysqli的以下方法实现。

autocommit(boolean):该方法用于限定查询结果是否自动提交,如果该方法的参数为true则自动提交,如果参数为false则关闭自动提交。MySQL数据库默认为自动提交。

rollback():利用mysqli类中的该方法可以实现事务的回滚。

commit():利用该方法可以实现提交所有查询。

include_once("conn.php");
$id=$_GET[id];
$conn->autocommit(false);
if(!$conn->query("delete from tb_sco where id='".$id."'")){
  $conn->rollback();
}
if(!$conn->query("delete from tb_stu where id='".$id."'")){
  $conn->rollback();
}
$conn->commit();
$conn->autocommit(true);
echo "ok"


例2

require('connectDB.php'); //建立数据库连接
mssql_query("BEGIN TRANSACTION DEPS02_DEL"); //开始事务
$delete_dep_sql="DELETE FROM TBLDEPARTMENT WHERE DEPTID='{$_GET[deptid]}'";
// echo $delete_dep_sql."<br>";
mssql_query($delete_dep_sql); //操作数据库
// var_dump($del_result);
$delete_result = mssql_query("select @@ROWCOUNT as id");
$delete_info = mssql_fetch_array($delete_result);
$delete_rows = $delete_info[0];
// var_dump($delete_rows);
mssql_free_result($delete_result);
echo "<script language=javascript>";
if(true){    //判断是否回滚提交
    mssql_query("COMMIT TRANSACTION DEPS02_DEL"); //提交事务
    echo "alert('delete success!');";
}
else{
    mssql_query("ROLLBACK TRANSACTION DEPS02_DEL"); //回滚事务
    echo "alert('delete faile!');";
}
echo "</script>";mssql_close();


例3

 MySQL的事务处理在处理实际问题中有着广泛且重要的应用,最常见的应用如银行转账业务、电子商务支付业务等等。但是,值得注意的是,MySQL的事务处理功能在MYSIAM存储引擎中是不支持的,在InnoDB存储引擎中是支持的。现在上传一段代码,作为引导认识MySQL事务处理的开始,简单的实例,但融汇思想,相信会有很大的帮助。

$conn=mysql_connect('localhost','root','yourpassword')or die(mysql_error());
mysql_select_db('transaction',$conn);
mysql_query('set names utf8');
//创建事务
mysql_query('START TRANSACTION') or die(mysql_error());
$sqlA="update A set account=account-1";
if(!mysql_query($sqlA)){
    mysql_query('ROLLBACK') or exit(mysql_error());//判断当执行失败时回滚
    exit();
}
$sqlB="update B set account=account+1";
if(!mysql_query($sqlB)){
    mysql_query('ROLLBACK') or exit(mysql_error());//判断当执行失败时回滚
    exit();
}
mysql_query('COMMIT')or die(mysql_error());//执行事务
mysql_close($conn);

以上代码可以作为模拟银行转账业务的事务流程。以表A、B分别表示两个已在银行开户的账户,当账户A执行转出1元给账户B的操作时,如果操作执行失败,转出将会回滚至原始状态,不继续向下执行动作。反之,如果操作执行成功,则账户B可用余额将增加1元,否则事务回滚至原始状态。


趣味分析

80年代中国人结婚四大件:手表、自行车、缝纫机、收音机(三转一响)。要把事务娶回家需要四大件,所以事务很刻薄(ACID),四大件清单:原子性(Atom)、一致性(Consistent)、隔离性(Isolate)、持久性(Durable)。ACID就是数据库事务正确执行的四个基本要素的缩写。


1.原子性:要么不谈,要谈就要结婚!

对于其数据修改,要么全都执行,要么全都不执行。如果系统只执行这些操作的一个子集,则可能会破坏事务的总体目标。最典型的问题就是银行转帐问题。


2. 一致性:恋爱时,什么方式爱我;结婚后还得什么方式爱我;

数据库原来有什么样的约束,事务执行之后还需要存在这样的约束,所有规则都必须应用于事务的修改,以保持所有数据的完整性。事务结束时,所有的内部数据结构(如 B 树索引或双向链表)、完整性约束(索引、主键)都必须是一致的。


3.隔离性:闹完洞房后,是俩人的私事。

事务查看数据时数据所处的状态,要么是另一并发事务修改它之前的状态,要么是另一事务修改它之后的状态,事务不会查看中间状态的数据。当事务可序列化时将获得最高的隔离级别。隔离性是事务机制里相对来说,比较复杂的,下文另说。


4.持久性:一旦领了结婚证,无法后悔。

修改即使出现致命的系统故障也将一直保持。不要告诉我系统说commit成功了,回头电话告诉我,服务器机房断电了,我的事务涉及到的数据修改可能没有进入数据库。

 

抛开刚才的四大件比方,再谈隔离性。隔离性是指DBMS可以在并发执行的事务间提供不同级别的数据读写安全隔离措施。隔离的级别和并发事务的吞吐量之间存在反比关系。较高级别的隔离可能会带来较高的冲突和较多的事务流产。流产的事务要消耗资源,这些资源必须要重新被访问。所以这需要trade-off,到底是访问速度优先,还是数据绝对正确优先。

两台机器分别向数据库插入数据,在提交事务前再查询一次数据库,此时本机器的读操作可以读到本机器尚未提交的事务的数据吗?这个问题本身就是一种意识错误,事务是一个原子单位,任何隔离级别都改变不了这个真理。

我们先了解几个数据库不得不知的秘密,事务一旦启动,写写肯定是冲突的。那么读写呢?读分成两种,被别人读和读别人的数据。被别人读会产生脏数据的问题。读别人的会产生不可重复读和幻读两种情况。


A.脏读:读到的数据不是此刻真实的数据。

脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。打个比方:

我的支付宝余额3000元,事务A改为2000,但事务A尚未提交。与此同时,事务B正在读取,并且“成功”读取到余额为2000。随后,事务A由于网络或者IO原因执行失败,回滚成3000元,事务B拿到的值继续运算,就是错误的,这算是一次dirty read。 ' T, B 

银行转帐,A给B转1000元,B+1000,这个时候,B就能够读取到。这个时候A还没有减掉1000元,后悔了,没有提交,这个时候B把钱提走了,这不扯吗? 银行在早年代出现类似情形的bug.


B.不可重复读:读了两次,值不一样。

一个事务对同一行数据重复读取两次,但是却得到了不同的结果。例如,在两次读取的中途,有人对该行数据进行了update操作,并提交,结果就让当然这个事务郁闷了…

还是余额3000元,事务A是一个比较长的事务,一开始读取到3000,结果恰好我的水电自动扣款100成功(事务B执行成功),事务A在最后读取到的余额成了2900元。这就是不可重复读现象。


C.幻读:原来没有,现在有了…

事务在操作过程中进行两次查询,第二次查询的结果包含了第一次查询中未出现的数据(这里并不要求两次查询的SQL语句相同)。这是因为在两次查询过程中有另外一个事务插入数据造成的。

事务A正在统计到目前为止的订单数量,一开始读到的是10笔。结果恰好这个时候,家人用此支付宝买了一个家电。等事务A打算提交的时候发现成了11笔。

 

刚才说到了这三种应用事务产生的读写问题,事务产生了对应的4种隔离级别,在Mysql中利用如下:

SELECT @@TX_ISOLATION; 即可看到:说明Mysql的默认是可重复读取。 select @@global.tx_isolation; 查看全局的事务隔离级别。现在我们按照java.sql.Connection定义的四个常量值说开来:


1. 读未提交:TRANSACTION_READ_UNCOMMITTED = 1;

允许脏读取,但不允许更新丢失。如果一个事务已经开始写数据,则另外一个数据则不允许同时进行写操作,但允许其他事务读此行修改后却没有提交的数据,就是担心人家的事务出问题回滚(ROLLBACK)了,而你还拿这个脏数据继续计算。该隔离级别可以通过“排他写锁”实现。绝大部分的数据库没有二到这个地步。

设置:SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

表现:可以读取任何数据,但是如果更新到同一数据上,需要等待另一个事务执行完,有超时异常:

注意,即使是GLOBAL设置,也需要打开一个新的会话连接,才能生效,包括当前设置连接。如果是SESSION,当前会话马上生效,但绝不会影响到其它会话的隔离级别,使用SELECT @@TX_ISOLATION; 检查一下当前的隔离级别,免得穿越到秦国。任何Mysql设置,首先要清楚它是全局的,还是会话级别的。


2.读提交:TRANSACTION_READ_COMMITTED   = 2;

允许不可重复读取,但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据,但是未提交的写事务将会禁止其他事务访问该行。

设置:SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

表现:对同一数据更新需要等待,一个事务如果没有COMMIT,任何其它事务无法读取它的中间值。因为只是加了行共享锁,所以此时,还是可以读到一个事务里正在被update的数据。这里的问题是一个事务你还可以读另外一个事务正在更新的数据。


3.可重复读取:TRANSACTION_REPEATABLE_READ  = 4;

禁止不可重复读取和脏读取,但是有时可能出现幻影数据,但在innodb中此隔离级别不允许幻象读,应该说这已经是较高级别的安全保证了。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务(但允许读事务),写事务则禁止任何其他事务。

设置:SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

表现:除了之上两个要求之后,如果一个事务对一行的读取,即使其它事务的的确确已经修改了此项数据,他也还是会将错就错到底,不会去读这条新值,保证一个事务开始后,读取的任何数据都一份


4.序列化:TRANSACTION_SERIALIZABLE     = 8;

与“可重复读取”隔离最大的区别是读也会加锁,另外事务无法更新。它要求事务序列化执行,事务只能一个接着一个地执行,但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的,必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。任何数据的插入与更新,都是慢慢来,象单跑道起飞的飞机一样。在序列化隔离中,innodb会对每一个select语句后自动加上lock in share mode.

设置:SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

表现:它依然可以同时起多个事务,但是如果对同一数据进行的任何读写操作,都必须一个等待另一个执行完再说,原则是先到的有锁定权。如果你执行一个update,对方也来一个update,那么出现:

在Java中,修改事务隔离级别,void setTransactionIsolation(int level) throws SQLException;

如果出现两边会话设置隔离级别不一致的情况,属性互相独立,以更高隔离级别为准。实际上隔离级别只针对于锁。设定对应的隔离级别,对应的操作都有对应的锁去执行现场清理工作。锁事实上只有两种:

A. 共享锁(Shared Lock) 也叫读锁.

共享锁表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。

B.写锁(Write Lock)也叫排它锁

写锁表示对数据进行写操作。如果一个事务对对象加了排他锁,其他事务就不能再给它加任何锁了。

 

锁粒度本连接查询本连接更新其它连接查询其它连接insert其它连接update
LOCK TABLES 表名 READ可以不可以可以不可以不可以
LOCK TABLES 表名 WRITE可以可以不可以不可以不可以


隔离级别的任何实现都离不开锁,DBMS,有一个善良而忠实的看门老者,不停地在给你开门、锁门、防止冲突产生。要mysql锁表的操作是LOCK,而解锁的操作是UNLOCK.

LOCK TABLES 表名 READ LOCAL;  其它连接的insert/update都无法做。(innodb)

  

在innodb中两个事务之间对于不同行的操作是可以的,所以它们实现的是行锁。


开始事务时,必须关闭自动提交,SET AUTOCOMMIT=0; 那么在AUTOCOMMIT=0之前的语句会顺利执行并逐条提交。支持事务的表必须是engine = INNODB。当错误时,全盘检查所有ROLLBACK到start位置。ROLLBACK相当于一个标记点,凡是打此标记的地方,都会被自动回滚。如果没有ROLLBACK,那么即使是autocommit=0,即会被正常提交,无法实现数据的回滚。


COMMIT对于单个事务事实上可有可无。因为end$$会强制提交事务。但在多个事务处理时,必须在某个点提交,否则回滚时存在问题。


只是如果AUTOCOMMIT默认为1,即使有SQL语句包含在事务里边,也是每条自动提交。注意这个时间的提交只是当前连接的操作员自己在YY,因为没有COMMIT,无论AUTOCOMMIT的值等于多少,都会不提交到数据库,让别的人看到。但这个时候非常有意思,因为自动提交为1,如果此事务包括的存储过程被再次调用一次,由于又开始一个新事务,会自动提交上一次的那个没有COMMIT的事务。所以就危害性来说,SET AUTOCOMMIT=0危害性要大得多。


即使COMMIT封闭了START TRANSACTION也无法自动把 AUTOMMIT自动置为1,这就是解释陈良允同学为什么那次操作,我无法看到结果,因为它的连接已经执行,却没有自动提交。可以在他的连接上看到,因为对他来说只是一个临时操作。


事务的开始、回滚、提交与否,与AUTOCOMMIT=0的值的改变没有任何关系!所以高度注意:前提是置AUTOCOMMIT=0,这时如果没有封闭一个事务是极其危险的,因为它非常容易引起锁表,这样影响就是全局性的。注:虽然对于每个连接@@autocommit这个这个系统变量是独立的,但是锁表是全局性的。所以正规操作,在一次存储过程中调用,不管事务提交几次,只需在整个存储过程开端设置一次AUTOCOMMIT=0即可,在整个存储过程结束后单独设置一次AUTOCOMMIT=1即可。难怪我以前写的程序会出现:

Error Code : 1205

Lock wait timeout exceeded; try restarting transaction

连接A: UPDATE e_course SET agent_nick = '222' WHERE course_id =2;

连接B:UPDATE e_course SET agent_nick = '222' WHERE course_id =3;


即使不是更新的同一行,也会超时。是因为我的存储过程里边连接A对commit没有封闭那个事务,使得事务的set AUTOCOMMIT这个值被置为0,这样,对于这个表的后续操作,则被锁住了。如果此时直接关闭连接A,那么连接B就可以顺利提交。如果在存储过程里写SET AUTOCOMMIT=1; 那么即使没有commit也会解锁。如果保险起见,在存储过程结束应该写上SET AUTOCOMMIT=1; 这样可以避免此连接万一没有释放会造成表锁。注意,这个情况只对此表来说,换了其他的表,可以非常正常地更新。这种阻塞的情况,如果存储过程修改成COMMIT; 或者是 set AUTOCOMMIT=1; 那么被阻塞的语句为立刻执行。使用START TRANSACTION,autocommit仍然被禁用,直到您使用COMMIT或ROLLBACK结束事务为止。然后autocommit模式恢复到原来的状态。


mysql的autocommit(自动提交)默认是开启,其对mysql的性能有一定影响,举个例子来说,如果你插入了1000数据,mysql会commit1000次的,如果我们把autocommit关闭掉,通过程序来控制,只要一次commit就可以了。