05MySQL----事务的应用

1 MySQL事务的概述

在MySQL中，事务由单独的一条或多条SQL语句组成。在这个单元中，每条MySQL语句时相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中一旦某条SQL语句执行失败或是产生错误，整个单元将会回滚，所有受到影响的数据将返回到事务开始前的状态；如果单元中的SQL语句均执行成功，则事务被顺利执行。

在现实生活中，事务处理数据的应用非常广泛，如网上交易、银行事务等。下面通过网上交易流程来讲述事务的概念。

相信大多数用户都有过网上购物的体验，即用户登录某个大型购物网站，浏览该网站中所陈列的商品信息，将喜欢的商品放入购物车中，选购完毕后，用户需要对选购的商品进行在线支付，当用户对所选商品付款完毕，通知商家发货。

在此过程中，用户所付货款并未提交到商户手中，当用户收到货物之后，可以确认收货，商家才收到商品货款，整个交易过程才算完成。

如果任何一步操作失败，则都会导致双方陷入尴尬的境界。试想当用户选购商品，付款操作完成后，用户选择在发货过程中取消订单，这时商家并没有得到货款将取消操作，如果不应用事务处理，则用户在取消订单操过程后，商家仍然继续将用户所订购的商品发送给用户，这会导致一些不愉快的争端。

故在整个交易过程中，必须用事务来对网上交易进行回滚操作。其流程如下：

                                                       

在网上交易流程过程中，商家与用户的交易可以被认为是一个事务处理过程，如果在交易流程中存在一个环节失败，都可能导致双方交易的失败。

如前面事务定义所说，所有这些流程都应该被成功执行，在MySQL中如果有任何命令失败，都会导致所有操作命令被撤销。系统返回未操作前的状态，即回滚到初始状态。添加到购物车、在线付款、商家发货等构成了一个基本的事务。

通过InnoDB和BDB类型表，MySQL事务能够完全满足事务安全的ACID测试。但是并不是所有表类型都支持事务，如MyISAM类型表就不能支持事务，只能通过伪事务对表实现事务处理。

说明
ACID指出每个事务型RDBMS必须遵守的4个属性，即原子性、一致性、孤立性和持久性。

1.1 原子性

这就类似于化学中的原子，事务也具备完整性和不可分割性，被认为是一个不可可分割的单元。

假设一个事务由多种任务组成，其中的语句必须同时操作成功，才可以认为事务是成功的，否则将回滚到初始状态。

从上面网上交易的例子可以看出，所有操作的成功是保证交易完成的前提条件，当任何一个环节出现问题时，就导致事务回滚，不能正常完成交易过程。即所有事务共同进退，保证了事务的整体性，这就是事务的原子性。

原子的执行是一个全部发生或全部失败的整体过程。在一个原子操作中，如果事务中的任何一条语句失败，前面执行的语句都将被返回，以保证数据的整体性不被破坏。这在常用的系统应用中，为保证数据的安全性起到一定作用。

1.2 一致性

在MySQL事务处理过程中，无论事务是完全成功说是在中途某些环节失败而导致失败，但事务使系统处于一致的状态时，其必须保证一致性。如在网上进行转账操作的过程中，用户A向用户B的账户中转入5 000元，但用户B在查询转账信息的时候，发现自己的账户中只增加了3 000元，这样不能使整个事务达到一致性。例如，从上述网上交易的例子考虑，当交易完成后，商家的货物会减少，不可能出现当商家给用户发货后货物数量不变，而用户收到货物不增加的情况。

在MySQL中，一致性主要由MySQL的日志机制处理，它记录数据库的所有变化，为事务回复提供跟踪记录。如果系统在事务处理中间发生错误，MySQL恢复过程将使用这些日志发现事务是否已经完全成功执行或需要返回。一致性属性保证数据库从不返回一个未处理的事务。

1.3 孤立性

孤立性是指每个事务在自己的空间发生，与其发生在系统中的事务隔离，而且事务的结果只在它完全被执行时才能看到。及时这样的一个系统中同时发生多个事务，孤立性也快成保证特定的事务在完成之前，其结果是不被公布的。

当系统支持多个同时存在的用户和连接时，系统必须遵守孤立性原则，否则在执行过程中可能导致大量数据被破坏，孤立性保证每个事务完整地在其各自的空间内被顺利执行，保证事务与事务之间不会相互冲突。

1.4 持久性

在MySQL中，即便是数据库系统崩溃，一个被提交的事务仍然在坚持。当一个事务完成，数据库的日志已经被更新时，持久性即可发挥其特有功效。在MySQL中，如果系统崩溃或者数据存储介质被破坏，通过使用日志，系统能够恢复在重启前进行的最后一次成功更新，可以反映系统崩溃时处于执行过程的事务的变化。
MySQL的持久性是通过一条记录事务过程中系统变化的二进制事务日志文件来实现的。如果遇到硬件损坏或者系统的异常关机，系统在下一次启动时，通过使用最后的备份和日志就可以恢复丢失数据。

说明

默认情况下，InnoDB表持久性最久，MyISAM表提供部分持久性。

2 MySQL事务的创建与存在周期

事务的创建过程一般是：初始化事、创建事务、应用SELECT语句查询数据是否被录入和提交事务。如果用户不在操作数据库完成后执行事务提交，则系统会默认执行回滚操作。如果用户在提交事务前选择撤销事务，则用户在撤销前的所有事务将被取消，数据库系统会回到初始状态。

注意
默认情况下，在MySQL中创建的数据表类型都是MyISAM，但是该类型的数据表并不能支持事务。所以，如果用户想让数据表支持事务处理能力，必须将当前操作数据表的类型设置为InnoDB或BDB。

在创建事务的过程中，用户需要创建一个InnoDB或BDB类型的数据表，其基本命令结构如下：

CREATE TABLE table_name(field_definitions)TYPE=INNODE/BDB;

其中，table_name为表名，而field_defintions为表内定义的字段等属性，TYPE指定数据表的类型，既可以是InnoDB类型，也可以是BDB类型。
当用户希望将已经存在的表支持事务处理，则用户可以应用ALTER TABLE命令，指定数据表的类型即可实现对表的类型更改操作，使原本不支持事务的数据表更改为支持事务处理的类型，其命令如

ALTER TABLE table_name TYPE=INNODB/BDB;

在用户更改完整表的类型后，即可使数据表支持事务处理。

说明
应用ALTER TABLE操作可能会导致数据库中的数据丢失，因此为了避免非预期结果出现，在使用ALTER TABLE命令之前，用户需要创建一个表备份。

2.1 初始化事务

初始化MySQL事务，首先声明初始化MySQL事务后所有的SQL语句为一个单元。在MySQL中，应用START TRANSACTION命令来标记一个事务的开始，初始化事务的结构如下：

START TRANSACTION;

另外，用户也可以使用BEGIN或者BEGIN WORK命令初始化事务，通常START TRANSACTION命令后面跟随是组成事务的SQL语句。

在命令提示符中输入如下命令：

start transaction;

如果在用户输入以上代码后，MySQL数据库没有给出警告提示或返回错误信息，则说明用户已经事务初始化成功，用户可以继续执行下一步操作。

2.2 创建事务

mysql -uroot -proot

use db_databse;

start transaction;

insert into connection(email,cellphone,QQ,sid) values('zhuo@163.com',42141232134,141294819,5);

2.3 用SELECT语句查询数据是否被正确输入

事务创建成功后，建议读者通过SELECT语句查询数据是否被正确录入。

在事务初始化成功后，用户创建事务，继续在命令提示符中输入如下执行：

SELECT * FROM connection WHERE sid=5;

说明
在用户插入新表为InnoDB类型或更改原来表类型为InnoDB时，如果在输入命令提示后，MySQL提示“The 'InnoDB' feature is disabled; you need 'InnoDB' to have it working”警告，则说明InnoDB表类型并没有被开启，用户需要找到MySQL文件目录下的my.ini文件，定位skip_innodb选项位置，将原来的skip_innodb改为“#skip_innodb”后保存该文件，重新启动MySQL服务器，即可令数据库支持InnoDB类型表。

2.4 提交事务

在用户没有提交事务之前，当其他用户连接MySQL服务器时，应用SELECT语句查询结果，则不会显示没有提交的事务。

当且仅当用户成功提交事务后，其他用户才可能通过SELECT语句查询事务结果。

由事务的特性可知，事务具有孤立性，当事务处在处理过程中，其实MySQL并未将结果写入磁盘中，这样一来，这些正在处理的事务相对其他用户是不可见的。

一旦数据被正确插入，用户可以使用COMMIT命令提交事务。提交事务的命令结构如下。

COMMIT;

一旦当前执行事务的用户提交当前事务，则其他用户就可以通过会话查询结果。

2.5 撤销事务(事务回滚）

撤销事务，又被称作事务回滚。即事务被用户开启、用户输入的SQL语句被执行后，如果用户想要撤销刚才的数据库操作，可使用ROLLBACK命令撤销数据库中的所有变化。ROLLBACK命令结构如下。

ROLLBACK;

输入回滚操作后，如何判断是否执行回滚操作了呢？可以通过SELECT语句查看前面插入的数据是否存在，不存在则回滚成功。

注意
如果执行一个回滚操作，则在输入START TRANSACTIONA命令后的所有SQL语句都将执行回滚操作。故在执行事务回滚前，用户需要慎重选择执行回滚操作。如果用户开启事务后，没有提交事务，则事务默认为自动回滚状态，即不保存用户之前的任何操作。

说明

在显示应用中，事务撤销即事务回滚有着重要的意义。例如，用户A和用户B采用银行转账方式交易，用户A将个人账户的部分存款转移到用户B的个人账户过程中，若银行的数据库系统突然发生错误或异常，则交易事务提交失败，系统执行回滚操作，恢复到交易的初始状态。这样，采用事务回滚可以避免因特殊情况而导致事务提交失败，从而导致不必要的损失。

2.6 事务的存在周期

事务的周期由用户在命令提示符中输入START TRANSACTION指令开始，直至用户输入COMMIT结束，如图展示了一个简单事务存在周期流程图。

                                                                    

说明
事务不支持嵌套功能，当用户在未结束第一个事务又重新打开一个事务时，则前一个事务会自动提交。在MySQL中很多命令都会隐藏执行COMMIT命令。

3 MySQL中的事务行为

在MySQL中，存在两个可以控制行为的变量，它们分别是AUTOCOMMIT变量和TRANSACTION ISOLACTION LEVEL变量。

3.1 自动提交

在MySQL中，如果不更改其自动提交变量，则系统会自动向数据库提交结果，用户在执行数据库操作过程中，不需要使用START TRANSACTION语句开始事务，应用COMMIT或者ROLLBACK提交事务或执行回滚操作。

如果用户希望通过控制MySQL自动提交参数，可以更改提交模式，这一更改过程是通过设置AUTOCOMMIT变量来实现。

下面通过一个示例向读者展示如何关闭自动提交参数，在命令提示符中输入以下命令：

SET AUTOCOMMIT=0;

关闭自动提交功能。只有当用户输入COMMIT命令后，MySQL才将数据表中的资料提交到数据库中，如果不提交事务，而终止MySQL会话，数据库将会自动执行回滚操作。

示例：在关闭自动提交命令后，查询数据表中的数据，并进行一条数据的插入

select * from timeinfo;

insert into timeinfo(info) values('test autocommit');

由于用户已经关闭了自动提交功能，所以上面进行的添加操作由于没有执行事务的提交操作，导致数据库没有成功添加。再次查询数据库中的数据。可预见之前插入的数据并未插入到数据库中，另外，可以通过查看@@AUTOCOMMIT变量来查看当前自动提交状态，查看此变量同样应用SELECT语句

SELECT @@autocommit;

3.2 事务的孤立级别

事务具有独立的空间，在MySQL服务器中，用户通过不同的会话执行不同的事务，在多用户环境中，许多RDBMS会话在任意指定时刻都是活动的。为了使这些事务互不影响，保证数据库性能不受到影响，采用事务的孤立级是十分有必要的。

孤立级在整个事务中起到了很重要的作用，如果没有这些事务的孤立性，不同的SELECT语句将会在同一事务的环境中检索到不同的结果，这将导致数据的不一致性。给不同的用户造成困扰，这样一来，用户就不能将查询的结果集作为计算基础。所以孤立性强制保持每个事务的独立性，以此来保证事务中看到一致的数据。

基于ANSI/ISO SQL规范，MySQL提供以下4种孤立级。

（1）SERIALIZABLE（序列化）：

顾名思义，以序列的形式对事务进行处理，该孤立级的特点是只有当事务提交后，用户才能从数据库中查看数据的变化。该孤立级运行会影响MySQL的性能。因为需要占用大量资源，以保证使大量事务在任意时间不被用户看到。
（2）REPEATABLE READ（可重读）：

对于应用程序的安全性作出部分妥协，以提高其性能。事务在该孤立级上不会被看成一个序列，不过当前在执行事务的过程中，用户仍然看不到事务的过程。直到事务提交为止，用户才能够看到事务的变化结果。
（3）READ COMMITTED（提交后读）：

提交后读孤立级的安全性比重复读安全性要低。在这一级的事务，用户可以看到其他事务添加的新纪录。在事务处理时，如果存在其他用户同时对事务的相关表进行修改，那么同一事务中不同时间内，应用SELECT语句返回可能的不同结果集。

(4) READ UNCOMMITED(未提交读)：

该孤立级提供事务间的最小程度间隔，该独立级容易产生幻读操作。其他用户在该孤立级别上看到未提交的事务。

3.3 修改事务的孤立级

在MySQL中，可以使用TRANSACTION ISOLATION LEVEL变量修改事务孤立级，其中，MySQL的默认孤立级未REPEATABLE READ(可重读)，用户使用SELECT命令可以获取当前事务孤立级变量的值，其命令如下。

SELECT @@tx_isolation;

可以通过SET命令来修改事务的孤立级

set global ttransaction isolation level read committed;

说明

如果用户想修改事务的孤立级，必须首先获取SUPER优先权，以便用户可以顺利执行修改操作。

4 事务的性能

从前面可以看出，应用不同孤立级的事务可能会对系统造成一系列影响，采用不同孤立级处理事务，可能会对系统稳定性和安全性等诸多因素造成影响。另外，有些数据库操作中，不需要应用事务处理，则用户在选择数据表类型时，需要选择合适的数据表类型。

所以，在选择表类型时，应该考虑数据表具有完善的功能，且高效执行的前提下，也不会对系统增加额外的负担。

4.1 应用的小事务

应用小事务的意义在于：保证每个事务不会在执行前等待很长时间，从而避免各个事务因为互相等待而导致系统性能的大幅度下降。用户在应用少数大事务的时候，可能无法看出因事务间互相等待而导致系统性能下降，但是当系统中存在处理量很大的数据库或多种复杂事务的时候，用户就可以明显感觉到事务因长时间等待，而导致系统性能下降。所以，应用小事务可以保证系统的性能，其可以快速变化或退出，这样，其他在队列中准备就绪的事务就不会受到明显影响。

4.2 选择合适的孤立级

因为事务的性能与其对服务器产生的负载成反比，即当事务孤立级越高，其性能越低，但是其安全性也越高。其性能和孤立级关系如下所示。所以只有选择适当的孤立级，才能有效地提高MySQL系统性能和应用性。

                                                                   

从图中可知，虽然随着孤立级的增高稳定性也会随之改变，但是并不代表孤立级的稳定性越高，也不能表明其灵活性越高，故用户在选择孤立级的时候，需要根据自身实际情况选择合适应用的孤立级。

4.3 死锁的概念与避免

死锁，即当两个或者多个处于不同序列的用户打算同时更新某相同的数据库时，因互相等待对方释放权限而导致双方一直处于等待状态。

在实际应用中，两个不同序列的客户打算同时对数据执行操作，极有可能产生死锁。更具体地讲，当两个事务相互等待操作对方释放所持有的资源，而导致两个事务都无法操作对方持有的资源，这样无限期的等待被称作死锁。

不过，MySQL的InnoDB表处理程序具有检查死锁这一功能，如果该处理程序发现用户在操作过程中产生死锁，该处理程序立刻通过撤销操作来撤销其中一个事务，以便使死锁消失。这样就可以使另一个事务获取对方所占有的资源而执行逻辑操作。

在MySQL中，InnoDB和BDB类型表可以支持事务处理，但是MySQL中MyISAM类型表并不能支持事务处理，对于某些应用该类型的数据表，用户可以选择应用表锁定来替代事务。这种引用表锁定来替代事务的事件被称作伪事务。使用表锁来锁定表的操作，可以加强非事务表在执行过程中的安全性和稳定性。

5 MySQL伪事务

在MySQL中，InnoDB和BDB类型可以支持事务处理，但是MySQL中MyISAM类型并不支持事务处理，对于某些应用该类型的数据表，用户可以选择应用表锁定来代替事务。

这种引用表来代替事务的事件被称为伪事务。使用表锁来锁定表的操作，可以加强非事务表在执行过程中的安全行和稳定性。

5.1 用表锁定代替事务

在MySQL的MyISAM类型数据表中，并不支持COMMIT(提交）和ROLLBACK(回滚)命令。当用户对数据执行插入、删除、更新等操作时，这些变化的数据都被立刻保存在磁盘中。这样，在多用户环境中，会导致诸多问题，为了避免同一时间有多个用户对数据库中指定表进行操作。可以应用表锁定来避免在用户操作数据表过程中受到干扰。当且仅当该用户释放表的操作锁定后，其他用户才可以访问这些修改后的数据表。

设置表锁定代替事务基本步骤如下

(1)为指定数据表添加锁定，其语法如下：

LOCK TABLES table_name lock_type,....

其中，table_name为被锁定的表名称，lock_type为锁定类型，该类型包括以读方式(READ)锁定表；以写方式(WRITE)锁定表。

（2）用户执行数据表的操作，可以添加、删除或者更改部分数据。
（3）用户完成对锁定数据表的操作后，需要对该表进行解锁操作，释放该表的锁定状态，其语法如下。

UNLOCK TABLES;

下面通过实例向读者展示如何以读方式锁定数据表和以写方式锁定数据表。

5.1.1 以读方式锁定数据表

以读方式锁定数据表，该方式是设置用户的其他方式操作，如删除、插入、更新都不被允许，直至用户进行解锁操作。

示例：以读方式锁定表后，向该表中插入数据，以及解锁后再插入数据的情况：

1 首先，以锁定数据表studentinfo为例，在命令提示符输入如下代码：

lock table studentinfo read;

然后，应用SELECT语句查看表中的信息:

select * from studentinfo;

接着尝试进行向数据表中插入数据，则会报错。

从上述结果中可以看出，当用户试图向数据库插入数据时，将会返回失败信息。当用户将锁定表解锁后，在次执行插入操作，则能成功。

一般锁定被释放后，用户可以对数据库执行添加、删除、更新等操作。

说明
其中，lock_type参数中，用户指定数据表以读方式（READ）锁定数据表的变体为READ LOCAL锁定，其与READ锁定的不同点是，该参数所指定的用户会话可以执行INSERT操作。它是为了使用MySQL dump工具而创建的一种变体形式。

5.1.2 以写的方式锁定数据表

与读方式锁定表类似，表的写锁定是设置用户可以修改数据表中的数据，但是除自己以外其他会话的用户不能进行任何读操作。在命令提示符中输入如下命令：

lock table studentinfo write;

示例：因为该表为写锁定，用户可以对数据库的数据执行修改、添加、删除等操作。在该命令提示符中应用SELECT语句查询该锁定表。

select * from studentinfo;

当前用户应用SELECT语句仍然可以查询该表的数据，并没有限制用户对数据表的读操作。这是因为，以写方式锁定数据表并不能限制当前锁定用户的查询操作，下面打开一个新用户会话，即保持上面的窗口不被关闭，重新打开一个新的MySQL连接，并执行上述过程。

此时使用SELECT语句执行查询操作，并没有结果显示，这是因为之前该表以写方式锁定。故当操作用户释放该数据表锁定后，其他用户才可以通过SELECT语句查看之前被锁定的数据表。在命令提示符中输入：

UNLOCK TABELS;

这时，在第二个命令提示符窗口，即可查询成功。当数据表被释放锁定的时候，其他访问数据库的用户即可查看数据表的内容。

说明

使用UNLOCK TABLE命令后，将会释放所有当前处于锁定状态的数据表。

5.2 应用表锁实现伪事务

通过使用表锁对MyISAM表进行锁定操作，以此过程来代替事务型表InnoDB,即应用表锁来实现伪事务。实现伪事务的一般步骤如下：

1 对数据库中的数据表进行锁定操作，可以对多个表做不同的方式锁定，期待吗格式如下：

LOCK TABLE table_name lock_type1,table_name2 lock_type2,....

2 执行数据库操作，向锁定的数据表中执行添加、删除、修改等操作。

如前面提到的INSERT、UPDATE、DELETE等操作。用户可以对锁定的数据表执行上述操作，在执行过程中，该伪事务所产生的结果是不会被其他用户更改的。

3 释放锁定的数据表，以便让正在队列中等待查看或操作的其他用户可以浏览数据表中的数据或对操作表执行各种数据的操作。

如果存在其他会话要求访问已锁定的多个表格，则该会话必须被迫等待当前锁定用户释放锁定表，才允许其他会话访问该数据表，表锁定使不同会话执行的数据库操作彼此独立。应用数据库锁定方式可以使不支持事务类型的表实现伪事务。