Category name´╝ÜTransactions

Why read uncommitted data?

I just read Dennis post about his adventures in isolation level land. He says he does not know a good reason to read uncommitted data because of dirty reads.

Well he should refrase this like: Using the isolation level read uncommitted data could result in a dirty read.

Reading uncommitted data can be very interesting. Not only because you can read data that “is not modified at all but not accessible because of a lock”. For example a eventlog table, a table that contains statistics about page requests.

A side benefit is that a select normally also creates a lock on a table for itself. That is because a single query adheres the ACID rules. This means that a select will lock data while it is running. Sometimes you want non-blocking reads as for example Sahil Malik writes. Lets say you have a log table in your database. You know you only do inserts. Each insert will do locking. Lets say you have some SELECT queries that will result in a table scan. You really don’t want to have a table-lock while it is running because else the application will not be able to add new rows to the table. You know in advance that you will never read dirty data because you only do inserts. And that reminds me of a post of my own Change mssql isolation levels to read uncommitted data from some months ago

So when to use this? Well if locking a table will stall other operations and reading uncommitted data isn’t that interesting. You use this sort of queries mostly for reporting functionality or even just normal read operations. As in reading! Not read data and then update the data. You will need optimistic concurrency control for that and that requires a timestamp column to validate or a total record compare. With a timestamp.. you MUST be sure that the record data read in the first place is not dirty.You cant use the read uncommitted here. But the nice thing about the total record compare is that you can read uncommitted data because the OCC solution relies on the data and not on a timestamp. So it IS possible but in most environments you see timestamps because comparing one timestamp column is cheap.

Change mssql isolation level to read uncommitted data

Sometimes you are debugging an application and want to query the database while the database connection still has not committed (or rollbacked) the transaction. So you fire up the query analyzer just to find out that you cannot see the modifications yet (ofcourse). Today a collegue of mine (Frank Bakker) found out that it is possible to read uncommitted data by altering the isolation level on a connection.


When you have executed this statement you can now see all modifications that are still in a transaction. Isnt that neat or what?

The thing is that I used this some years ago in some functionality that generates reports based on website visits. The query did a select statement and it wouldn’t be bothered if some additional records would have beed added to the table. You can get this behaviour by adding the keyword NOLOCK like the following example.

SELECT count(*)
GROUP BY MyCoolColumn

So NOLOCK and SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED are the same but the first (NOLOCK) is usualy best option as it is directly visible that the query needs a different isolation level. Setting the isolation level seperately can ofcourse increase readability is your query does a lot of joins though.

You can also do this in MySql in almost the same syntax as shown below.

SELECT count(*)
FROM MyTable(nolock)
GROUP BY MyCoolColumn

Happy ‘reading’…

  • Recent Posts
  • Recent Comments
  • Archives
  • Categories
  • Meta