sql中的事务处理

事务处理（transaction）在各种管理系统中都有着广泛的应用， 很多同步数据库操作大都需要用到事务处理， 比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务。在比如说：如果我们在库存表中做了标记，表明吧三本书卖给了顾客，但是吧订单存入了订单表之前PHP服务器断电了或是出了神马故障，我们的库存信息就不一致了。我们可可能遇到“竞争状态”，比如两个人同时试图购买仓库里特定的最后一本书，如果不采取手段的话，可以想象我们可能把它卖给两个用户。这是我们需要的是通过设计各种模式对表进行“锁定”并且防止其他用户访问它们。

事务处理是把多个SQL查询和语句组合在一起构成对数据库的单一原子性操作的简单方式。要么所有查询和语句都成功并且被数据库接受（提交commit），要么都是失败并且被取消（回退rollback）。至于数据库要支持事务处理必须遵守ACID规则，这个我就不扯淡了，我也不是很懂这些个规则。

1.在说事务处理之前先说说一下几个关键词：

1）.SQL中的自动提交模式

autocommit（自动提交），在mysql和sql server中都是默认autocommit=1，即自动提交模式。这种模式下当你提交一个query时就直接执行。我们可以通过下列语句更改状态：

set autocommit=0    或

set autocommit=1

需要注意的是，当设置为0，即禁止自动提交的时候，以后所有的SQL都作为事务处理， 直到你用commit确认或rollback结束。 注意当你结束这个事务 的同时也开启了个新的事务！

2）.selec...for update的应用

格式： SELECT cols FROM tables [WHERE...] FOR UPDATE [OF cols] [NOWAIT]

其实不只select有这个语句，其他的delete/update/insert都可以在后面添加for update，这样的语句会锁定相应的行（row）。这个将在后面继续接着说。

2.现在说说MYSQL和SQL SERVER中的Transaction

1）.MYSQL中的Transaction

提前说一点，在MYSQL中 只有INNODB和BDB类型的数据表才能支持事务处理。

因此，在定义表的时候应该如下所示：

create table t1

(

id integer auto_increment primary key,

name varchar(50) not null,

paawd varchar(100) not null

)

engine=InnoDB;

上面的新建表注明了engine=InnoDB,表明支持事物处理。

举个例子：

BEGIN;

select number_in_stock from products where pid=343;

subtract 1 from the number_in_stock

update products set number_in_stock=0 where pid=343;

insert into orders(order_date,user_id,product,num_units) values(now(),4538,343,1);

If we make it this far without any errors:

COMMIT;

otherwise, in case of any errors whatever :

ROLLBACK;

事物处理的一般格式就是这样。但是这样还是会出现情况，比如因为数据库服务器中的默认事物处理的隔离级别不是最严格的，同时发生的事物处理可以看到同一行的值，所以当两个用户同时购买只剩下一本的书时,就可能出现情况。如上面的代码第一个用户运行到第一句，这是第二个用户也运行了第一句，这样两个用户都看到了这本书还剩下了一本可以购买。接着用户1继续成功的购买了这本书，而用户2的update查询也会成功，但是没有更新表中的数据！因为数据库服务器中的事物处理代码认识到底层数据已经改变，就不会让第二个进程改变它，但是它不会指出发生了错误。这是我们就需要添加额外的逻辑来检查底层的行值是否已经改变。这就要运用到select查询的更新版本了---select...for update查询。那上面的例子就变为：

BEGIN;

select number_in_stock from products where pid=343 for update;

subtract 1 from the number_in_stock

update products set number_in_stock=0 where pid=343;

insert into orders(order_date,user_id,product,num_units) values(now(),4538,343,1);

If we make it this far without any errors:

COMMIT;

otherwise, in case of any errors whatever :

ROLLBACK;

2）.SQL SERVER中的Transaction

语句：

BEGIN { TRAN | TRANSACTION } 
    [ { transaction_name | @tran_name_variable }
      [ WITH MARK [ 'description' ] ]
    ]
[ ; ]

3.事务处理中的“死锁”现象

当然事物处理也不能多用，可能出现“死锁”的情况，就是都在等待另一个事物处理完成。 有时候只好采取删除掉事务的代码,或是提前在程序中检测输入的存储过程参数是否合法数据.