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

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!