Isolation Level

Isolation Level

  • Isolation level refers to the degree to which a transaction is isolated from modifications made by other transactions
    • eg. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  • Concurrency problem in SQL database
    • Dirty Reads
      • Reading uncommitted changes
    • Lost Update
      • Concurrent updates overwrite each other
    • Non-repeatable Reads
      • Different results from the same query in a transaction
    • Phantom Reads
      • New rows appear in a query’s results within the same transaction
Isolation LevelDirty ReadsLost UpdateNon-repeatable ReadsPhantom ReadsDescription
READ UNCOMMITTEDOOOOAllow reading uncommitted data
READ COMMITTEDXOOO1. Default isolation level. 2. Disallow reading uncommitted data
REPEATABLE READXXXO1. Disallow reading uncommitted data. 2. No other transactions can modify data that has been read by the current transaction until the current transaction completes
SERIALIZABLEXXXXEnsure transactions occur in a strict sequence