Why read uncommitted data?

By ramon
December 19, 2006
1

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.

Comments: 1

  1. Lelala says:

    So,
    high traffic sites usually use (in most parts of the system) read-uncommit.
    A page like Facebook has no neads to read-commit, since most of the data is not essential/critical.

    Another story it is when handling payments or “real enterprise” apps (sorry for that: Facebook is NOT an enterprise app from my perspective 😉 )

    Depending on your architecture, read-uncommit may give you an additional speed boost.

    Regards

Leave a Reply

Your email address will not be published. Required fields are marked *

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