Infrastructure at your Service

By Franck Pachot

Did you know that when you set isolation level to SERIALIZABLE, it is not serializable but SNAPSHOT? This isolation level is lower than serializable. I’ve never thought about it until I read Markus Winand slides about transactions. I recommend every developer or DBA to read those slides. This post is there to illustrate write skew in Oracle.

Let’s show an example on SCOTT.EMP table. Let’s say there’s a HR directive to increase one of department 10 employees salary so that total salaries for the department is 9000.
Now let’s imagine that two HR users received the directive at the same time.

User A checks the salaries:

23:18:33 SID=365> select ename,sal from EMP where deptno=10;
---------- ----------
CLARK 2450
KING 5000

The sum is 8750 so User A decides to increase MILLER’s salary with additional 250.

However, to be sure that he is the only one to do that, he starts a transaction in SERIALIZABLE isolation level, checks the sum again, and do the update:

23:18:40 SID=365> set transaction isolation level serializable;
Transaction set.
23:18:41 SID=365> select sum(sal) from EMP where deptno=10;
23:18:44 SID=365> update EMP set sal=sal+250 where ename='MILLER';
1 row updated.

Now at the same time, User B is doing the same but chose to increase CLARK’s salary:

23:18:30 SID=12> set transaction isolation level serializable;
Transaction set.
23:18:51 SID=12> select sum(sal) from EMP where deptno=10;
23:18:53 SID=12> update EMP set sal=sal+250 where ename='CLARK';
1 row updated.

Note that there is no “ORA-08177: can’t serialize access for this transaction” there because the updates occurs on different rows.

The User A checks again the sum and then commits his transaction:

23:18:46 SID=365> select sum(sal) from EMP where deptno=10;
23:19:04 SID=365> commit;
Commit complete.

And so does the User B:

23:18:55 SID=12> select sum(sal) from EMP where deptno=10;
23:19:08 SID=12> commit;
Commit complete.

However, once you commit, the result is different:

23:19:09 SID=12> select sum(sal) from EMP where deptno=10;

Actually, what Oracle calls SERIALIZABLE here is only SNAPSHOT isolation level. You see data without the concurrent changes that have been commited after the beginning of your transaction. And you cannot modify a row that has been modified by another session. However, nothing prevents that what you have read is modified by another session. You don’t see those modification, but they can be commited.

The definition of serializability requires that the result is the same when transactions occurs one after the other. Here, if User A had commited before the start of User B transaction, the latter would have seen that the total were already at 9000.

In this example, if you want to prevent write skew you need to lock the table in Share mode. Locking the rows (with select for update) is sufficient to prevent concurrent updates, but then another user can insert a new employee which brings the total salary higher. In addition to that, row locks are exclusive and you don’t want readers to block readers. Locking a range (DEPTNO=10) is not possible in Oracle. So the solution is to lock the table.

It seems that only PostgreSQL (version >= 9.1) is able to guarantee true serializability without locking.


  • Siddharth Malhotra says:

    Good catch!

    The snapshot isolation does not quite guarantee serializability. A sample algorithm for two accounts with A + B > 100 and A = 100 and B =100 can be shown as:

    READ A into A1
    READ B into B1

    A3 = A1 – 200;
    LOCK (X,A)

    READ A into A2

    if (A1 != A2)
    goto Loop

    else if (A1 + B1 >= 0)
    WRITE A3 into A

    else Abort

    Unlock (All locks)

    A similar transaction as this committed on Transaction B’s side, will end up having both values updated.
    Basically bringing the balance to a net -200.

    I guess sometimes bugs can become features!

  • mik says:

    Wen you commit, the serializable transaction finishes. As a result, what you read after the commit is no longer guaranteed to be consistent with what was read before it.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Oracle Team
Oracle Team