Oracle vs SQLserver: Consistent Reads

I am in a good position given I work extensively with both Oracle and SQLserver databases to see the subtle but major differences in how certain parts of the database are implemented.

What is a consistent read?
It is the C in the ACID (Atomic,Consistent,Isolated and Durable) transaction model. It means when you read the data, your query will see a version of the data which has not changed from when you started running your query.

To provide consistent reads (or READ_COMMITTED transaction level) SQLserver uses shared locks on the data to stop any changes occurring to the data whilst a query is run. This effectively blocks any writes to the data.

***IMPORTANT*** In SQLserver 2000 and earlier, this is the main reason for explosive lock contention and table lock escalations, especially on tables having many inserts, deletes or updates happening concurrently. This type of lock and table escalation will blow away your CPU and any scalability you were hoping for.

Note: In SQLserver 2005, Microsoft have introduced READ_COMMITTED_SNAPSHOT as a database option, which will allow the database to show a version of the data (at the time of execution) without requiring shared locks, similar to the read consistent view Oracle provides with rollback/undo segments. See this part of the MSDN documentation for more information.

Oracle provide consistent reads (or read consistent view) of the data via the use of rollback or undo segments, these segments store the prior versions of the blocks (similar to pages in SQLserver) which contain the rows which are being written to. When you query the data and hit a block which has changed since you query started, Oracle will rebuild the data from the rollback or undo segment to keep the data consistent. See the Oracle Concepts manual for a detailed description of this method.

The dirty little secret (of SQLserver) is that you can do dirty or read uncommitted reads which will return data, even if it has changed since the query started. In some cases this is the only way to enable SQLserver to scale without falling over with lock contention. You use the TABLE hint (NOLOCK) or SET TRANSACTION LEVEL READ UNCOMMITTED at the beginning of your SQL code. This should be implemented only after understanding the nature of the work your application is performing against the database.

Finally SQLserver 2005 is getting closer in functionality to Oracle. It will be interesting to see the snapshots that SQLserver use to provide the versions of data are prone to problems which Oracle has had in the past i.e. the infamous "snapshot too old" error.

Have Fun

Paul