分类 Mysql 下的文章

关于使用PDO无法执行两次查询的问题

今天在使用PDO查询数据时遇到这么一个问题,使用实例化的PDO类无法执行两次查询,即第一次查询是正常的,第二次查询是无效的。

举个栗子:

$pdo = new PDO('mysql:host=127.0.0.1;dbname=test;', 'root', '');

$stmt1 = $pdo->query('select count(*) from table');

$stmt2 = $pdo->query('select * from table limit 0,5');

实例化PDO类,调用类中方法query查询一条语句“select count(*) from table”,返回的$stmt1变量是一个PDOStatement对象,而此时没有使用$stmt1这个实例做任何操作(或者只是使用了$stmt1->fetchColumn()获得了数据数量)。接着再调用PDO实例中的方法query(使用prepare后再execute也一样)再一次查询数据,这是返回给$stmt2变量的值就不是一个PDOStatement对象了,而是false。使用$stmt2->errorInfo();打印出结果可以看到这么个错误“Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute”

大致意思是说,当一个未缓存查询正在活动时不能再执行查询操作。解决方式有:

1、可以考虑使用前一个查询返回的PDOStatement对象(本例子中是$stmt1)中fetchAll方法把数据处理完毕

2、亦或者是将前一个查询PDOStatement对象设为null:$stmt1=null

3、再或者是连接数据库后使用PDO实例中方法$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true); 设置mysql启用缓冲查询。

这时第二次查询时$stmt2就不会为false了。

Mysql转换表的引擎的三种方法

ALTER TABLE

将表从一个引擎修改为另一个引擎最简单的办法就是使用ALTER TABLE语句。下面将mytable的引擎修改为InnoDB:

mysql> ALTER TABLE `mytable` ENGINE = InnoDB;

上述语法可以适用任何存储引擎。担忧一个问题:需要执行很长时间。Mysql会按行将数据从原表复制到一张新的表中,在赋值期间可能会小号系统所有的I\O能力,同时原表上会加上读锁。所以,在繁忙的表上执行此操作要特别小心。一个替代方案是采用下面的导出与导入的方法,手工进行表的复制。

导出与导入

为了更好的控制转换过程,可以使用mysqldump工具将数据导出到文件,然后修改文件中CREATE TABLE语句的存储引擎选项,注意同时修改表名,因为同一个数据库中不能存在相同的表名,即使它们使用的是不同的存储引擎。同时要注意mysqldump默认会子的那个在CREATE TABLE语句前加上DROP TABLE语句,不注意这一点可能会导致数据丢失。

创建与查询(CREATE和SELECT)

第三种转换的技术综合了第一种方法的高效和第二种方法的安全。不需要导出整个表的数据,而是先创建一个新的存储引擎的表,然后利用INSERT...SELECT语法来导数据:

mysql> CREATE TABLE `innodb_table` LIKE `myisam_table`;
mysql> ALTER TABLE `innodb_table` ENGINE = InnoDB;
mysql> INSERT INTO `innodb_table` SELECT * FROM `myisam_table`;

数据量不大的话,这样做工作的很好。如果数量很大,则可以考虑做分批处理,针对每一段数据执行事务提交操作,以避免大事务产生过多的undo。假设有主键字段id,重复运行以下语句(最小值x和最大值y进行相应的替换)将数据导入到新表:

mysql> START TRANSACTION;
mysql> INSERT INTO `innodb_table` SELECT * FROM `myisam_tale` BETWEEN x AND y;
mysql> COMMIT;

这样操作完成以后,新表示原表的一个全量复制,原表还在,如果需要可以删除原表,如果有表要,可以在执行的过程中对原表加锁,以确保新表和原表的数据一致。

 

Percona Tolkit提供了一个pt-online-schema-change的工具(基于Facebook的在线schema变更技术),可以比较简单、方便的执行上述过程,避免手工操作可能导致的失误和繁琐。

了解mysql中多版本并发控制

前面写的一篇《了解mysql的并发控制》中了解了mysql的并发控制,主要是通过锁机制来控制并发的。控制严格的锁会影响性能,反之则影响数据安全性。而mysql的大多数事务性存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑,它们一般都同时实现了多版本并发控制(MVCC)。

可以认为MVCC是行级锁的一个变种,但是它在很多情况下避免了加锁操作,因此开销更低。虽然实现机制有所不同,但大都实现了非阻塞的读操作,写操作也只锁定必要的行。

MVCC的实现,是通过保存数据在某个时间点的快照来实现的。也就是说,不管需要执行多长时间,每个事务看到的数据都是一致的。下面就通过InnoDB的简化版行为说明MVCC是如何工作的:

InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的。这两个列,一个保存了行的创建时间,一个保存行的过期时间(也叫删除时间)。当然存储的并不是实际的时间值,而是系统的版本号。每开始一个新的事务,系统版本号都会自动递增。事务开始时刻的系统版本号作为事务的版本号,用来和查询到的每行记录的版本号进行比较。下面看一下在REPEATABLE READ(可重复读)隔离级别下,MVCC具体是如何操作的。

SELECT

InnoDB会根据以下两个条件检查每行记录:

a.InnoDB只查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号),这样可以确保事务的读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或者修改过的。

b.行的删除版本要么未定义,要么大于当前事务版本号。这可以确保事务读取到的行,在事务开始之前未被删除。

INSERT

InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

DELETE

InnoDB为删除的每一行保存保存当前系统版本号作为行删除标识。

UPDATE

InnoDB为插入一行新记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为删除标识。

 

保存这两个额外系统版本号,使大多数读操作都都可以不用加锁。这样设计使得读数据操作很简单,性能很好,并且也能保证只会读取到符合标准的行。不足之处是每行记录都会需要额外的存储空间,需要做更多的行检查工作,以及一些额外的维护工作。

MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容。因为READ UNCOMMITTED总是读取到最新的数据行(脏读),而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。(这段内容需要了解mysql隔离级别,请看这篇文章

如果有疑问,欢迎留言探讨。

了解mysql事务

mysql事务可能大多数人都有所了解,本篇博文主要记录我了解的事务的一些细节之处,也必然对事务能有进一步的理解。

看过《高性能mysql》一书,上面写道事务就是一组原子性的sql查询,或者说一个独立的工作单元。可能这句话并不是那么好理解,什么叫原子性?什么又是独立的工作单元?看几个例子和几个名词解释或许更加有帮助。

在事务中,如果数据库引擎能够成功地对数据库应用一组查询的全部语句,那么就执行这组查询。如果其中有任何一条语句因为崩溃或其他原因无法执行,那么所有的语句都不会执行。也就是说,事务内的语句,要么全部执行成功,要么全部执行失败。

讲解事务最经典的例子还是银行转帐。假设银行的数据库有两张表:支票表和储蓄表,现在要从用户A的支票账户转义200元到他的储蓄帐户,那么至少需要三个步骤

1、检查支票账户的余额高于200元。

2、从支票账户余额中减去200元。

3、在储蓄帐户余额中增加200元。

上面三个步骤操作必须打包在一个事务中,其中任何一个步骤失败,则必须回滚所有步骤,即之前操作的几个步骤都失效。

一般可以用START TRANSACTION语句开始一个事务,然后要么使用COMMIT提交事务将修改的数据持久保留,要么使用ROLLBACK撤销所有的修改。上面三个步骤用语句可以这样表示:

1、START TRANSACTION;

2、SELECT balance FROM checking WHERE customer_id=123123;

3、UPDATE checking SET balance = balance -200 WHERE customer_id=123123;

4、UPDATE savings SET balance = =balance + 200 WHERE customer_id=123123;

5、COMMIT;

单纯的讲事务还是不够的。试想一下,如果执行到第四条语句时服务器崩溃了,会发生什么?用户可能会损失200元。再假如,在执行到第三条语句和第四条语句之间时,另一个进程要删除支票账户的所有余额,那么结果可能就是银行在不知情的状况下白了用户200元。

由此看来空谈事务还是不够的,需要通过严格的ACID测试,一个运作良好的事务系统必须具备四个特性,即ACID。这是本篇博文重点要记的,也是我所说的需要记忆的细节之处。下面就介绍一下ACID:

A、原子性(atomicity)

一个事务必须被视为一个不可分割的最小工作单位,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作。这也就解开了本篇博文开头的疑问。

C、一致性(consistency)

数据库总是从一个一致性的状态转换到另一个一致性的状态。在前面的例子中,即使在执行第三、四条语句之间时系统崩溃,支票账户中也不会损失200元,储蓄帐户也不会多出200元。因为事务最终没有提交,如果所有语句都顺利执行完成,并且事务也提交成功,那么就转换到了另一个一致性。尤其是在进行多表update的时候,根据业务的操作,在更新前是一个一致性状态,更新成功后又是另一个一致性状态(因为更新后的数据结果就是预期的结果)。

I、隔离性(isolation)

通常来说,一个事务所做的修改在最终提交以前,对其他事务是不可见的,在前面的例子中,当执行完第三条语句,第四条语句还未开始时,此时有另外一个帐户汇总程序开始运行,则其看到的支票账户的额并没有减去200元。在下一篇博文中将理解讨论隔离级别(isolation level),那时你会发现为什么要说“同样来说”是不可见的。当然,关于隔离级别见这篇文章《Mysql事务以及隔离级别》。

D、持久性(durability)

一旦事务提交,则其所做的修改就会永久保存到数据库中。此时系统崩溃也不会影响到已经修改的数据了。话说持久性是个模糊的概念,因为实际上持久性也分很多不同的级别。有些持久性策略能够提供非常强的安全保障,而有些未必。而且不可能有能做到100%的持久性保证的策略(如果数据库本身就能做到真正的持久性,那么还需要备份来增加持久性做什么呢?).

 

虽然事务的ACID特性可以增加事务处理过程中额外的安全性,但如同锁粒度的升级会增加系统开销一样(详见上一篇博文《了解mysql的并发控制》),这也会需要数据库系统做更多的工作。因此一个实现了ACID的数据库,相比没有实现ACID的数据库,通常会需要更强的CPU处理能力、更大的内存和更多的磁盘空间。当然用户可以根据业务是否需要事务处理,来选择合适的存储引擎。对于一些非事务性的存储引擎,可以获得更高的性能。

本篇重点理解ACID的概念,如有疑问,欢迎留言探讨。

了解mysql的并发控制

关于mysql的并发控制,主要涉及“锁”这个概念。本篇博文为纯文字理论,看似可能有点枯燥,但易于理解也易懂。

何谓“锁”,“锁”是用来干嘛的?我们先来看一个例子,我们以Unix系统的email box为例,这是一个邮箱,邮箱中的所有邮件都串行在一起,彼此首尾相连。这种格式对于读取和分析邮件信息非常友好,同时投递邮件也很容易,只要在文件末尾附加新的邮件内容即可。

那么,问题来了!

如果有两个进程同一时刻对同一个邮箱投递邮件,会发生什么情况?显然,邮箱的数据会被破坏,两份邮件的内容会交叉的附加在邮箱文件的末尾。那么我们就需要一种东西来控制邮件的投递,使得一个客户投递邮件时,将邮箱锁住,此时其他客户无法对此邮箱进行投递而进入等待,直到这个客户投递完毕解开锁才能进行投递。这就是所谓的“锁”,与mysql中的“锁”概念类似。

在mysql中主要有两种锁,一个是共享锁(shared lock),一个排他锁(exclusive lock),也分别称为读锁(read lock)和写锁(write lock)。

读锁是共享的,或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一个资源,而互不干扰。写锁则是排他的,也就是说一个写锁会阻塞其他的写锁和读锁,这是处于安全策略的考虑,只有这样,才能确保在给定的时间内只有一个用户能执行写入,并防止其他用户读取正在写入的同一资源。

锁粒度

mysql该如何加锁?修改数据时将全部数据都锁定?还是只是锁定要修改的那一块数据?加锁会不会影响性能?这就是要讨论的锁的粒度大小。

锁的粒度影响着共享资源的并发性,所以加锁时应该选择明确的锁定对象。尽量只锁定需要修改的部分数据,而不是所有数据。更理想的方式是,只对会修改的数据片进行精确的锁定。任何时候,在给定的资源上,锁定的数据量越少,则系统的并发程度越高。

however,加锁也是需要消耗资源的,锁的各种操作,包括获得锁、检查锁是否已经解除、释放锁等,都会增加系统开销。

因此,我们需要在追求并发性和数据安全性之间寻求平衡,即如何加锁,制定一个锁策略。

mysql中提供了多种的加锁选择,每种mysql存储引擎都可以实现自己的锁策略和锁粒度。在存储引擎的设计中,锁管理是个非常重要的决定。将锁粒度固定在某个级别,可以为某些特定的应用场景提供更好的性能,但同时却会失去对另外一些场景的良好支持。好在mysql支持多个存储引擎的架构,所以不需要单一的通用的解决方案。

下面介绍两种锁策略:

表锁(table lock)

表锁是mysql中最基本的锁策略,并且是开销最小的策略。表锁非常类似前文描述的邮箱加锁机制:它会锁定整张表。一个用户在对表进行写操作(插入、修改、删除)钱,需要先获得写锁,这会阻塞其他用户对该表的所有读写操作。只有没有写锁时,其他读取的用户才能获得读锁,读锁之间是不会相互阻塞的。

在特定的场景中,表锁也可能有良好的性能。写锁比读锁有更高的优先级,因此一个写锁请求可能会被插入到读锁队列的前面,而读锁则不能插入到写锁的前面。

尽管存储引擎可以管理自己的锁,mysql本身还是会使用各种有效的表锁来实现不同的目的。例如,服务器(详见前面的逻辑架构一文)会为诸如ALTER TABLE之类的语句使用表锁,而忽略存储引擎的锁机制。

行级锁(row-level lock)

行级锁可以最大程度的支持并发处理,同时也带来了最大的锁开销。在InnoDB以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现,而mysql服务器层没有实现。

 

以上这些就是对mysql并发控制的理解,通过锁机制来有效控制并发。

如有疑问欢迎留言探讨,毕竟我也是刚开始深入学习mysql。

简单了解mysql逻辑架构

2012031510324452

如图,mysql逻辑架构大致分为三层。

第一层,服务层(为客户端服务):这并不是mysql所独有的,很多基于网络的客户端/服务器的工具或者服务都有这样的类似架构,比如为请求做连接处理,授权认证,安全等。

第二层,核心层:这是一个比较有意思的部分,大多数mysql核心服务功能都在这一层,包括查询解析、分析、优化、缓存以及所有的内置函数(例如日期、时间、数学和加密函数),所有跨存储引擎的功能都在这一层实现:存储过程、触发器、视图等。

第三层,存储引擎层:存储引擎负责Mysql中数据的存储和提取,每个存储引擎都有它的优势和劣势。服务器(即上面的核心层)通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异,使得这些差异对上层的查询过程透明。存储引擎API包含几十个底层函数,用于执行注入“开始一个事务”或者“根据主键提取一行记录”等操作。但存储引擎不会去解析SQL(InnoDB是一个例外,它会解析外键定义,因为Mysql服务器本身没有实现该功能),不同存储引擎之间也不会相互通信,而只是简单的响应上层服务器的请求。

下面进一步了解第一层和第二层。

连接管理与安全性(第一层 服务层)

2012031510114191

  • 每个客户端链接都会在服务器进程中拥有一个线程,这个链接的查询只会在这个单独的线程中进行,即每个连接的查询都在一个进程中的线程完成。
  • 服务层会负责缓存线程,因此不需要为每个新建的连接创建一个线程或销毁已经使用的线程,类似线程池。

认证流程

2012031510220113

 

当客户端(应用)连接到mysql服务器时,服务器需要对其进行认证。认证基于原始主机信息和密码。如果使用了安全套接字(SSL)的方式连接,还可以使用X.509证书认证。一旦客户端连接成功,服务器会继续验证该客户端是否具有执行某个特定查询的权限。例如,是否允许客户端对world数据库的country数据表执行select语句。

优化与执行(第二层 核心层)

2012031510482383

 

mysql会解析查询,并创建内部数据结构(解析树),然后对其进行各种优化,包括重写查询、决定表的读取顺序,以及选择合适的索引等。
在解析查询之前,服务器会“询问”是否进行了查询缓存(只能缓存SELECT语句和相应结果)。缓存过的直接返回结果,未缓存的就需要进行解析查询,优化,重新执行返回结果。
参考文献《高性能Mysql》

PHP 5.3.6及以前版本的PDO的bindParam,bindValue潜在的安全隐患

PHP 5.3.6及以前版本的PDO的bindParam,bindValue潜在的安全隐患

使用PDO的参数化查询时,可以使用bindParam,bindValue为占位符绑定相应的参数或变量, 我们往往使用如下格式:

$statement->bindParam(1, $string);

$statement->bindParam(2, $int, PDO::PARAM_INT);

我在以前的文章中分析介绍过PDO的ATTR_EMULATE_PREPARES属性对PDO底层协议与MySQL Server通讯机制影响:

1. 默认情况下,PDO会使用DSN中指定的字符集对输入参数进行本地转义(PHP手册中称为native prepared statements),然后拼接成完整的SQL语句,发送给MySQL Server。这有些像我们平时程序中拼接变量到SQL再执行查询的形式。

这种情况下,PDO驱动能否正确转义输入参数,是拦截SQL注入的关键。然而PHP 5.3.6及老版本,并不支持在DSN中定义charset属性(会忽略之),这时如果使用PDO的本地转义,仍然可能导致SQL注入,解决办法后面会提到。

2. MySQL Server 5.1开始支持prepare预编译SQL机制,即SQL查询语句与参数分离提交,但这个特性需要客户端协议支持支持,目前所有版本的PHP均支持。
如果PDO客户端使用mysql Server的prepare功能,大致的交互过程是:
A. PDO将SQL模板发送给mysql server, SQL模板即包含参数占位符(问号或命名参数)的SQL语句

B. PDO不对输入参数作任何转义处理,将参数的位置,值,类型等等信息发送给MySQL Server

C. PDO客户端调用execute statement

D. MySQL Server 对B步骤提交的参数进行内部转义,并执行查询,返回结果给客户端。

看到没有,如果使用了mysql server prepare功能,则字符串的转义是由MySQL Server完成的。mysql会根据字符集(set names <charset>)对输入参数转换,确保没有注入产生。

 

PDO默认情况下使用了本地模拟prepare(并未使用MySQL server的prepare),如果要禁止PDO本地模拟行为而使用MySQL Server的prepare机制,则需要设置PDO的参数:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

如果你使用了PHP 5.3.6及以前版本,强烈推荐使用上述语句加强安全性。

如果你使用PHP 5.3.6+, 则请在DSN中指定 charset,是否设置上述参数,都是安全的。

 

好了,现在步入正题,假设有以下代码逻辑:

 

$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8",'root','');

$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);

$pdo->exec('set names utf8');

$id = '0 or 1 =1 order by id desc';

$sql = "select * from article where id = ?";

$statement = $pdo->prepare($sql);

$statement->bindParam(1, $id, PDO::PARAM_INT);

$statement->execute();

假设$id是外部变量(由其它函数传递,或用户提交),我们也没有使用intval对这两个参数进行强制类型转换(我们认为使用PDO绑定参数时已经指定参数类型为INT, 即PDO::PARAM_INT),期待PDO能使用我们指定的类型对其进行转义,但是事实上呢?却有太多不确定因素:

1. 以上代码实测在PHP 5.2.1造成了SQL注入

2. 以上代码在PHP 5.3.9下,没有造成任何SQL注入

那么,如果使用了存在bug的PHP版本,那么如何从根本上解决这个问题?

1. 设置PDO不使用本地模拟prepare, 即 $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

 

这样,即使不使用intval对输入进行转换,也可以确保是安全的。如果由于程序员遗忘没有使用intval转换,那么还是存在安全隐患的。

 

使用这种方式,更彻底,更安全。

原文地址:http://zhangxugg-163-com.iteye.com/blog/1855088

sql中where 1=1和 0=1 的作用

刚在写sql的时候思考了一下在不确定条件因素时的情况,之前看到别人使用过where 1=1这个条件, 这个条件始终为True,后来了解到在不定数量查询条件情况下,1=1可以很方便的规范语句。

一、不用where  1=1  在多条件查询中的困扰

  举个例子,如果您做查询页面,并且,可查询的选项有多个,同时,还让用户自行选择并输入查询关键词,那么,按平时的查询语句的动态构造,代码大体如下:

$sql=”select * from table where”;

if(!empty($age))
{
    $sql .= 'age='.$age';
}

if(!empty($address))
{
  $sql. = 'and address='.$address;
}

如果上述的两个if判断语句,均为true,即用户都输入了查询词,那么,最终的$sql动态构造语句变为:

$sql= 'select * from table where age=20 and address="常州"';

可以看得出来,这是一条完整的正确的SQL查询语句,能够正确的被执行,并根据数据库是否存在记录,返回数据。

②种假设

如果上述的两个if判断语句不成立,那么,最终的$sql动态构造语句变为:

$sql  = 'select * from table where';

现在,我们来看一下这条语句,由于where关键词后面需要使用条件,但是这条语句根本就不存在条件,所以,该语句就是一条错误的语句,肯定不能被执行,不仅报错,同时还不会查询到任何数据。

上述的两种假设,代表了现实的应用,说明,语句的构造存在问题,不足以应付灵活多变的查询条件。

二、使用 where  1=1  的好处

假如我们将上述的语句改为:

$sql=”select * from table where 1=1”;

if(!empty($age))
{
    $sql .= ' and age='.$age';
}

if(!empty($address))
{
  $sql. = 'and address='.$address;
}

①种假设

如果两个if都成立,那么,语句变为:

$sql = 'select * from table where 1=1 and age=12 and address="常州'",很明显,该语句是一条正确的语句,能够正确执行,如果数据库有记录,肯定会被查询到。

②种假设

如果两个if都不成立,那么,语句变为:

$sql = 'select * from table where 1=1',现在,我们来看这条语句,由于where 1=1 是为true的语句,因此,该条语句语法正确,能够被正确执行,它的作用相当于:$sql = 'select * from table',即返回表中所有数据。

言下之意就是:如果用户在多条件查询页面中,不选择任何字段、不输入任何关键词,那么,必将返回表中所有数据;如果用户在页面中,选择了部分字段并且输入了部分查询关键词,那么,就按用户设置的条件进行查询。

说到这里,不知道您是否已明白,其实,where 1=1的应用,不是什么高级的应用,也不是所谓的智能化的构造,仅仅只是为了满足多条件查询页面中不确定的各种因素而采用的一种构造一条正确能运行的动态SQL语句的一种方法。

where 1=0; 这个条件始终为false,结果不会返回任何数据,只有表结构,可用于快速建表

"select * from table where 1=0"; 该select语句主要用于读取表的结构而不考虑表中的数据,这样节省了内存,因为可以不用保存结果集。

create table newtable as select * from oldtable where 1=0;  创建一个新表,而新表的结构与查询的表的结构是一样的。

Mysql事务以及隔离级别

数据库事务概念

数据库事务必须同时满足 4 个特性:原子性(Atomic)、一致性(Consistency)、隔离性(Isolation)和持久性(Durabiliy),简称为ACID。下面是对每个特性的说明。

  • 原子性:表示组成一个事务的多个数据库操作要么全部成功、要么全部失败。
  • 一致性:事务操作成功后,数据库所处的状态和它的业务规则是一致的,即数据不会被破坏。如从A账户转账100元到B账户,不管操作成功与否,A和B的存款总额是不变的。
  • 隔离性:在并发数据操作时,不同的事务拥有各自的数据空间,它们的操作不会对对方产生干扰。准确地说,并非要求做到完全无干扰,数据库规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性越好,但并发性越弱。
  • 持久性:一旦事务提交成功后,事务中所有的数据操作都必须被持久化到数据库中,即使提交事务后,数据库马上崩溃,在数据库重启时,也必须能保证能够通过某种机制恢复数据。

其实这四个特性,原子性是最终目的。

 

数据并发的问题

一个数据库可能拥有多个访问客户端,这些客户端都可以并发方式访问数据库。数据库中的相同数据可能同时被多个事务访问,如果没有采取必要的隔离措施,就会导致各种并发问题,破坏数据的完整性。这些问题可以归结为5类,包括3类数据读问题( 脏读、 不可重复读和 幻象读)以及2类数据更新问题( 第一类丢失更新和 第二类丢失更新)。下面,我们分别通过实例讲解引发问题的场景。

脏读(dirty read)

A事务读取B事务尚未提交的更改数据,并在这个数据的基础上操作。如果恰巧B事务回滚,那么A事务读到的数据根本是不被承认的。来看取款事务和转账事务并发时引发的脏读场景:

15150645_B8Se

 

在这个场景中,B希望取款500元而后又撤销了动作,而A往相同的账户中转账100元,就因为A事务读取了B事务尚未提交的数据,因而造成账户白白丢失了500元。在Oracle数据库中,不会发生脏读的情况。

不可重复读(unrepeatable read)

不可重复读是指 A事务读取了B事务已经提交的更改数据。假设A在取款事务的过程中,B往该账户转账100元,A两次读取账户的余额发生不一致:

15150645_B8Se

 

在同一事务中,T4时间点和T7时间点读取账户存款余额不一样。

幻象读(phantom read)

A事务读取B事务提交的新增数据,这时A事务将出现幻象读的问题。幻象读一般发生在计算统计数据的事务中,举一个例子,假设银行系统在同一个事务中,两次统计存款账户的总金额,在两次统计过程中,刚好新增了一个存款账户,并存入100元,这时,两次统计的总金额将不一致:

15150645_d6yw

 

如果新增数据刚好满足事务的查询条件,这个新数据就进入了事务的视野,因而产生了两个统计不一致的情况。

幻象读和不可重复读是两个容易混淆的概念,前者是指读到了其他已经提交事务的新增数据,而后者是指读到了已经提交事务的更改数据(更改或删除),为了避免这两种情况,采取的对策是不同的,防止读取到更改数据,只需要对操作的数据添加行级锁,阻止操作中的数据发生变化,而防止读取到新增数据,则往往需要添加表级锁——将整个表锁定,防止新增数据(Oracle使用多版本数据的方式实现)。

第一类丢失更新

A事务撤销时,把已经提交的B事务的更新数据覆盖了。这种错误可能造成很严重的问题,通过下面的账户取款转账就可以看出来:

15150645_d6yw

A事务在撤销时,“不小心”将B事务已经转入账户的金额给抹去了。

第二类丢失更新

A事务覆盖B事务已经提交的数据,造成B事务所做操作丢失:

15150645_d6yw

 

上面的例子里由于支票转账事务覆盖了取款事务对存款余额所做的更新,导致银行最后损失了100元,相反如果转账事务先提交,那么用户账户将损失100元。

四种隔离级别

尽管数据库为用户提供了锁的DML操作方式,但直接使用锁管理是非常麻烦的,因此数据库为用户提供了自动锁机制。只要用户指定会话的事务隔离级别,数据库就会分析事务中的SQL语句,然后自动为事务操作的数据资源添加上适合的锁。此外数据库还会维护这些锁,当一个资源上的锁数目太多时,自动进行锁升级以提高系统的运行性能,而这一过程对用户来说完全是透明的。
ANSI/ISO SQL 92标准定义了4个等级的事务隔离级别:

15150645_d6yw

 

事务的隔离级别和数据库并发性是对立的,两者此增彼长。一般来说,使用READ UNCOMMITED隔离级别的数据库拥有最高的并发性和吞吐量,而使用SERIALIZABLE隔离级别的数据库并发性最低。

Mysql的默认隔离级别时Repeatable Read,即可重复读。