MCTS 70-433 SQL Server Concurrency and Phantom Reads

To prevent dirty / phantom / non-repeatedable reads, you need an "isolation level" with high degree of concurrency

.

In SQL2005 there are five types of isolation, show here in order of increasing degree of concurrency:

• READ UNCOMMITTED
  
• READ COMMITTED
  
• REPEATABLE READ
  
• SNAPSHOT
  
• SERIALIZABLE
  

 

The difference between SNAPSHOT and SERIALIZABLE is quite complex (SNAPSHOT working via a table lock and row versiosns and SERIALIZABLE working via page and row lock), but both prevent phantom reads.

SERIALIZABLE

This isolation level specifies that all transactions occur in a completely isolated fashion; i.e., as if all transactions in the system had executed serially, one after the other. The DBMS may execute two or more transactions at the same time only if the illusion of serial execution can be maintained. At this isolation level, phantom reads cannot occur.

However, many databases (e.g. Oracle[1], PostgreSQL[2]) do not guarantee that there is some serial ordering of the transactions that will result in the same outcome, instead implementing snapshot isolation. This explicitly contradicts the ANSI/ISO SQL 99 standard (see ISO/IEC9075-2:1999(E), page 83). Other databases (MS SQL Server[3]) support both serializable and snapshot isolation modes.

With a lock-based concurrency control DBMS implementation, serializability requires that range locks are acquired when a query uses a ranged WHERE clause. When using non-lock concurrency control, no lock is acquired; however, if the system detects a concurrent transaction in progress which would violate the serializability illusion, it must force that transaction to roll back, and the application will have to restart the transaction.

http://en.wikipedia.org/wiki/Isolation_(database_systems)