SQL Server - REPEATABLE READ transaction isolation levels

Isolation levels determine the degree of isolation for a transaction from changes made to resources or data by other transactions. To protect data consistency, SQL Server uses locking. Locks protect resources required by a transaction from being accessed or modified by other concurrent transactions. Unless DBA/Developer specify locking hints, SQL Server automatically applies locks of the appropriate granularity, depending on the action being performed. The types of locks acquired depend on the transaction isolation level.

The REPEATABLE READ isolation level ensures that no other transaction can delete or update rows in the result set of each SELECT statement in the current connection until the current transaction has either been committed or rolled back. Transactions using other connections can insert new rows, which may appear in the result set if the same SELECT statement is reissued later in the current transaction. In this scenario, insertion of new rows will not be a problem if users need to insert rows into the Product table while an existing product is being requested. The following transaction isolation levels can be specified:

SNAPSHOT: Ensures that the data read by a statement in a transaction will remain consistent until the transaction is complete. This isolation level does not allow dirty reads, nonrepeatable reads, or phantom reads.

SERIALIZABLE: Completely isolates transactions from one another. This isolation level is the most restrictive and generates the most locks.

READ COMMITTED: Prevents statements from reading data that has been modified by other transactions but not committed. This isolation level does not allow dirty reads, but does allow phantom reads and nonrepeatable reads, and is the default.

REPEATABLE READ: Prevents statements from reading data that has been modified by other transactions but not yet committed, and also prevents other transactions from modifying data read by the current transaction until the current transaction completes. This isolation level does not allow dirty reads or nonrepeatable reads, but does allow phantom reads.

READ UNCOMMITTED: Allows other transactions to read rows that have been modified by another transaction but not yet committed. DBA/Developer should not specify the TABLOCKX table hint when initially querying the Product table. The TABLOCKX table hint is used to specify that a table should be exclusively locked for a given statement. In this scenario, DBA/Developer want to minimize locking, and other users should be able to access information for other products for which a request is not being made. DBA/Developer should not issue the SET XACT_ABORT ON statement at the beginning of the application. The XACT_ABORT option determines how SQL Server handles statements within transactions when runtime errors occur. When the XACT_ABORT option is set to ON and a Transact-SQL statement raises an error at run time, the entire transaction is terminated and rolled back. When the XACT_ABORT option is set to OFF, only the Transact-SQL statement that raised the error is rolled back. DBA/Developer should not set the ALLOW_SNAPSHOT_ISOLATION database option to OFF. The ALLOW_SNAPSHOT_ISOLATION database option controls whether or not the SNAPSHOT isolation level can be used for transactions.