mysql事务处理学习笔记

什么是事务

在MySQL环境中，一个事务由作为一个单独单元的一个或者多个sql语句组成。这个单元中的每个sql语句是互相依赖的， 而且单元作为一个整体是不可分割的。如果单元中的一个语句不能成功完成，整个单元都会回滚，所有影响到的数据将返回到 事务开始之前的状态。因而，只有事务中的所有语句都被成功的执行才能说这个事务被成功执行。

事务的四个特性：

原子性，每个事务都必须被认为是一个不可分割的单元。

一致性，不管事务是完全成功还是中途失败，当事务使系统处于一致的状态时存在一致性。

孤立性，每个事务在它自己的空间发生，和其他发生在系统中的事务隔离，而且事务的结果只有在它完全被执行时才能看到。

持久性，即使系统崩溃，一个提交的事务扔在坚持。

生命周期

为了初始化一个事务，并告诉MySQL所有随后的sql语句需要被认为是一个单元，MySQL提供了start transaction命令来标记 一个事务的开始。也可以使用begin或者begin work命令来初始化一个事务。通常情况下，start transction命令后跟随的 是组成事务的sql语句。

一旦sql语句被执行，就可使用commit命令来把整个事务保存在磁盘上，或者使用rollback命令来撤销所有的变化。 如果事务包括事务表和非事务表的变化，非事务表的事务处理部分是不能使用rollback命令撤销的。在这种情况下， MySQL将会返回一个错误，通知出现一个不完全撤销。

commit命令标记了事务块的结束。

控制事务行为

MySQL提供了两个变量来控制事务行为：autocommit变量和transaction isolation level变量。

自动提交，默认情况下，MySQL的sql查询一旦被执行，就会自动向数据库提交结果。这种默认的行为可以通过特定的 autocommit变量来进行修改。设置set autocommit=0，随后表的更新将不会被保存，直到明确发出一个commit命令。

事务孤立级，MySQL默认为repeatable read孤立级，可以使用set来修改

事务和性能

因为支持事务的数据库在保持不同用户彼此孤立方面要比非事务数据库难，所以自然的反应了系统的性能。

我们需要做一些事情来保证事务不会向系统添加不适当的负担。

使用小事务，两个普遍的策略

1：保证所有要求的用户输入在发出start transaction命令之前都是可行的

2：尝试把大的事务分成小的事务然后分别执行。

选择合适的孤立级，孤立级越高，性能越低，所以选择合适的孤立级，有助于性能优化

避免死锁，在一个事务环境中，当两个或者多个处于不同序列的客户同时想要更新相同的数据时，就会发生死锁，我们应该 避免发生死锁。

例子

事务处理在各种管理系统中都有着广泛的应用，比如人员管理系统，很多同步数据库操作大都需要用到事务处理。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
删除的SQL语句
delete from userinfo where ~~~
delete from mail where ~~
delete from article where~~
~~
如果没有事务处理，在你删除的过程中，假设出错了，只执行了第一句，那么其后果是难以想象的！
但用事务处理。如果删除出错，你只要rollback就可以取消删除操作（其实是只要你没有commit你就没有确实的执行该删除操作）

一般来说，在商务级的应用中，都必须考虑事务处理的！

查看inodb信息
shell> /usr/local/mysql -u root -p
mysql> show variables like "have_%"
系统会提示：
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_bdb | YES |
| have_crypt | YES |
| have_innodb | YES |
| have_isam | YES |
| have_raid | YES |
| have_symlink | YES |
| have_openssl | NO |
| have_query_cache | YES |
+------------------+-------+
8 rows in set (0.05 sec)
如果是这样的，那么我们就可以创建一张支持事务处理的表来试试了。

MYSQL的事务处理功能！

作者：Feifengxlq Email:[email protected]
一直以来我都以为MYSQL不支持事务处理，所以在处理多个数据表的数据时，一直都很麻烦（我是不得不将其写入文本文件，在系统重新加载得时候才写入数据库以防出错）～今天发现MYSQL数据库从4.1就开始支持事务功能，据说5.0将引入存储过程^_^
先简单介绍一下事务吧！事务是DBMS得执行单位。它由有限得数据库操作序列组成得。但不是任意得数据库操作序列都能成为事务。一般来说，事务是必须满足4个条件（ACID）
原子性（Autmic）：事务在执行性，要做到“要么不做，要么全做！”，就是说不允许事务部分得执行。即使因为故障而使事务不能完成，在rollback时也要消除对数据库得影响！
一致性（Consistency）：事务得操作应该使使数据库从一个一致状态转变倒另一个一致得状态！就拿网上购物来说吧，你只有即让商品出库，又让商品进入顾客得购物篮才能构成事务！
隔离性（Isolation）：如果多个事务并发执行，应象各个事务独立执行一样！
持久性（Durability）：一个成功执行得事务对数据库得作用是持久得，即使数据库应故障出错，也应该能够恢复！

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的数据表的锁定和解锁！

MYSQL5.0 WINXP下测试通过～ ^_^

mysql> use test;
Database changed
mysql> CREATE TABLE `dbtest`(
-> id int(4)
-> ) TYPE=INNODB;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> select * from dbtest
-> ;
Empty set (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into dbtest value(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>

*******************************************************************************************************************

[PHP]
function Tran( $sql ) {
$judge = 1;
mysql_query('begin');
foreach ($sql as $v) {
if ( !mysql_query($v) ) {
$judge = 0;
}
}
if ($judge == 0) {
mysql_query('rollback');
return false;
}
elseif ($judge == 1) {
mysql_query('commit');
return true;
}
}
[/PHP]

************************************************

$handler=mysql_connect("localhost","root","");
mysql_select_db("task");
mysql_query("SET AUTOCOMMIT=0");//设置为不自动提交，因为MYSQL默认立即执行
mysql_query("BEGIN");//开始事务定义
if(!mysql_query("insert into trans (id) values('2')"))
{
mysql_query("ROOLBACK");//判断当执行失败时回滚
}
if(!mysql_query("insert into trans (id) values('4')"))
{
mysql_query("ROOLBACK");//判断执行失败回滚
}
mysql_query("COMMIT");//执行事务
mysql_close($handler);
?>

Mysql事务处理问题

事务处理就是将一系列操作当做一个原子操作，要么全部执行成功，如果执行失败则保留执行期的状态。通过提交和回滚机制来实现操作，如果全部执行成功通过提交执行commit结果就会记录到数据库中，如果执行失败通过回滚操作rollback将发生错误之前的所有错误消除，回退到原来状态。

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

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

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

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

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

注意Mysql支持的存储引擎中，默认为MyISAM，是不支持事务处理的，一般都有InnoDB，是支持事务型的。

（1）如果对一个表进行操作的时候需要事务支持，需要配置存储引擎为InnoDB等支持事务型的。

create table XX() engine=InnoDB;

（2）默认情况下，mysql是自动提交模式（autocommit=1），此时会在每一条语句执行完毕后将所做修改立即提交，此时的commit相当于没用的，rollback只对前一句语句起作用，其实也没用，一条mysql语句默认也是原子操作，没必要。

如果设置默认事务处理，需要将自动提交模式关闭即将autocommit设置为0.

set autocommit=0; 设置模式为关闭

select @@autocommit; 查看值是否已经改变

注意，如果在客户端设置的话，设置完，之后断掉连接后再重连又恢复默认设置。每个客户端只能设置客户自己的。

（3）如果自动提交模式是打开的，则需使用语句：

start transaction; 开始事务处理

XX1;

XX2;

commit; / rollback;

来开始事务处理；而如果设置为关闭，则无需使用start transaction，连续语句就为事务指导rollback或者commit。

（4）注意创建、改变、删除数据库或者其中的数据定义语言以及锁有关的都不能成为事务的一部分，如下面：

import MySQLdb

try:
conn = MySQLdb.connect(host="localhost",user="root",passwd="your passwd",db="dbName")
except MySQLdb.Error,e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])

else:
pass #conn.close()

html" name="code">执行一个事务处理，当执行到要创建表时，mysql会自动提交，然后再执行创建语句。如果test1的i为主键，则第三条语句出错，回滚时test1还是插入成功，且创建了表test2.

（5）python中使用数据库，最好采用这种形式，

try:
cur=conn.cursor()
cur.execute('set autocommit=0') #cur.execute('start transaction')
cur.execute('insert into test1 values("8")')
cur.execute('insert into test1 values("8")')

except MySQLdb.Error,e:
conn.rollback()
print "Mysql Error %d: %s" % (e.args[0], e.args[1])

else:
conn.commit()
cur.close()
conn.close()
（6）并行处理问题

Mysql是一个多用户的系统，有多用户在同一时间访问统一数据表，MySIAM采用的是数据表级的锁定标记，来保证同一时间只有一个用户访问此表；Innodb采用了数据行级的访问机制，即两个用户可以对同一个表中不同行的数据同时进行修改，而如果是同一行，则先来的用户先锁住此行，操作结束释放锁后，下一个用户才能操作。

（7）事务处理的隔离性问题

InnoDB默认的隔离级别是repeatable read，如果某个用户两次执行同一个select语句，其结果是可重复的，如果在事务期间有用户对所要读取的数据进行了操作，那么也不会有显示，比如一个存储引擎为innodb的表，如果有一个客户用事务来select读取表数据，另一个用户此时对表做了一个插入之类的操作，第一个用户再进行同样的select读取时，显示数据是没有变化的。

（8）多语句操作非原子操作

如上面（6）中会出现一个问题，如果是一个事务操作，读取数据后，想对数据进行操作，但是可能有另外一个人对此做了操作，那再对此数据进行操作就不对了。

此时需要明确加锁来锁住表，防止别人更改数据，执行结束后释放锁。

lock tables XX write;

XXXXXX;

unlock tables;

也可以使用相对更新代替绝对更新，相对于当前值进行更新，不根据上次的值算出一个绝对值进行更新。这样避免了多条语句的非原子操作。

set a = a - 3 XXXXXXXXXXX；