Concurrency Control via Locking

CS157b

Chris Pollett

Apr 12, 2023

Outline

Locking
Lock Types and Compatibility
In-Class Exercise
Lock Scheduling

Locking

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.

More on Locking

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.

Two Phase Locking (2PL)

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.

Why two-phase locking works

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`.

In-Class Exercise

Consider the following serializable schedule from Monday's lecture:
`R_1(A,t)`, `(t:=t+100)_1`, `W_1(A,t)`, `R_2(A,s)`, `(s:= s*2)_2`, `R_1(B,t)`, `(t := t + 100)_1`, `W_1(B,t)`, `W_2(A,s)`, `R_2(B,s)`, `(s := s*2)_2`, `W_2(B,s)`.
Add locks and unlocks to the above schedule to ensure consistency of transactions and legality of schedules.
Are the locks you added consistent with 2PL? Why or why not?
Please post your solutions to the Apr 12 In-Class Exercise Thread.

Different types of Lock Modes

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 either a shared lock or exclusive lock first.
In order to write to `X`, you need an exclusive lock first.
2PL is as in the single lock mode case.

Compatibility Matrix

The information about what is legal to do with shared and exclusive locks can be listed in a matrix:

Lock Requested

S X

Lock Held in Mode S Y N

X N N

		Lock Requested
		S	X
Lock Held in Mode	S	Y	N
X	N	N

Upgrading Locks

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 .

Update Locks

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.

Update Lock Compatibility Matrix

Here's what the matrix looks like:

Lock Requested

S X U

Lock Held in Mode S Y N Y

X N N N

U N N N

		Lock Requested
		S	X	U
Lock Held in Mode	S	Y	N	Y
X	N	N	N
U	N	N	N

Increment Locks

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).
```

Compatibility Matrix for Increment Locks

Here's the associated lock matrix which guarantees serializable database schedules:

Lock Requested

S X I

Lock Held in Mode S Y N N

X N N N

I N N Y

		Lock Requested
		S	X	I
Lock Held in Mode	S	Y	N	N
X	N	N	N
I	N	N	Y

Lock Scheduler Architecture

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.

A Two Part Scheduler

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.

A Lock Table Entry

Group mode is used as a quick test on what locks are currently held.
- `S` - indicates only shared.
- `U` - some shared, one update.
- `X` - only one exclusive lock.
Waiting indicates if some transaction is waiting for a lock on A.

Handling Locking

For `T` to lock `A`:
- The DBMS first checks, "Does there exists an entry for `A`?"
  - If no, the DBMS creates it, adds the transaction to the entry, and grants the lock.
  - If yes, the DBMS checks the group mode:
    - if the mode is `S` and the request is a shared lock, the DBMS grants it, and adds the transaction to the list.
    - If the mode is `S` and the request is `U`, the DBMS grants the lock request, and adds the transaction to the list. The mode is switched to `U`.
    - If the mode is `X` or `U` and the request is `S`, `X`, or `U`, the DBMS denies the request, and adds the transaction to the list, but with Wait? having value "yes".

Handling Unlocking

For `T` to unlock `A`:
- `T`'s entry in the list for A is deleted.
- If `T` had a `U` lock then the DBMS changes the group mode to `S` if there are any transaction still in the list.
- If `T` had value `U` or `X` and Waiting is "yes", then the DBMS can grant the lock to one of the waiting transactions. This grant can be based on one of the following strategies:
  - First come first serve.
  - Priority to shared locks -- First come first serve to shared lock, then updates, then exclusive locks.
  - Priority to upgrading -- if `T` has `U` and is waiting for an `X`lock, grant `T` first. Otherwise, follow one of the previous strategies.