Brief comparison of locking between Oracle, Postgres and Sybase

I'm always surprised at how little most server-side developers know about databases even when all but the most trivial application them. And I am not talking about understanding SQL (worthy cause as it is) but understanding what is happening in the DB regarding locks. You generally don't have any problems at this level during testing. But under a non-trivial load they are a major concern.

So, partly to remind myself, here are some notes on the databases of which I have experience.

Sybase uses (exclusive) write and (shared) read locks extensively. If you update any data, you have an exclusive lock on that data until the end of the transaction. No other thread can even read it. "That data" can be anything. It doesn't need to be a row. It could be a table or an index. So, perhaps surprisingly, these two threads block each other:

TX 1

begin tran

TX2
begin tran

insert into X values ('y')

insert into X values ('y')

[Blocked by TX1]

.
.
.
commit tran

[Released by TX1]

.
.
.
commit tran

They are dealing with different rows but TX1 holds an exclusive lock on the table.

Oracle and Postgres deal with things differently. They use Multiversion Concurrency Control. At the start of a transaction, you get something similar to a snapshot of the database that you can play with until your "merge" your changes into the "trunk" (note: this is only a loose analogy and there are areas where it breaks down - see later).

So, these two (Oracle) processes don't block each other:

TX 1

set transaction isolation level read committed ;

TX2
set transaction isolation level read committed ;

insert into X values ('y')

insert into X values ('y')

commit;

commit;

They only really block each other when they really are contending for the same row:

TX 1

set transaction isolation level read committed ;

TX2
set transaction isolation level read committed ;

update X set Y ='y' where Z = z;

update X set Y ='y' where Z = z;

[Blocked by TX1]

.
.
.
commit;

[Released by TX1]

commit;

But (unlike Sybase) TX2 is not blocked in Oracle or Postgres in this scenario:

TX 1

set transaction isolation level read committed ;

TX2
set transaction isolation level read committed ;

update X set Y ='y2' where Z = z;

select Y from X where Z = z;
[sees the old value for Y]

commit;

commit;

Sybase would block TX2 upon the select command until TX1 committed or rolled back. This leads to an interesting unintuitive corner case in Sybase that I have seen. Since a shared read lock is still a lock, you can have deadlocks between two transactions even if one of those transactions is read-only. For instance, if I issue a select * where ... on a table, I start locking rows in the table. If another transaction updates a row that my select process has not yet reached then tries to update a row my select process already has a lock on - deadlock. Sybase chooses one transaction to be the loser. By default, this is the transaction that has done the least work.

So far, I've discussed the READ COMMITTED isolation level - the default on all three databases. The fun starts when you use SERIALIZABLE.

In Sybase:

TX1
set transaction isolation level 3 -- SERIALIZABLE
begin tran
select count(z) from X

TX2

set transaction isolation level 1 -- READ_COMMITTED

begin tran
insert into X values ('y')

[Blocked by TX1]

.
.
.
commit;

[Released by TX1]

commit;

Note: if TX1 had been at isolation level 1 (READ_COMMITTED) there would have been no blocking in TX2.

Unsurprisingly, Oracle and Postgres don't block:

TX1
set transaction isolation level serializable;

TX2

set transaction isolation level serializable;

insert into X values ('y');
select count(z) from X

[sees value ' w', say]

insert into X values ('y')
select count(z) from X

[sees value 'w' too]

commit;

select count(z) from X

[still sees value 'w' !]

.
.
.
commit;

So, although Sybase blocks (which can be somewhat inefficient with anything but the shortest transactions), it is consistent. In Oracle and Postgres, you will continue to see the same value for as long as the transaction continues even though the data is stale and a perfectly good change has been successfully committed.

Oracle has to keep hold of this snapshot to maintain the SERIALIZABLE contract for potentially forever (or at least until you get "ORA-01550 snapshot too old"...).

And whereas Sybase guarantees that your view of the data always SERIALIZABLE, Oracle and Postgres do not.


TX1
set transaction isolation level serializable;

TX2

set transaction isolation level serializable;

delete from X where Z=z;

update X set Y='y2' where Z=z

[ERROR!]

commit;

Oracle gives you:

Error: ORA-08177: can't serialize access for this transaction

and Postgres gives you:

ERROR: could not serialize access due to concurrent update
SQL state: 40001

Interestingly, the criteria of SERIALIZABLE is defined as being met if the final database state is equal to all the queries being run one after the other with no overlap. Note, the DB implementation is not obligated to actually do this, just that the final state must be equivalent to this behaviour. The Sybase programmers chose to guarantee this every time. The Oracle and Postgres developers chose to make a best-effort and to throw an exception if the contract could not be honoured.

There will always be subtle differences between implementations. So, Java developers, get to know a little about your database of choice. (Oh, and keeping transactions as short as possible is a good idea in all three databases.)

Aside: it's a pity that the Exceptions in the JDBC spec are so non-specific. I get a SQLException if the database is down (from which I can't really recover). And I get a SQLException if my transaction was deadlocked (from which I can recover, namely by trying again.) A missed opportunity.