What levels of transaction isolation in databases do you know?

In the SQL standard, four levels of transaction isolation are defined: Read Uncommitted, Read Committed, Repeatable Read, Serializable.

1. Read Uncommitted: The lowest level. A transaction can read data that has not yet been committed (i.e., performed by another transaction that has not yet completed COMMIT). This leads to "dirty" reads.

2. Read Committed: A transaction sees only data that was committed before it started or during its execution. It prevents "dirty" reads but allows non-repeatable reads and phantom reads.

   • Non-repeatable reads: The same read within a transaction returns different data because another transaction has modified and committed the data between reads.
   • Phantom reads: When executing a query with a WHERE condition, a second transaction adds or deletes rows matching the condition, and re-executing the same query within the first transaction returns a different set of rows.

3. Repeatable Read: A transaction sees only data that was committed before it started. It guarantees that repeated reads of the same rows within a transaction will return the same data. It prevents "dirty" and non-repeatable reads. It allows phantom reads.

   // Example of Repeatable Read
   // Transaction 1
   // BEGIN TRANSACTION;
   // SELECT COUNT(*) FROM users WHERE status = 'active'; // For example, 100
   
   // Transaction 2 (executing concurrently)
   // BEGIN TRANSACTION;
   // INSERT INTO users (name, status) VALUES ('New User', 'active');
   // COMMIT;
   
   // Transaction 1
   // SELECT COUNT(*) FROM users WHERE status = 'active'; // Still 100 (phantom)
   // COMMIT;
   

4. Serializable: The highest level of isolation. Ensures that concurrent execution of transactions is equivalent to some serial execution of those transactions. It prevents "dirty" reads, non-repeatable reads, and phantom reads. It may reduce performance due to locking.

Different DBMSs may have their own implementations and features of isolation levels, as well as additional levels. For example, in MySQL InnoDB, the default is Repeatable Read.