Implementation of Isolation Levels

Using different concurrency policies

Policy 1: Locking Database

• A transaction acquires a lock on the entire database before it starts and releases the lock after it has committed.

• A concurrency-control policy such as this one leads to poor performance

Policy 2: Locking accessed data items only

• Instead of locking the entire database, a transaction could, instead, lock only those data items that it accesses. Under such a policy, the transaction must hold locks long enough to ensure serializability, but for a period short enough not to harm performance excessively. We can implement this using these techniques:

  • Two Phase Locking with Two modes of locks

    • Two Phase Locking: First phase where it acquires locks but does not release any, and a Second phase where the transaction releases locks but does not acquire any.

    • Two modes of locks: Shared locks are used when transactions are reading same data and exclusive locks are used when transactions are writing on same data.

  • Using Timestamps

    • assign each transaction a timestamp, typically when it begins

    • For each data item, the system keeps two timestamps:

      • the timestamp of the transaction which read it latest

      • the timestamp of the transaction which wrote over it latest

    • Timestamps are used to ensure that transactions access each data item in order of the transactions’ timestamps if their accesses conflict. When this is not possible, offending transactions are aborted and restarted with a new timestamp.

  • Multiple Versions and Snapshot Isolation

    • By maintaining more than one version of a data item, it is possible to allow a transaction to read an old version of a data item rather than a newer version written by an uncommitted transaction or by a transaction that should come later in the serialization order. There are a variety of multiversion concurrency- control techniques. One in particular, called snapshot isolation, is widely used in practice.

    • In snapshot isolation, we can imagine that each transaction is given its own version, or snapshot, of the database when it begins.4 It reads data from this private version and is thus isolated from the updates made by other transactions. Of course, in reality, the entire database is not copied. Multiple versions are kept only of those data items that are changed. If the transaction updates the database, that update appears only in its own version, not in the actual database itself. Information about these updates is saved so that, after making some checks, the updates can be applied to the “real” database when the transaction commits.