自斟自饮——4. 事务的commit和rollback

liangguanhui

浏览: 112497 次
性别:

最近访客更多访客>>

yuefei008

jyjsjd

jing344986203

xintingandzhouyang

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

自斟自饮

Oracle Informix SQL MySQL PostgreSQL

首先是准备两个个表，注意，不是临时表来的。

create table test1 (
   tid   integer primary key,
   tname char(10)
);

create table test2 (
   tid   integer primary key,
   tname char(10)
);

然后，向test1表插入500w条数据，先test2表插入1w条数据（两个表的数据量相差了500倍）。

至于怎么插入，有三个方法：

用java，连接jdbc，用编程的方式循环插入；
如果数据库支持PL/SQL之类的，可以编程插入，如果是informix，可以考虑用Informix 4GL；
先准备好500w行和1w行的数据文件，然后用数据库自带的Load From之类的语句insert进去。

可以任选其中一样你喜欢的。

数据现在已经准备完毕了。Show is ready.

（1）commit时间的测试。

先建两个表，

create table test1_next (
   tid   integer primary key,
   tname char(10)
);

create table test2_next (
   tid   integer primary key,
   tname char(10)
);

大家都可以注意到，这里四个表的格式都是一样的。

然后测试开始。

begin work;
insert into test1_next select * from test1;
commit;

begin work;
insert into test2_next select * from test2;
commit;

很简单的两个事务。你觉得两个insert的SQL语句，那一个的消耗的时间会多一点？你觉得两个commit消耗的时间哪个会多一点？你觉得Informix、Oracle、PostgreSQL、MySQL的结果会一样嘛？

（2）rollback时间的测试。

把刚才建的两个next表格删除，然后再重新建一次。（重建是为了测试时间尽量不会受到上一次测试的影响）

然后测试开始。

begin work;
insert into test1_next select * from test1;
rollback;

begin work;
insert into test2_next select * from test2;
rollback;

很简单的两个事务。你觉得两个insert的SQL语句，那一个的消耗的时间会多一点？你觉得两个rollback消耗的时间哪个会多一点？你觉得Informix、Oracle、PostgreSQL、MySQL的结果会一样嘛？

（3）结论

你觉得为什么会有这种差别？这种差别会导致我们需要有什么样的习惯？

作为一个开发人员，你必须要了解你的数据库在commit和rollback的时候到底做了什么。注意，这里不是最好、建议，是必须，MUST，not suggestion.

我个人觉得很多人在使用数据库的时候都会有一个很大的误区，以为数据库在begin transaction之后的操作都跟其它的session没有关系，都没有真正操作到数据，直到commit才会一脑子把数据set到数据库里。我不知道你有没有这种想法，但我刚学数据库的时候的确就有这种idea。

如果基于这种观点，数据库在我们commit的时候应该就会很耗时间，因为需要做很多很多的事，操作很多很多的数据；而rollback则不需要消耗什么时间，因为数据还没有被修改啊。

但实际情况并不如我们所愿，commit做的事情其实不多。

------------------------------------ 开始节选 ------------------------------------

在数据库中执行COMMIT之前，困难的工作都已经做了。我们已经修改了数据库中的数据，所以99.9%的工作都已经完成。例如，已经发生了以下操作：

已经在SGA中生成了undo块。
已经在SGA中生成了已修改数据块。
已经在SGA中生成了对于前两项的缓存redo。
取决于前三项的大小，以及这些工作花费的时间，前面的每个数据（或某些数据）可能已经刷新输出到磁盘。
已经得到了所需的全部锁。

执行COMMIT时，余下的工作只是：

为事务生成一个SCN。如果你还不熟悉SCN，起码要知道，SCN是Oracle使用的一种简单的计时机制，用于保证事务的顺序，并支持失败恢复。SCN还用于保证数据库中的读一致性和检查点。可以把SCN看作一个钟摆，每次有人COMMIT时，SCN都会增1.

LGWR将所有余下的缓存重做日志条目写到磁盘，并把SCN记录到在线重做日志文件中。这一步就是真正的COMMIT。如果出现了这一步，即已经提交。事务条目会从V$TRANSACTION中“删除”，这说明我们已经提交。

V$LOCK中记录这我们的会话持有的锁，这些所都将被释放，而排队等待这些锁的每一个人都会被唤醒，可以继续完成他们的工作。

如果事务修改的某些块还在缓冲区缓存中，则会以一种快速的模式访问并“清理”。块清除（Block cleanout）是指清除存储在数据库块首部的与锁相关的信息。实质上讲，我们在清除块上的事务信息，这样下一个访问这个块的人就不用再这么做了。我们采用一种无需生成重做日志信息的方式来完成块清除，这样可以省去以后的大量工作（在下面的“块清除”一节中将更全面地讨论这个问题）。

------------------------------------ 节选完毕 ------------------------------------

上面那段文字是我从书上摘下来的，有点枯燥。简单来说，对于一般的数据库，commit做的事情非常有限，仅仅是写写日志、做个标志和释放锁。

实际上，commit最耗时间的操作是写日志。为什么？因为写日志涉及到IO，凭我们有限的经验都可以知道IO操作是很慢的，而且这个写日志是串行、不是并行的。

于是你可能会问，如果我在一个事务里，更新1000w条数据库，那这个日志应该会很大吧？commit的时候不就很慢？ This is a very good question. 理论上应该是这样，但数据库为了尽可能地减少commit的时间，会在处理过程中写日志（例如每更新10w条写一次），而不会等到commit的时候才一脑子写进去。在处理过程中这种写日志有两种方式：一种是到了一定数量（例如10w）就写，另外一种就是定时（例如每5秒钟）写。Oracle和MySQL都支持这两种方式（实际上这两种方式同时工作）。

但，rollback的情况就完全不同了。假如说commit座的事情非常有限，那rollback做的事情就是推倒重来。简单来说

------------------------------------ 开始节选 ------------------------------------

ROLLBACK时，要做以下工作：

撤销已做的所有修改。其完成方式如下：从undo段读回数据，然后实际上逆向执行前面所做的操作，并将undo条目标记为已用。如果先前插入了一行，ROLLBACK会将其删除。如果更新了一行，回滚就会取消更新。如果删除了一行，回滚将把它再次插入。
会话持有的所有锁都将释放，如果有人在排队等待我们持有的锁，就会被唤醒。

------------------------------------ 节选完毕 ------------------------------------

其中我们很自然就知道，撤销已做的所有修改是最消耗时间的。例如我们修改了1000w条数据，然后rollback，这个时候相当于再重新做1000w条数据的update。

假如：

begin; update mpolicy set magtcd = 1000; commit;      --消耗的时间是10秒。
begin; update mpolicy set magtcd = 1000; rollback;      --那这里消耗的时间我们可以理解为20秒。

之所以commit和rollback有这样的不同待遇，因为在我们的一般操作中commit比rollback的情况多得多，所以就会尽可能降低commit的时间。

所以我们可以得出这样的结论：

commit操作是一种很“平”的操作，不会因为事务操作数据量的大小而有明显的飙升，简单来说就是一种很快的操作；
rollback是跟事务操作数据量呈线性关系耗时的操作，简单来说就是很好耗时间的操作。（当然，数据量很少的时候也是很快的）

最后我要指出一个特例——PostgreSQL。pgsql有一个很重要的特性：在数据量很大的情况下rollback还是非常快。这是由pgsql存储数据的一个特性决定的，它把数据段和UNDO段混放在同一个地方，rollback的时候仅仅是做一个标志，所以跟commit同样快。但上帝永远是公平的，让你爽完之后就来敲打一下你。pgsql的这种特性会导致另外一个问题，需要不定期地清理这一类混杂的“垃圾数据”（你可以理解为Java的垃圾回收过程，那是一个很郁闷的问题，不是吗？）。

（如果需要更加深入了解pgsql的这种特性，可以访问：http://blog.csdn.net/collin1211/archive/2010/11/21/6024691.aspx）