Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Oracle’s commit is fast – What about SQL Server?

with 4 comments

1. Oracle updates 10 rows and commit

2. Oracle updates 1,000,000,000 rows and commit

Whose commit is faster?

The answer is “both of them are fast enough”. This is due to fast commit mechanism.

Fast commit means that Oracle does not clean out all dirty blocks modified by the transaction. The cleanout is delayed. The dirty blocks are cleaned out when accessed at later time(and maybe by another session). This is called delayed block cleanout.

Delayed block cleanout has many problems(like notorious 1555 error), but anyhow, by virtue of it, Oracle’s commit is always fast irrespective of the amount of modified data.

Now my concern is “okay, what about SQL Server?”

1. SQL Server updates 10 rows and commit.

2. SQL Server updates 1,000,000,000 rows and commit.

Whose commit is faster?

My first thought was as following. “Unlike Oracle, SQL Server manges row level lock per row basis(Oracle’s row level lock is per transaction basis). So, the more rows you update, the heavier your commit is”.

But,  think one more time!!!

Lock escalation.

With 1,000,000,000 rows updates, SQL Server would have already escalated row level lock to table level lock. This means that you don’t need to clean out all the rows. Only table level lock is cleaned out and that’s all.

I believe that the threshold of lock escalation is 3,000~5,000 rows(but not sure).

Yes, both Oracle and SQL Server has always fast commit performance, but the mechanisms are quite mutually opposite!

About these ads

Written by Dion Cho

February 17, 2009 at 1:28 pm

4 Responses

Subscribe to comments with RSS.

  1. Hi Dion:
    Can I understand this like this? If sql server does a row level lock and updates 10 rows , it has to repeat the process of commit 10 times (like flush redolog buffer, assing scn etc). But in case of Oracle, it is only once because all 10 rows could be updated as part of one transaction.
    So oracle commit is faster?

    Kumar

    April 1, 2009 at 11:03 pm

    • Kumar.
      No, it’s not my point.

      For 10 rows, both Oracle and SQLServer would do cleanout all of them.
      We would see the big difference when lots of rows are updated.

      Oracle delays cleanout and let later session clean out the dirty blocks. So commit is fast.
      SQLServer escalates the row leve lock to table level lock with more than thousands of dirty blocks. By virtue of lock escalation, SQLServer has just to cleanout the table not the whole rows. So commit is fast.

      Both products has fast commit, but the mechanism is whole different. That’s my point.

      My personal belief is that Oracle’s design is far more superior than SQLServer’s, but it’s just personal!

      Dion Cho

      April 2, 2009 at 1:01 am

  2. Thank you Dion.
    So in a case where I update all the rows in a table in oracle, oracle’s cleaning mechanism and sql server’s cleaning mechanism become same. Is this right?
    SQL Servers method of cleaning dirty blocks becomes only a special case when it comes to ORacle.
    Wherease sql server locks the entire table if number of rows to be updated is more than some number..

    Kumar

    April 2, 2009 at 2:21 am

    • Kumar.

      Oracle’s cleanout mechanism is same with SQLServer’s only when very small number of rows are updated.

      Lock escalation(SQLServer) vs. Undo(Oracle)

      That’s the key difference.

      Dion Cho

      April 2, 2009 at 2:33 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 58 other followers

%d bloggers like this: