Consistency & Concurrency: A study of Oracle and SQL Server
What does consistency and concurrency exactly mean in the context of databases?
The standard definition of ACID adds a little confusion to transaction consistency. The C in ACID refers to a consistent existence of the wholeness of a database system. That is, maintaining referential integrity, triggers, constraints etc. to maintain a consistent state of the whole database. This is different from the ability of database to provide a consistent view of data to all transactions, known as transactional consistency. ACID defines this property in the I of that acronym, that is isolation. Much of the modern database literature including NoSQL products uses the term consistency to denote transactional consistency, except where it specifically says ACID.
Concurrency is not defined in ACID, so theoretically you can build an ACID compliant database and not support concurrency. Concurrency is the ability of a database to allow multiple users to co-exist and have transactions that access (and potentially modify) the same record(s).
Oracle’s consistency model
Oracle uses a technique known as Multi-version Concurrency Control (MVCC) to implement its consistency model. Specifically, it uses three transaction isolation levels.
READ ONLY
READ COMMITTED
SERIALIZEABLE
READ COMMITTED is Oracle’s most widely used isolation level. Unless we change it using a SET command this is the default. A transaction always reads the last committed version of data, not the recently modified and not yet committed data. Oracle achieves this by way of multi-versioning. There is a time-ordered copy of data that Oracle maintains using rollback segments (undo segments). When a transaction requests data (let’s call it transaction A) that got dirty (that is data modified by another transaction — transaction B — but not yet committed) transaction A doesn’t see the dirty data. This is because Oracle fetched the previous version, or the last committed version, from rollback segments.
This design allows Oracle to not lock each transaction out. When a record is being written and read at the same time by two different users (transactions) Oracle gives the reader the previous copy. This is also the foundation of Oracle’s concurrency model. This allows concurrent transactions seamlessly. There’s however a caveat and we will get to it shortly.
READ ONLY and SERIALIZABLE are analogous. Unlike the name sounds SERIALIZABLE isolation doesn’t put transactions in a queue so that it goes one after the other. Transactions are executed as it arrives. When SERIALIZABLE transaction needs data that’s made dirty by a concurrent transaction, it uses undo segments to fetch previous version of that data (the last committed non-dirty version). It works just the way READ COMMITTED transaction works. But the behavior of SERIALIZABLE transaction changes when the other concurrent transaction commits the dirty records.
When that happens Oracle recognizes that the SERIALIZABLE transaction is no longer really serializable. A commit occurred (from another transaction) on the data that’s being currently read. To be truly serializable, the transaction running on SERIALIZABLE mode should follow the committed record and do that in a graceful way (that is, not picking up the committed value in the middle of the transaction). The only way to achieve this is to fail the SERIALIZABLE transaction. It’s on the application to expect failure when using SERIALIZABLE transactions and design accordingly. Applications should be designed to re-attempt failed transactions and have smaller transactions.
READ ONLY isolation works the same way except there is no writes. This is a rather useless isolation level in practice because READ ONLY can have the same drawbacks in READ COMMITTED (see section below), and on top of that restricts us from writing. Note that database access controls shouldn’t be a product of transaction isolation levels. Oracle provides other means such as permissions, roles and schemas to control access.
Oracle’s concurrency model. And some drawbacks.
Oracle’s implementation of READ COMMITTED isolation using multi-versioning is what enables concurrency. In other words, Oracle do not use record-level locks to ensure consistency. This results in a non-locking, seamlessly concurrent transaction model. We will see shortly that SQL Server does it differently. When a transaction has read a record and half way if a concurrent transaction modified and committed the same record, Oracle’s default consistency model (which is READ COMMITTED) doesn’t lock either of the users out or keep them waiting. This is because the reading transaction uses the previous committed version.
But there are two drawbacks. Note that these apply to SQL Server too.
You may not be able to re-read a record within the same transaction. If you do that, and a concurrent transaction just committed that same record, you will get a different value than the value that transaction had in the beginning. In ANSI/ISO standards, the ability to allow re-reading within a transaction (ability to prevent above situation) is called REPEATABLE READ.
The second drawback is that if a concurrent transaction inserted new records and committed while your READ COMMITTED transaction is still in progress, the READ COMMITTED transaction won’t see those new records (even though it satisfies the criteria of the transaction). It only sees those records that were present at the beginning of the transaction. These invisible records are called PHANTOM RECORDS.
SQL Server’s consistency model
SQL Server don’t have a READ ONLY isolation level. But they have two additional isolation levels for transaction consistency.
READ UNCOMMITTED
READ COMMITTED
READ REPEATABLE
SERIALIZABLE
The two isolation levels not present in Oracle are highlighted. READ UNCOMMITTED lets you read dirty records. That is records modified by another transaction but not committed yet. Just like Oracle’s READ ONLY mode, there is no practical use to it. In production, there is hardly a use case where an uncommitted record by a concurrent transaction needs to be read.
READ REPEATABLE isolation level ensure that once a transaction has begun, it can go back and re-read a record and still get the same values as at the beginning of that transaction. What happens if a concurrent transaction commits in between? Re-reading should give you a different value now, right?
Well, SQL Server prohibits this situation by locking those records out when a READ REPEATABLE transaction begins. It’s implemented using shared locks. A concurrent transaction attempting to commit to the same set of records won’t succeed. Note that the concurrent transaction can still access those records, modify it, but it just cannot commit those changes. This could lead to the most dreaded nightmare of application developers: database deadlock.
Imagine two READ REPEATABLE transactions in progress. The first one read records 1,2,3..10. So there is a shared lock being applied on records as it processes records one by one. That means other transactions can read but can’t write to the locked records. Before it gets to record 10 (and hence before placing a shared lock on it), the second transaction updates record 10. Since record 10 is now dirty and shouldn’t be opened to repeat-reads, second transaction puts an exclusive lock on that record (no reads or writes). Let’s imagine that the second transaction now wants to update record 1 before it can successfully commit the transaction. But record 1 is locked by first transaction through a shared lock (only reads, no writes). At the same time, first transaction can’t read record 10 because there’s an exclusive lock on it. None of them succeeds and presents a deadlock. This is the bane of shared locks.
What is the motive behind this design? Well, for starters this is how Sybase, the precursor of SQL Server, was designed. And the reason it’s designed this way was to follow ANSI/ISO standards. Both READ UNCOMMITTED and READ REPEATABLE are part of the four standard isolation levels per SQL92 standards (the other two are READ COMMITTED and SERIALIZABLE both of which are implemented in both products).
But isn’t shared locks a good way to avoid non-repeatable reads and phantom reads?
I’d argue that it’s not. Deadlock is too much a cost for application developers and DevOps alike. Deadlocks are painful to clean up. All you can do is randomly choose one, and sacrifice the other. That’s irresponsible of the database to leave it to the user to clean up that mess.
Any application requiring strict transactional consistency on a first-come first-served basis — that is the first transaction that lays hand on the data gets to commit it — must implement SERIALIZEABLE transactions. And take enough care to make it less painful (shorter atomic transactions, proactive coding that anticipates and handles serialization errors).
SQL Server concurrency model (and drawbacks)
SQL Server’s default mode is READ COMMITTED, which is the same as Oracle’s. It has the same drawbacks as in Oracle in that it doesn’t allow repeatable reads and allows phantom reads. However with the lack of multi-versioning in this mode of SQL Server, Oracle fares well in concurrency. In more recent versions, SQL Server has introduced something called SNAPSHOT to accomplish concurrency.
It’s the same idea as in multi-versioning. There’s a time-ordered sequence of data (known as snapshots) that’s used when a READ COMMITTED transaction begins, so that the transaction has the most recently committed version of that data. This circumvents the need for record-level locks there by increasing concurrency of transactions. Note that SNAPSHOT option has to be enabled at database level to use this feature.
The flood of NoSQL for a few years around 2010 led us to believe that the rigidity of transaction isolation is going obsolete. But that trend is reversing. Industry is maturing to realize there’s a place for everything. Coincidental enough, just this morning MongoDB introduced ACID compliance on their JSON based NoSQL document database.
