In a previous post, we discussed how to work with batches where we created a table named Accounting.BankAccounts and inserted two records. You will need to insert additional records for this demonstration to work. Keep in mind when inserting records for this table that the AcctID field is an IDENTITY column. This is the same table we have been using with our previous post on transaction isolation levels.

SET TRANSACTION ISOLATION LEVEL
READ COMMITTED
BEGIN TRAN
	SELECT AcctID, AcctName, 
		Balance, ModifiedDate
	FROM Accounting.BankAccounts
WAITFOR DELAY '00:00:10:000'
	SELECT AcctID, AcctName, 
		Balance, ModifiedDate
	FROM Accounting.BankAccounts
COMMIT TRAN

To begin, we will start with a new session and write the code as shown above. For this example, we will set the Transaction Isolation Level to Read Committed. We begin our transaction for the first session by selecting AcctID and ModifiedDate from the Accounting.BankAccounts table. For this demonstration, we had previously inserted nine total records.

Now to simulate running two sessions at the same time, we have added a WAITFOR DELAY statement to pause 10 seconds to allow us time to run the second session. In a real-world environment, you would not have this statement in your code. While we are waiting for the 10-second delay, we will run the code for the second session that will delete three records from our table.

BEGIN TRAN
	DELETE FROM Accounting.BankAccounts
	WHERE AcctID IN(3,5,6)
COMMIT TRAN

After executing session 2 and the 10-second delay, the SELECT statement for the first session runs again, but this time three records have disappeared. This is an example of a Phantom Read (The records disappeared when reading data twice in the same transaction).

This is because the Read Committed and/or the Repeatable Read transaction isolation levels do not place locks on a range of records. To prevent records from being deleted or inserted while a transaction is running, you would change the transaction isolation level to either Serializable or use one of the Snapshot isolation levels.