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.
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 session one to select the AcctID and ModifiedDate from the Accounting.BankAccounts table. For this demonstration, we previously inserted eight 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 most likely 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.
After running session two and the 10-second delay has finished, session one will again run the SELECT statement, but this time three records have disappeared. 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.