This is also referred to as a dirty read.
In your example, you are updating data in one session, reading the "dirty" (uncommitted) data in another session. I assume that you use the data for something... a report, a process, a new transaction.
But if the transaction is rolled back in the first session, you still have it in the second. You've now violated three of the four of the ACID properties.
- requires that database modifications must follow and "all or nothing" approach. If one part of the transaction fails, the entire transaction fails and the database is left unchanged. If your example, a data modification in session 1 can be rolled back while the data has already been read and processed in session 2.
- consistency states that only consisten data will be available. Sessions 1 and 2 are now in an inconsistent state.
- isolation refers to the requirement that no transaction should be able to interfere with each other. Rolling back in session 1 will interfere with session 2.
Use of NOLOCK and READ UNCOMMITTED should be used with caution and ONLY when there is no other choice. There are very few scenarios where it is acceptable to read data which may be rolled back. This should be the LAST option, not the first.
I've most often seen this when a programmer or DBA runs into locking issues and rather than appropriately restructuring the application, they simply turn off locking. This is analogous to solving security issues by making all users a sysadmin or Domain Admin. It is a horribly dangerous practice.
commented on Jan 8 2012 9:47AM