php mysql事务详解

@航空母舰

已于 2022-02-16 17:39:34 修改

阅读量1.3k

点赞数

分类专栏： MYSQL 面试文章标签：异常处理事物

于 2011-09-02 22:26:26 首次发布

本文链接：https://blog.csdn.net/hudeyong926/article/details/99540410

版权

MYSQL 同时被 2 个专栏收录

62 篇文章 1 订阅

订阅专栏

面试

50 篇文章 0 订阅

订阅专栏

事务是由一组SQ语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

一、事务四大属性

原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
隔离性（Isoation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
持久性（Durabe）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

二、事务的隔离级别

1、为什么要设置隔离级别

在数据库操作中，在并发的情况下可能出现如下问题：

更新丢失（Lost update）
如果多个线程操作，基于同一个查询结构对表中的记录进行修改，那么后修改的记录将会覆盖前面修改的记录，前面的修改就丢失掉了，这就叫做更新丢失。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。
第1类丢失更新：事务A撤销时，把已经提交的事务B的更新数据覆盖了。

第2类丢失更新：事务A覆盖事务B已经提交的数据，造成事务B所做的操作丢失。

解决方法：对行加锁，只允许并发一个更新事务。
脏读（Dirty Reads）
脏读（Dirty Read）：A事务读取B事务尚未提交的数据并在此基础上操作，而B事务执行回滚，那么A读取到的数据就是脏数据。

解决办法：如果在第一个事务提交前，任何其他事务不可读取其修改过的值，则可以避免该问题。
不可重复读（Non-repeatable Reads）
一个事务对同一行数据重复读取两次，但是却得到了不同的结果。事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。
解决办法：如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题。
幻象读
指两次执行同一条 select 语句会出现不同的结果，第二次读会增加一数据行，并没有说这两次执行是在同一个事务中。一般情况下，幻象读应该正是我们所需要的。但有时候却不是，如果打开的游标，在对游标进行操作时，并不希望新增的记录加到游标命中的数据集中来。隔离级别为游标稳定性的，可以阻止幻象读。例如：目前工资为1000的员工有10人。那么事务1中读取所有工资为1000的员工，得到了10条记录；这时事务2向员工表插入了一条员工记录，工资也为1000；那么事务1再次读取所有工资为1000的员工共读取到了11条记录。

解决办法：如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可避免该问题。

正是为了解决以上情况，数据库提供了几种隔离级别。

2、事务的隔离级别

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted(未授权读取、读未提交)、Read committed（授权读取、读提交）、Repeatable read（可重复读取）、Serializable（序列化），这四个级别可以逐个解决脏读、不可重复读、幻象读这几类问题。

Read uncommitted(未授权读取、读未提交)：
如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。这样就避免了更新丢失，却可能出现脏读。也就是说事务B读取到了事务A未提交的数据。
Read committed（授权读取、读提交）：
读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。该隔离级别避免了脏读，但是却可能出现不可重复读。事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。
Repeatable read（可重复读取）：
可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，即使第二个事务对数据进行修改，第一个事务两次读到的的数据是一样的。这样就发生了在一个事务内两次读到的数据是一样的，因此称为是可重复读。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。这样避免了不可重复读取和脏读，但是有时可能出现幻象读。（读取数据的事务）这可以通过“共享读锁”和“排他写锁”实现。
Serializable（序列化）：
提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。大多数数据库的默认级别就是Read committed，比如Sql Server , Oracle。MySQL的默认隔离级别就是Repeatable read。

三、悲观锁和乐观锁

虽然数据库的隔离级别可以解决大多数问题，但是灵活度较差，为此又提出了悲观锁和乐观锁的概念。

1、悲观锁

悲观锁，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度。因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制。也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统的数据访问层中实现了加锁机制，也无法保证外部系统不会修改数据。

使用场景举例：以MySQL InnoDB为例

商品t_items表中有一个字段status，status为1代表商品未被下单，status为2代表商品已经被下单（此时该商品无法再次下单），那么我们对某个商品下单时必须确保该商品status为1。假设商品的id为1。
如果不采用锁，那么操作方法如下：

//1.查询出商品信息
select status from  t_items where id=1;
//2.根据商品信息生成订单,并插入订单表 t_orders 
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_items set status=2;

但是上面这种场景在高并发访问的情况下很可能会出现问题。例如当第一步操作中，查询出来的商品status为1。但是当我们执行第三步Update操作的时候，有可能出现其他人先一步对商品下单把t_items中的status修改为2了，但是我们并不知道数据已经被修改了，这样就可能造成同一个商品被下单2次，使得数据不一致。所以说这种方式是不安全的。

使用悲观锁来解决问题

在上面的场景中，商品信息从查询出来到修改，中间有一个处理订单的过程，使用悲观锁的原理就是，当我们在查询出t_items信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么在这个过程中，因为t_items被锁定了，就不会出现有第三者来对其进行修改了。需要注意的是，要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。我们可以使用命令设置MySQL为非autocommit模式：set autocommit=0;
设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：

//0.开始事务
start transaction;
//1.查询出商品信息
select status from t_items where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_items set status=2;
//4.提交事务
commit;

上面的begin/commit为事务的开始和结束，因为在前一步我们关闭了mysql的autocommit，所以需要手动控制事务的提交。
上面的第一步我们执行了一次查询操作：select status from t_items where id=1 for update;与普通查询不一样的是，我们使用了select…for update的方式，这样就通过数据库实现了悲观锁。此时在t_items表中，id为1的那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。需要注意的是，在事务中，只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 操作同一个数据时才会等待其它事务结束后才执行，一般SELECT ... 则不受此影响。拿上面的实例来说，当我执行select status from t_items where id=1 for update;后。我在另外的事务中如果再次执行select status from t_items where id=1 for update;则第二个事务会一直等待第一个事务的提交，此时第二个查询处于阻塞的状态，但是如果我是在第二个事务中执行select status from t_items where id=1;则能正常查询出数据，不会受第一个事务的影响。

Row Lock与Table Lock

使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键或者索引，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。举例如下：
1、select * from t_items where id=1 for update;
这条语句明确指定主键（id=1），并且有此数据（id=1的数据存在），则采用row lock。只锁定当前这条数据。
2、select * from t_items where id=3 for update;
这条语句明确指定主键，但是却查无此数据，此时不会产生lock（没有元数据，又去lock谁呢？）。
3、select * from t_items where name='手机' for update;
这条语句没有指定数据的主键，那么此时产生table lock，即在当前事务提交前整张数据表的所有字段将无法被查询。
4、select * from t_items where id>0 for update; 或者select * from t_items where id<>1 for update;（注：<>在SQL中表示不等于）
上述两条语句的主键都不明确，也会产生table lock。
5、select * from t_items where status=1 for update;（假设为status字段添加了索引）
这条语句明确指定了索引，并且有此数据，则产生row lock。
6、select * from t_items where status=3 for update;（假设为status字段添加了索引）
这条语句明确指定索引，但是根据索引查无此数据，也就不会产生lock。

悲观锁小结
悲观锁并不是适用于任何场景，它也有它存在的一些不足，因为悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。如果加锁的时间过长，其他用户长时间无法访问，影响了程序的并发访问性，同时这样对数据库性能开销影响也很大，特别是对长事务而言，这样的开销往往无法承受。所以与悲观锁相对的，我们有了乐观锁。

2、乐观锁

乐观锁（ Optimistic Locking ）相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以只会在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回用户错误的信息，让用户决定如何去做。实现乐观锁一般来说有以下2种方式：

- 使用版本号
  使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
- 使用时间戳
  乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

事物一般和异常处理结合

<?php
$lnk = mysql_connect("localhost", "root", "");
mysql_select_db("test");

mysql_query("BEGIN");
try {
	mysql_query("INSERT INTO test VALUES(1, 'yangjun')");
	mysql_query("INSERT INTO test VALUES(1, 'yangjun')");
	mysql_query("INSERT INTO test VALUES(2, '杨俊')");
	
	mysql_query("COMMIT"); //全部成功，提交执行结果
}catch (Exception $e){
	//$e->getMessage();
	mysql_query("ROLLBACK"); //有任何错误发生，回滚并取消执行结果
}

在这里要注意，

MyISAM：不支持事务，用于只读程序提高性能
InnoDB：支持ACID事务、行级锁、并发
Berkeley DB：支持事务

还有一点要注意：MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句，从而有效的将每条语句独立为一个事务。
但往往，我们需要在使用事务的时候，是需要执行多条sql语句的。这就需要我们手动设置MySQL的autocommit属性为0，默认为1。
同时，使用START TRANSACTION语句显式的打开一个事务。如上面的示例。
如果不这样做，会有什么结果呢？

我们将上面第二段代码中 //mysql_query(‘SET autocommit=0′); 和 // mysql_query($sql3); 注释去掉，然后执行。
此时，mysql_query($sql3) 执行就不会insert到数据库中。

//对于不支持事务的MyISAM引擎数据库可以使用表锁定的方法：
$sql_1=" LOCK TABLES test WRITE ";
mysql_query($sql_1);

$sql_2=" INSERT INTO test VALUES('".$a."','".$b."') ";
if(mysql_query($sql_2)){
  echo 'successful!';
}else{
  echo 'Unsuccessful!';
 }
$sql_3=" UNLOCK TABLES ";
mysql_query($sql_3);

如果我们将 // mysql_query(‘SET autocommit=1′); 本句注释去掉，那么mysql_query($sql3); 就会执行成功。

通常COMMIT或ROLLBACK语句执行时才完成一个事务，但是有些DDL语句等会隐式触发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

我们再来举个例子看下。

$sql1 = 'create table ScoreDetail_new(id int)';
$sql2 = 'rename table ScoreDetail to ScoreDetail_bak';
$sql3  = 'rename table ScoreDetail_new to ScoreDetail';

$mysqli = new mysqli('localhost','root','','DB_Lib2Test');
$mysqli->autocommit(false);//开始事物
$mysqli->query($sql1);
$mysqli->query($sql2);
$mysqli->query($sql3);
if(!$mysqli->errno){
  $mysqli->commit();
  echo 'ok';
}else{
 echo 'err';
  $mysqli->rollback();
}

在上面的示例中，假如$sql2执行出错了，$sql1照样会执行的。为什么呢？
因为rename在执行的时候，mysql默认会先执行commit，再执行rename。

Mysql不支持嵌套事务

set autocommit=0; 
start TRANSACTION ; 
insert into person (firstName,lastName) VALUES ('tr1','tr2'); 

	START transaction ; 
	--这个时候前一个事务已经被commit了. insert了一次. 
	insert into person (firstName,lastName) VALUES ('tr1','tr2'); 
	commit; 
	--又insert了一遍 
ROLLBACK;

START TRANSACTION：开始事务，如果已经有一个事务在运行，则会触发一个隐藏的COMMIT 事物嵌套：程序实现

protected $transactions = 0;
public function beginTransaction() {
    ++$this->transactions;
    if ($this->transactions == 1) {
        $this->pdo->beginTransaction();
    }
}

这个函数的第一行用一个transactions来标识当前嵌套的级别，如果是1，那就用默认的方法执行一下START TRANSACTION就ok了，然后看下rollBack函数

public function rollBack() {
    if ($this->transactions == 1) {
        $this->transactions = 0;
        $this->pdo->rollBack();
    } else {
        --$this->transactions;
    }
}

可以看到处理的方式也很简单，如果transactions是1，直接rollback，然后我们继续看下commit函数

public function commit() {
    if ($this->transactions == 1) $this->pdo->commit();
    --$this->transactions;
}

在嵌套的内层里面实际上是木有真正的事务的，只有最外层一个整体的事务。

创建可抛出一个异常的函数

<?php 
//创建可抛出一个异常的函数
function checkNum($number){
	if($number>1){
		throw new SqlException("Value must be 1 or below");
	}
	return true;
}
//在 "try" 代码块中触发异常
try{
	checkNum(2);
	//如果异常被抛出，那么下面一行代码将不会被输出
	echo 'If you see this, the number is 1 or below';
}catch(Exception $e){
	//捕获异常
	echo 'Message: ' .$e->getMessage();
}catch(SqlException $e){
	//捕获异常
	echo 'Message: ' .$e->getMessage();
}
class SqlException extends Exception{}
?>

四、跨库事务处理

try {
    $this->conn1->beginTransaction();
    $this->conn2->beginTransaction();
    //db1批处理
    $this->conn1->insert();
    $this->conn1->delete();
    //db2批处理
    $this->conn2->insert();
    $this->conn2->update();

    $this->conn->commit();
    $this->conn2->commit();
} catch (\Exception $ex) {
    $msg = $ex->getMessage();

    $this->conn1->rollBack();
    $this->conn2->rollBack();
}