MS SQL Server vs Oracle: Row Versioning

I hear sometimes that SQL Server nowadays, when it supports row versioning, is just the same as Oracle. Is that so? I am going to compare and see.

Indeed, the basic principle is the same: when one transaction is modifying the data in the table, the original row is stored in some temporary place, so other transactions can read the unchanged data.

SQL Server uses the tempdb:

Oracle uses Undo Segments:

Which conceptually looks pretty much the same. Let's get deep now.

STEP 1: Create and setup databases.

A. SQL Server TEST_01, with Read Committed Snapshot isolation enabled:

ALTER DATABASE TEST_01 SET READ_COMMITTED_SNAPSHOT ON;

B. SQL Server TEST_02, with Snapshot isolation enabled:

ALTER DATABASE TEST_02 SET ALLOW_SNAPSHOT_ISOLATION ON;

C. Any Oracle database.


STEP 2: Create a table and fill with the demo data.

1. SQL Server (Read Committed Snapshot, Snapshot)

CREATE TABLE dbo.LockTest (I INT PRIMARY KEY, C VARCHAR(50));

INSERT INTO dbo.LockTest (I, C) VALUES (1, 'A'), (2, 'B'), (3, 'C');

2. Oracle

CREATE TABLE LockTest (I INT PRIMARY KEY, C VARCHAR(50));

INSERT INTO LockTest (I, C) VALUES (1, 'A');

INSERT INTO LockTest (I, C) VALUES (2, 'B');

INSERT INTO LockTest (I, C) VALUES (3, 'C');

STEP 3: Start the first transaction which modifies the table row (DONOT commit).

1. SQL Server (Read Committed Snapshot, Snapshot)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

UPDATE dbo.LockTest SET C = 'B Modified' WHERE I = 2

Exclusive locks will be acquired:

We can check that the original version of the row is now stored in tempdb:

SELECT * FROM sys.dm_tran_version_store;

2. Oracle

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

UPDATE LockTest SET C = 'B Modified' where I = 2;

Exclusive locks will be acquired:

STEP 4: Start another transaction and try to read

STEP 4.a READ UNCOMMITTED

1. SQL Server (Read Committed Snapshot)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRAN

SELECT * FROM dbo.LockTest

Despite an original row is stored in tempdb, transaction reads the dirty data.

2. SQL Server (Snapshot)

The same result, transaction reads the dirty data.

3. Oracle

In fact, Oracle doesn't support READ UNCOMMITTED serialization level. Oracle always reads committed data. It simply can afford it, because, as we are going to see, it does not have any penalties.



STEP 4.b READ COMMITTED


1. SQL Server (Read Committed Snapshot)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

SELECT * FROM dbo.LockTest

So we can see that SQL Server could read data, including row changed in another transaction, obtaining the original data from tempdb.

2. SQL Server (Snapshot)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

SELECT * FROM dbo.LockTest

Now SQL Server cannot get any results, being blocked by the first uncommitted transaction:

So we can see that SQL Server is trying to get Shared lock on the row in question (KEY, technically).

3. Oracle

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM LockTest;

Pretty much the same as in case of SQL Server in Read Committed Snapshot we can read rows, including one being updated by the first transaction. Oracle reads the original row from undo segments.

STEP 4.c REPEATABLE READ

1. SQL Server (Read Committed Snapshot)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN

SELECT * FROM dbo.LockTest

Once again with SQL Server, we are blocked. And the reason is that transaction is trying to obtain the Shared lock:

2. SQL Server (Snapshot)

Transaction will be blocked, the same reason.

3. Oracle

Oracle does not support REPEATABLE READ isolation level - it doesn't have to, and we'll see why.

STEP 4.d SERIALIZABLE

1. SQL Server (Read Committed Snapshot)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT * FROM dbo.LockTest

And we are blocked again. Now because transaction wants to get the Shared lock on the whole range of keys, which will satisfy the WHERE condition (empty).

2. SQL Server (Snapshot)

The same reason, the same result.

3. Oracle

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT * FROM LockTest;

STEP 4.e SQL SERVER ONLY: SNAPSHOT

Because of unimpressive results with the SERIALIZABLE isolation level, SQL Server offers something different: SNAPSHOT isolation.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT * FROM dbo.LockTest

And now we finally got it - the same as Oracle does in SERIALIZABLE.

Wrap-up:

1. Oracle never reads uncommitted data.

SQL Server always reads uncommitted data when in READ UNCOMMITTED.

2. In READ COMMITTED, Oracle transaction reads the committed data and never gets blocked by parallel transaction which modifies the same data. When the data is modified by another uncommitted transaction, Oracle reads original data from undo segments.

SQL Server (READ COMMITTED  SNAPSHOT) transaction, the same as in Oracle, simply reads all the rows, modified in another uncommitted transaction from tempdb.

3. Oracle does not support REPEATABLE READ, because writers do not block readers in SERIALIZABLE.

SQL Server (READ COMMITTED SNAPSHOT) does not support REPEATABLE READ WELL ENOUGH, but SNAPSHOT can be used as an alternative.

4. Oracle does fine in SERIALIZABLE, writers do not block readers.

SQL Server does fine in SNAPSHOT, writers do not block readers.

Well, are they the same? When correctly used, pretty much they are. The performance (temptb vs undo segments) is the only question.