[CS - DB] Isolation level of Transactions

Isolation level of Transactions

In a select transaction, several problems might occur:

• Dirty Read: Other transactions can see changes from the current transaction, even if the changes are not yet committed
• Non-Repeatable Read: Repeated attempts to read the same data results in different result sets
• Phantom Read: By inserting, modifying or deleting data, other transactions will see different results even if the conditions remain the same

SQL defines 4 different transaction levels:

• READ UNCOMMITTED: Changes in a transaction are visible from outside (in PG: READ COMMITTED)
• READ COMMITTED: Changes are only visible after COMMIT
• REPEATABLE READ: The database remembers the timestamp of the first read of a data set, but this does not belong to new data sets inserted in the meantime (in PG: SERIALIZABLE)
• SERIALIZABLE: Transactions are fully encapsulated