Outline

• Rules for Timestamp Scheduling
• Quiz
• Discuss isolation levels

Introduction

• Last Wednesday, we finished up our discussion of how to use locking to ensure serializability of schedules.
• We then started considering other techniques that allow serializable schedules, in particular, we began looking at how we could use timestamps to do this.
• In this approach, we maintain for each transaction `T`, a starting timestamp `TS(T)` for when it began.
• For each database element `X`, we maintain `WT(X)`, the timestamp of when it was last written; `RT(X)`, the timestamp of when it was last read; and `C(X)`, whether the last item to write `X` has committed.
• We identified the read-too-late and write-too-late problems as situations where the database scheduler might use `TS(T)`, `WT(X)`, and `RT(X)` to determine that a transaction needs to be aborted.
• We also gave the dirty read problem as an example of a situation where the scheduler can make use of `C(X)` to determine that a transaction's request needs to be delayed.
• We start today by determining an algorithm the scheduler could use with timestamps to ensure serializability of transaction operations.

Timestamp-Based Scheduling

• The scheduler, when given a read or write request by `T`, can either:
  • Grant it,
  • Abort `T`,
  • Delay `T` until some other transaction commits or aborts.
• The next slide has the exact rules.

Rules for Timestamp-Based Scheduling -- Read and Write

• If the request is a `R(X)`:
  • If `TS(T) gt WT(X)`, the read is physically realizable.
    • If `C(X)` is true:
      • Grant the request.
      • If `TS(T) gt RT(X)` set `RT(X) := TS(T)`
    • If `C(X)` is false:
      • Delay `T` until either `C(X)` becomes true or the transaction that wrote `X` aborts.
  • If `TS(T) lt WT(X)`, the read isn't realizable.
    • Rollback `T`, and restart it with a larger timestamp.
• If the request is a `W(X)`:
  • If `TS(T) ge RT(X)` and `TS(T) ge WT(X)`, the write is physically realizable and must be performed.
    • Set new value for `X`.
    • Set `WT(X) := TS(T)`
    • Set `C(X) := false` ...
  • If `TS(T) ge RT(X)` but `TS(T) lt WT(X)`, then the write is realizable but there is already a later value of `X` stored in DB.
    • If `C(X)` is true, then we can ignore the write request.
    • If `C(X)` is false, we must delay `T` until the other transaction commits or aborts.
  • If `TS(T) lt RT(X)`, the operation is unrealizable so it is rolled back.

Rules for Timestamp-Based Scheduling -- Commits and Aborts

• If the scheduler receives a commit request from `T`:
  • All of the elements written by `T` have `C(X`) marked true.
  • Delayed transactions are notified so they can now proceed.
• If `T` aborts:
  • Transactions waiting on `T` are notified.
  • These transactions can now repeat the requested operation that was blocked by `T` to see if it is now legal.

Quiz

Which of the following statements is true?

1. In two phase locking only a commit will cause transactions locks to be released.
2. The tree protocol is used to define the compatibility matrix of an incremental lock.
3. To implement timestamp scheduling, each transaction is assigned a timestamp, each database object is assigned both a read and write timestamp, and the DBMS maintains a commit flag for each database object.

Multiversion Timestamps (Papadimitriou Kanellakis 1984)

• We want to allow for greater concurrency than basic timestamping allows.
• To do this we will reduce the number of reads that cause transactions to abort.
• Suppose transactions arrive in the order:`T_1`, `T_3`, `T_2`, `T_4`.
• Consider the schedule:
  `R_1(A)`, `W_1(A)`, `R_2(A)`, `W_2(A)`, `R_3(A`), `R_4(A)`.
• `T_3` must abort according to timestamping (`T_2` started later than `T_3`, but wrote `A` before `T_3` tried to read it).
• However, if we had kept an old value of A around for `T_3` we might not need to abort it.

More on Multiversion Timestamps

• How do we manage multiple versions of database elements?
  • When a write `W_T(X)` occurs, if it is legal, a new version of `X` is created. Its write time is `TS(T)` and we will call it `X_t` where `t=TS(T)`.
  • When a `R_T(X)` occurs, the scheduler finds the first version of `X_t` of `X` such that `t le TS(T)`, and such that there is no `X_{t'}` with `t lt t' le TS(T)`.
  • Write times are now associated with versions of an element and they never change.
  • Each `X_t`, now has its own, independent read time, `RT(X_t)`, indicating the most recent timestamp of a transaction that read version `X_t`.
  • Read times are used to reject certain writes as will be indicated on the next slide.
  • When a version `X_t` has a time `t` such that no active transaction has a timestamp less than `t`, then we may delete any version `X` of previous to `X_t`.

What kind of writing should be rejected?

• `S` should have read `T`'s value, but reads `X_50` instead. So `T` would be aborted in this case.

Timestamps versus Locking

• Basic rule of thumb:
  • Timestamping works better when most transactions are read-only, or it is rare that concurrent transaction will read and write the same element.
  • Locking works better in high conflict situations.
• The reasoning is:
  • Locking frequently delays transactions as they have to wait for locks and can lead to deadlocks.
  • If concurrent transactions have frequent reads and write in common then timestamping will tend to cause transactions to rollback frequently making the throughput less than with locking.
• Commercial DBMS systems try to get the best of both by allowing user's to set transactions as read only. Such transactions are then handled using multiversion timestamping, and otherwise, the DBMS uses locking for other kinds of transactions.

Transactions and SQL

• To indicate the start of a transaction in SQL we can use the command: START TRANSACTION.
• Many DBMSs also allow the commands: BEGIN, BEGIN WORK, BEGIN TRANSACTION to do the same thing.
• A transaction continues until either a COMMIT command or ROLLBACK command is executed.
• Many DBMSs allow END or END TRANSACTION as an alias for COMMIT, and ABORT as an alias for ROLLBACK.
• If you connect to a database and never do a START TRANSACTION, then the DBMS often implicitly assumes there is a START TRANSACTION before each operation you do and a COMMIT after it is done.
• To tell the DBMS about your transaction you can use the SET TRANSACTION command. For example,

  SET TRANSACTION READ ONLY;
  SET TRANSACTION READ WRITE;
  

• The first line above says that your transaction should only be allowed to do reads, the second says it can do both reading and writing.
• In the first case above, as we said on the previous slide, the DBMS will typically use multiversion timestamping to manage concurrency.
• It should be noted, although Postgres supports explicit locking, by default it uses multiversion concurrency control.

SQL Isolation Levels

• If a transaction is READ WRITE (DBMS will typically assume this by default), we can control the degree to which its operations are isolated from other transactions by setting its isolation level. The possible variants on this are:
  • SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- In this case, the DBMS can do the operations of the transaction and not bother with locking. The transaction `T` might read data that is not committed (dirty reads) and values that `T` reads might be changed while `T` is still operating (unrepeatable reads). Also, phantom records can be inserted while the transaction is operating. This level might be suitable if you wanted to compute some statistics (which you write), and where you don't care if the statistics are perfectly accurate, but you do want them calculated quickly.
  • SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- In this case, the DBMS obtains exclusive locks before writes and holds them until the end of the transaction. It obtains share locks before reads, but releases them immediately after the read. Given another transaction at this level or higher, this guarantees that a read has been committed as exclusive locks are only released when a transaction ends. It is possible to have unrepeatable reads and phantoms with this kind of locking.
  • SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- In this case, the DBMS obtains exclusive locks before writes, and shared locks before reads, but does not release any locks until the transaction commits or aborts. This is called strict 2PL. It is still possible for phantom inserts to occurs at this level, but all reads will be repeatable.
  • SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- In this case, the DBMS obtains locks as in the REPEATABLE READ case, but also obtains locks on any indexes and structure granularities that might be involved with `T`. Since an insert by another transaction `S` would involve changing a table, and at this level the database would get an `IS` or `IX` lock on a table that `T` needs as well as locks on an index elements, the insert can't happen until after `T` commits or aborts.