Concurrency Control via Locking

CS157b

Chris Pollett

Apr 13, 2020

Outline

We are now going to consider a mechanism for guaranteeing conflict serializability based on locks.
In this set-up, the scheduler keeps a table of locks.
Entries in this table consist of the id of a transaction together with an id of a database element.
The scheduler ensures that only transactions which have the appropriate lock on a database element to do an action can perform that action.
Some kinds of locks will prevent other transactions from obtaining locks on an element.

To make the locking mechanism of the last slide work, we want:
- Consistency of transactions:
  1. A transaction can only read or write an element, if it has previously requested and obtained the lock and it hasn't since released the lock.
  2. If a transaction locks an element it must release it a some point.
- Legality of schedules:
  - Locks must have their intended meaning -- In the simplest case, this means no two transactions may have the lock on the same element without one having first released the lock.
In a schedule, we write `l_i(X)` to say `T_i` locks X, and we write `u_i(X)` to say `T_i` releases the lock.

The two phase locking condition is the following:
- For every transaction, all lock requests must precede all unlock requests.
It turns out that if this condition is satisfied for a schedule, then it will be conflict serializable.
It is called "two-phase", because for a given transaction there will be a phase when the number of locks it has is increasing, followed by a phase where the number of locks is decreasing.
One of the important early papers on 2PL is Eswaran, Gray, Lorie, Traiger 1976.

We will only give some intuition ... A proof is in the book based on induction on the number of transactions in a schedule.
The idea, though, is that for `T_1` to read or to write `X`, it must first get a lock.
At that point, it must hold onto the lock until it is done all of its operations on `X`. This is because `T_1` is not allowed in 2PL to get a lock, release it and re-acquire the lock.
Thus, if `T_2` does anything with `X` it must have done all of its operations with `X` before `T_1` or have done all of its operations after `T_1`.
Now suppose `T_1` and `T_2` also conflict on some element `Y`. To be concrete, suppose `T_2` has the lock on `Y`.
`T_1` can't get this lock until `T_2` releases it, at which point we know `T_2` can't acquire more locks so, in particular, it couldn't get the lock for `X`.
So `T_2` must do all of its operation on `X` and `Y` either before `T_1` or do them all after `T_1`.

Which of the following statements is true?

In redo logging, we flush all changed DB elements before we write a commit.
In undo-redo logging, to do a non-quiescent checkpoint, we write a start checkpoint, then write all dirty buffer to disk, then write an end check point, and finally flush the log file.
All serializable schedules are conflict serializable.

A more sophisticated locking mechanism allows for different levels of locks.
For example, shared and exclusive locks.
If `T_1` has a shared lock on `X`, other transactions are allowed to get a shared lock on `X`. Write `sl_i(X)` in the schedule.
However, only one transaction at a time can have an exclusive lock on `X`, and if any transaction has an exclusive lock on `X`, then no other locks on `X` can be held. One cannot get an exclusive lock until everyone else has released their locks. Write `xl_i(X)` in the schedule.
In order to read from `X`, you need at a shared a lock or exclusive first.
In order to write to `X`, you need an exclusive lock first.
2PL is as in the single lock mode case.

If a transaction `T` already has a shared lock on `X`, it can request an exclusive lock.
If no one else has a lock on `X`, then the request will be granted.
`u_i(X)` releases all locks held on `X`. That is, if one has both a shared and an exclusive lock on `X`.
For example, the following schedule is possible:
`sl_1(A)`, `r_1(A)`, `sl_2(A)`, `r_2(A)`, `sl_2(B)`, `r_2(B)`, `sl_1(B)`, `r_1(B)`, `xl_1(B)` -- denied, `u_2(A)`, `u_2(B)`, `xl_1(B)`, `w_1(B)`, `u_1(A)`, `u_1(B)`.
The ability to upgrade lock can cause deadlocks. For example,
`sl_1(A)`, `sl_2(A)`, `xl_1(A)` --denied, `xl_2(A)` -- denied .

One way to avoid deadlocks is to introduce a new type of lock - the update lock.
We now no longer let a transaction get an exclusive lock if they have a shared lock. One can upgrade to an update lock, however.
An update lock can be used to read and can be upgraded to an exclusive lock, but only one transaction at a time is allowed to hold an update lock.

Many transactions operate on the database only by incrementing or decrementing a value.
For example, the number of seats on an airplane after one ticket purchased goes down by one.
Another example, is money transfers of a fixed dollar amount (say $40).
These kinds of operations commute with each other:
INC(X,40), INC(X,2) = INC(X,2), INC(X,40).
We can introduce a new kind of operation:
```
inc(X,a) -- increment X by a
```
and a new lock called an increment lock to handle them:
```
il(X).
```

We next discuss the design of a simple lock scheduler.
The design principles we will follow are:
1. Transactions themselves do not request locks. It is the job of the scheduler to insert lock actions into the stream of reads, writes, etc.
2. Transactions do not release locks. Rather, the scheduler releases locks when the transaction manager tells it that the transaction will commit or abort.

We will split our scheduler into two parts:
1. The first part inserts locks into the stream of operations from the transaction. Its main job is to determine the lock type.
2. The second part then checks the modified stream. If the operation is a DB operation, then it executes it. If the operation was a lock then it checks if the lock can be granted. If yes, the lock table is modified. If no, then a request is noted in the lock table, and the transaction must wait on all further actions until the lock request is granted.
When a transaction commits or aborts all of its locks are released.
It is also the job of the second part of the scheduler to figure out which of the waiting transactions should get a given lock.