Repeatable Read prevents dirty reads and nonrepeatable reads: rows read once cannot change before the transaction ends. It can still allow phantom reads, where a repeated query returns a different number of rows. section 10.11
Mistake: choosing Repeatable Read as the default because the data feels important.
- Repeatable Read is valid when requirements demand it, such as actuarial calculations, but it is often excessive for conventional workloads. section 10.11
- Stronger isolation can increase lock contention and deadlocks; begin with Read Committed unless the use case requires stronger guarantees. section 10.11 section 10.14
- Consider optimistic isolation levels such as Read Committed Snapshot or Snapshot when lock contention causes performance problems. section 10.14
A reporting transaction and a data steward
Use two query windows against ImpressionsArchive. A reporting transaction counts rows matching an operating system, while a data steward deletes three matching rows and commits. Repeating the report can return a different count: Repeatable Read protects previously read rows but does not provide Serializable protection against phantoms. section 10.11
Listing 10.27 Beginning the reporting user’s transaction
BEGIN TRANSACTION #1
SELECT COUNT(*)
FROM dbo.ImpressionsArchive
WHERE OperatingSystemID = (
SELECT OperatingSystemID
FROM dbo.ImpressionsArchive
WHERE ImpressionID = 100
) ;
Start the reporting transaction, then inspect the paired data-steward deletion and final count queries. section 10.11
