MySQL事务

 

MySQL事务

事务处理机制在程序开发过程中有着非常重要的作用,它可以使整个系统更加安全,例如在银行处理转账业务时,如果A账户中的金额刚被发出,而B账户还没来得及接收就发生停电,这会给银行和个人带来很大的经济损失。采用事务处理机制,一旦在转账过程中发生意外,则程序将回滚,不做任何处理。

实例265:使用事务处理技术实现关联表间信息的删除

实例说明

MySQL数据库中的表可以通过主键相互关联,例如学生成绩管理系统中,学生表和成绩表可以通过id实现关联,所以在删除某个学生信息时,只需删除这两个表中为该id的所有记录。但是,当对成绩表实现删除相关id工作后,还没来得及删除学生信息表中该学生的信息时就发生停电等意外,则再重新查找该学生的成绩时,是无法查找到该学生成绩的。

本实例将采用事务处理方式,对学生信息表和学生成绩表中的数据进行删除,运行本实例,学生信息及学生成绩信息分别如图9.18及图9.19所示。当删除图9.18中的学生信息后,查看学生成绩信息可以发现与该学生对应的成绩全部删除。

图9.19 查看学生信息
图9.18 查看学生成绩

技术要点

实现事务的处理可以通过PHP预定义类mysqli的以下方法实现。
autocommit(boolean):该方法用于限定查询结果是否自动提交,如果该方法的参数为true则自动提交,如果参数为false则关闭自动提交。MySQL数据库默认为自动提交。
rollback():利用mysqli类中的该方法可以实现事务的回滚。
commit():利用该方法可以实现提交所有查询。

实现过程

(1)建立数据库及数据表,并实现与数据库的连接。实现与数据库建立连接的代码如下:

<?php
include_once("conn.php");
$sql=$conn->query("select * from tb_stu"); 
$info=$sql->fetch_array(MYSQLI_ASSOC);
if($info==NULL)
{
echo "暂无学生信息!";
} 
else
{
do
{
?> 
<tr>
<td height="25" bgcolor="#FFFFFF"><div align="center">
<?php echo $info[sname];?></div></td>
<td height="25" bgcolor="#FFFFFF"><div align="center">
<?php echo $info[sno];?></div></td>
<td height="25" bgcolor="#FFFFFF"><div align="center">
<?php echo $info[sage];?></div></td>
<td height="25" bgcolor="#FFFFFF"><div align="center">
<?php echo $info[saddress];?></div></td>
<td height="25" bgcolor="#FFFFFF"><div align="center">
<?php echo $info[ssfzh];?></div></td>
<td bgcolor="#FFFFFF"><div align="center">
<a href="javascript:if(window.confirm('确定删除该学生信息么?')==true)
{window.location.href='delete.php?id=<?php echo $info[id];?>';}">删除</a></div></td>
</tr>
<?php
}
while($info=$sql->fetch_array(MYSQLI_ASSOC));
}
?>


(2)显示所有学生基本信息。代码如下:
在实现该模块功能时,可以利用JavaScript实现该页与delete.php页面的信息传递。代码如下:

<a href="javascript:if(window.confirm('确定删除该学生信息么?')==true)
{window.location.href='delete.php?id=<?php echo $info[id];?>';}">删除</a>


window对象的confirm()方法用于弹出一个对话框,用于提示用户是否真正删除某学生的所有信息。
(3)利用事务处理机制对学生基本信息进行删除。代码如下:

<?php
$id=$_GET[id];
include_once("conn.php");
$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 "<script>window.location.href='index.php';</script>";
?>

举一反三

根据本实例,读者可以:
实现学生信息的批量删除。
实现银行的转账处理。

实例262:使用事务处理技术实现银行的安全转账

实例说明

在实现银行转账过程中,发生意外是在所难免的,为了避免因意外而造成不必要的损失,在银行转账时经常使用事务处理方式。运行本实例,如图9.20所示,在图中的文本框中输入要转给B账户的金额后,单击“转账”按钮即可实现转账。

 

图9.20 银行转账

技术要点

本实例的关键技术是如何应用PHP的事务处理机制处理转账过程中可能遇到的意外。事务处理方式是个不错的选择。本实例应用事务处理技术实现转账的代码如下:

$tob=$_POST[tob];
include_once("conn.php");
$conn->autocommit(false);
if(!$conn->query("update tb_zy set money=money-'".$tob."' where flag='mrsoft'"))
{
$conn->rollback();
}
if(!$conn->query("update tb_zy set money=money+'".$tob."' where flag='mr'"))
{
$conn->rollback();
}
$conn->commit();
$conn->autocommit(true);
echo "<SCRIPT>window.location.href='index.php';</SCRIPT>";
?>


首先调用mysqli类的autocommit()方法关闭数据库的自动提交,然后减少A账户一定数量的金额,并判断查询是否顺利执行,如果在此过程中发生意外,则通过调用mysqli类的rollback()方法回滚,不做任何处理,反之为B账户增加相同的金额。最后通过调用mysqli类的commit()方法提交查询,实现转账。

实现过程

(1)利用mysqli扩展技术实现与MySQL数据库的连接。代码如下:

$conn=new mysqli("localhost","root","root","db_database09");
$conn->query("set names gb2312");
?>

(2)查询A账户与B账户的现有金额并在页面中显示。代码如下:

include_once("conn.php");
$sql=$conn->query("select * from tb_zy where flag='mrsoft'");
$info=$sql->fetch_array(MYSQLI_ASSOC);
$sql1=$conn->query("select * from tb_zy where flag='mr'");
$info1=$sql1->fetch_array(MYSQLI_ASSOC);
?>

举一反三

根据本实例,读者可以:
实现多用户同时转账。
实现自动转账。

MySQL与事务

先来明确一下事务涉及的相关知识:

事务都应该具备ACID特征。所谓ACID是Atomic(原子性),Consistent(一致性),Isolated(隔离性),Durable(持续性)四个词的首字母所写,下面以“银行转帐”为例来分别说明一下它们的含义:

原子性:组成事务处理的语句形成了一个逻辑单元,不能只执行其中的一部分。换句话说,事务是不可分割的最小单元。比如:银行转帐过程中,必须同时从一个帐户减去转帐金额,并加到另一个帐户中,只改变一个帐户是不合理的。

一致性:在事务处理执行前后,数据库是一致的。也就是说,事务应该正确的转换系统状态。比如:银行转帐过程中,要么转帐金额从一个帐户转入另一个帐户,要么两个帐户都不变,没有其他的情况。

隔离性:一个事务处理对另一个事务处理没有影响。就是说任何事务都不可能看到一个处在不完整状态下的事务。比如说,银行转帐过程中,在转帐事务没有提交之前,另一个转帐事务只能处于等待状态。

持续性:事务处理的效果能够被永久保存下来。反过来说,事务应当能够承受所有的失败,包括服务器、进程、通信以及媒体失败等等。比如:银行转帐过程中,转帐后帐户的状态要能被保存下来。

再来看看哪些问题会用到事务处理:

这里不说“银行转帐”的例子了,说一个大家实际更容易遇到的“网上购书”的例子。先假设一下问题的背景:网上购书,某书(数据库编号为123)只剩最后一本,而这个时候,两个用户对这本书几乎同时发出了购买请求,让我们看看整个过程:

在具体分析之前,先来看看数据表的定义:

-------------------------------------------------------------------------------

create table book
(
book_id unsigned int(10) not null auto_increment,
book_name varchar(100) not null,
book_price float(5, 2) not null, #我假设每本书的价格不会超过999.99元
book_number int(10) not null,
primary key (book_id)
)
type = innodb; #engine = innodb也行

-------------------------------------------------------------------------------

对于用户甲来说,他的动作稍微比乙快一点点,其购买过程所触发的动作大致是这样的:

-------------------------------------------------------------------------------

1. SELECT book_number FROM book WHERE book_id = 123;

book_number大于零,确认购买行为并更新book_number

2. UPDATE book SET book_number = book_number - 1 WHERE book_id = 123;

购书成功

-------------------------------------------------------------------------------

而对于用户乙来说,他的动作稍微比甲慢一点点,其购买过程所触发的动作和甲相同:

-------------------------------------------------------------------------------

1. SELECT book_number FROM book WHERE book_id = 123;

这个时候,甲刚刚进行完第一步的操作,还没来得及做第二步操作,所以book_number一定大于零

2. UPDATE book SET book_number = book_number - 1 WHERE book_id = 123;

购书成功

-------------------------------------------------------------------------------

表面上看甲乙的操作都成功了,他们都买到了书,但是库存只有一本,他们怎么可能都成功呢?再看看数据表里book_number的内容,已经变成 “-1”了,这当然是不能允许的(实际上,声明这样的列类型应该加上unsigned的属性,以保证其不能为负,这里是为了说明问题所以没有这样设置)

好了,问题陈述清楚了,再来看看怎么利用事务来解决这个问题,打开MySQL手册,可以看到想用事务来保护你的SQL正确执行其实很简单,基本就是三个语句:开始,提交,回滚。

-------------------------------------------------------------------------------

开始:START TRANSACTION或BEGIN语句可以开始一项新的事务

提交:COMMIT可以提交当前事务,是变更成为永久变更

回滚:ROLLBACK可以回滚当前事务,取消其变更

此外,SET AUTOCOMMIT = {0 | 1}可以禁用或启用默认的autocommit模式,用于当前连接。

-------------------------------------------------------------------------------

那是不是只要用事务语句包一下我们的SQL语句就能保证正确了呢?比如下面代码:

-------------------------------------------------------------------------------

BEGIN;

SELECT book_number FROM book WHERE book_id = 123;

// ...

UPDATE book SET book_number = book_number - 1 WHERE book_id = 123;

COMMIT;

-------------------------------------------------------------------------------

答案是否定了,这样依然不能避免问题的发生,如果想避免这样的情况,实际应该如下:

-------------------------------------------------------------------------------

BEGIN;

SELECT book_number FROM book WHERE book_id = 123 FOR UPDATE;

// ...

UPDATE book SET book_number = book_number - 1 WHERE book_id = 123;

COMMIT;

-------------------------------------------------------------------------------

由于加入了FOR UPDATE,所以会在此条记录上加上一个行锁,如果此事务没有完全结束,那么其他的事务在使用SELECT ... FOR UPDATE请求的时候就会处于等待状态,直到上一个事务结束,它才能继续,从而避免了问题的发生,需要注意的是,如果你其他的事务使用的是不带FOR UPDATE的SELECT语句,将得不到这种保护。

最后看看PHP + MySQL事务操作的代码演示:

实际LAMP应用中,一般PHP使用AdoDB操作MySQL,下面给出AdoDB相应的代码方便大家查阅:

-------------------------------------------------------------------------------

<?php
// ...

$adodb->startTrans
();

//实际,getOne所调用的查询也可以直接放到rowLock来进行,这里只是为了演示效果能更明显些。

$adodb->rowLock('book', 'book_id = 123'
);

$bookNumber = $adodb->getOne("SELECT book_number FROM book WHERE book_id = 123"
);

$adodb->execute("UPDATE book SET book_number = book_number - 1 WHERE book_id = 123"
);

$adodb->completeTrans
();

// ...
?>

-------------------------------------------------------------------------------

其中,rowLock的方法就是调用的FOR UPDATE来实现的行锁,你可能会想把“FOR UPDATE”直接写到$adodb->getOne()调用的那条SQL语句里面去实现行锁的功能,不错,那样确实可以,但是并不是所有的数据库都使用“FOR UPDATE”语法来实现行锁功能,比如Sybase使用“HOLDLOCK”的语法来实现行锁功能,所以为了你的数据库抽象层保持可移植性,我还是劝你用rowLock来实现行锁功能,至于可移植性就交给AdoDB好了,嗯,有点扯远了,今儿就说到这里了。

-------------------------------------------------------------------------------

附:

AdoDB中存在一个setTransactionMode()方法,能够设置事务的隔离级别,如下:

SetTransactionMode allows you to pass in the transaction mode to use for all subsequent transactions for that connection session. Note: if you have persistent connections and using mysql or mssql, you might have to explicitly reset your transaction mode at the beginning of each page request. This is only supported in postgresql, mssql, mysql with InnoDB and oci8 currently. For example:

$db->SetTransactionMode("SERIALIZABLE");
$db->BeginTrans();
$db->Execute(...); $db->Execute(...);
$db->CommiTrans();

$db->SetTransactionMode(""); // restore to default
$db->StartTrans();
$db->Execute(...); $db->Execute(...);
$db->CompleteTrans();

Supported values to pass in:

* READ UNCOMMITTED (allows dirty reads, but fastest)
* READ COMMITTED (default postgres, mssql and oci8)
* REPEATABLE READ (default mysql)
* SERIALIZABLE (slowest and most restrictive)

You can also pass in database specific values such as 'SNAPSHOT' for mssql or 'READ ONLY' for oci8/postgres.

PHP的事务处理 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数据库默认的引擎是MyISAM,这种引擎不支持事务!如果要让MYSQL支持事务,可以自己手动修改:
方法如下:1.修改c:\appserv\mysql\my.ini文件,找到skip-InnoDB,在前面加上#,后保存文件。
2.在运行中输入:services.msc,重启mysql服务。
3.到phpmyadmin中,mysql->show engines;(或执行mysql->show variables like 'have_%'; ),查看InnoDB为YES,即表示数据库支持InnoDB了。
也就说明支持事务transaction了。
4.在创建表时,就可以为Storage Engine选择InnoDB引擎了。如果是以前创建的表,可以使用mysql->alter table table_name type=InnoDB;
或 mysql->alter table table_name engine=InnoDB;来改变数据表的引擎以支持事务。
*/
/*************** transaction--1 ***************/
$conn = mysql_connect('localhost','root','root') or die ("数据连接错误!!!");
mysql_select_db('test',$conn);
mysql_query("set names 'GBK'"); //使用GBK中文编码;
//开始一个事务
mysql_query("BEGIN"); //或者mysql_query("START TRANSACTION");
$sql = "INSERT INTO `user` (`id`, `username`, `sex`) VALUES (NULL, 'test1', '0')";
$sql2 = "INSERT INTO `user` (`did`, `username`, `sex`) VALUES (NULL, 'test1', '0')";//这条我故意写错
$res = mysql_query($sql);
$res1 = mysql_query($sql2);
if($res && $res1){
mysql_query("COMMIT");
echo '提交成功。';
}else{
mysql_query("ROLLBACK");
echo '数据回滚。';
}
mysql_query("END");
/**************** transaction--2 *******************/
/*方法二*/
mysql_query("SET AUTOCOMMIT=0"); //设置mysql不自动提交,需自行用commit语句提交
$sql = "INSERT INTO `user` (`id`, `username`, `sex`) VALUES (NULL, 'test1', '0')";
$sql2 = "INSERT INTO `user` (`did`, `username`, `sex`) VALUES (NULL, 'test1', '0')";//这条我故意写错
$res = mysql_query($sql);
$res1 = mysql_query($sql2);
if($res && $res1){
mysql_query("COMMIT");
echo '提交成功。';
}else{
mysql_query("ROLLBACK");
echo '数据回滚。';
}
mysql_query("END"); //事务处理完时别忘记mysql_query("SET AUTOCOMMIT=1");自动提交


/******************对于不支持事务的MyISAM引擎数据库可以使用表锁定的方法:********************/


//MyISAM & InnoDB 都支持,
/*
LOCK TABLES可以锁定用于当前线程的表。如果表被其它线程锁定,则造成堵塞,直到可以获取所有锁定为止。
UNLOCK TABLES可以释放被当前线程保持的任何锁定。当线程发布另一个LOCK TABLES时,或当与服务器的连接被关闭时,所有由当前线程锁定的表被隐含地解锁。
*/

mysql_query("LOCK TABLES `user` WRITE");//锁住`user`表
$sql = "INSERT INTO `user` (`id`, `username`, `sex`) VALUES (NULL, 'test1', '0')";
$res = mysql_query($sql);
if($res){
echo '提交成功。!';
}else{
echo '失败!';
}
mysql_query("UNLOCK TABLES");//解除锁定

php中的事务处理 mysqli

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

<?php
$db = new mysqli("localhost","root","","test"); //连接数据库

$db->autocommit(false); //设置为非自动提交——事务处理

$sql1  = "INSERT INTO `test`.`test1` (`name` )VALUES ('1' )";

$result1 = $db->query($sql1);

$sql2  = "INSERT INTO `test`.`test2` (`a` )VALUES ('1')";

$result2 = $db->query($sql2);

if ($result1 && $result2) {

$db->commit();  //全部成功,提交执行结果

echo '提交';

} else {

$db->rollback(); //有任何错误发生,回滚并取消执行结果

echo '回滚';

}

$db->autocommit(true); //设置为非自动提交——事务处理

$db->close();

?>