---

Implementation of Isolation in DBMS

---

Isolation is one of the core ACID properties of a database transaction, ensuring that the operations of one transaction remain hidden from other transactions until completion. It means that no two transactions should interfere with each other and affect the other's intermediate state.

Isolation Levels

Isolation levels defines the degree to which a transaction must be isolated from the data modifications made by any other transaction in the database system. There are four levels of transaction isolation defined by SQL -

1. Serializable

• The highest isolation level.
• Guarantees full serializability and ensures complete isolation of transaction operations.

2. Repeatable Read

• This is the most restrictive isolation level.
• The transaction holds read locks on all rows it references.
• It holds write locks on all rows it inserts, updates, or deletes.
• Since other transaction cannot read, update or delete these rows, it avoids non repeatable read.

3. Read Committed

• This isolation level allows only committed data to be read.
• Thus it does not allows dirty read (i.e. one transaction reading of data immediately after written by another transaction).
• The transaction hold a read or write lock on the current row, and thus prevent other rows from reading, updating or deleting it.

4. Read Uncommitted

• It is lowest isolation level.
• In this level, one transaction may read not yet committed changes made by other transaction.
• This level allows dirty reads.

The proper isolation level or concurrency control mechanism to use depends on the specific requirements of a system and its workload. Some systems may prioritize high throughput and can tolerate lower isolation levels, while others might require strict consistency and higher isolation.

Implementation of Isolation

Implementing isolation typically involves concurrency control mechanisms. Here are common mechanisms used:

1. Locking Mechanisms

Locking ensures exclusive access to a data item for a transaction. This means that while one transaction holds a lock on a data item, no other transaction can access that item.

• Shared Lock (S-lock): Allows a transaction to read an item but not write to it.
• Exclusive Lock (X-lock): Allows a transaction to read and write an item. No other transaction can read or write until the lock is released.
• Two-phase Locking (2PL): This protocol ensures that a transaction acquires all the locks before it releases any. This results in a growing phase (acquiring locks and not releasing any) and a shrinking phase (releasing locks and not acquiring any).

2. Timestamp-based Protocols

Every transaction is assigned a unique timestamp when it starts. This timestamp determines the order of transactions. Transactions can only access the database if they respect the timestamp order, ensuring older transactions get priority.

---

Next Topic : Lock Based Protocols in DBMS