Concurrency Control Protocols. Walid G. Aref

Concurrency Control

Protocols

Concurrency Control Protocols

•

Pessimistic Approaches

• Assumes worst-case scenario

• All transactions will potentially conflict, i.e., will request the same objects at the same time

• Try to take in-advance precautions to avoid conflicts

•

Optimistic Approaches

• Assumes best-case scenario

Lock-based Concurrency Control

•

Lock a database object before accessing it to avoid the conflicts

• RW Conflicts • WW Conflicts

•

If we maintain one lock type, we can avoid all the above conflicts

•

But will also prevent concurrent reads from happening

• Read-Read will be prohibited • Will limit concurrency

•

Solution:

• _{Maintain two types of locks}

Lock Compatibility Matrix

Discussion on Locks as a Solution for

Concurrency Issues

•

Question: Is having locks on objects enough?

•

Will it resolve the concurrency anomalies?

•

Read Uncommitted (Dirty Reads)

• Does not need any locking support

•

Read Committed

• _{Will need only exclusive locks for write requests}

• Two exclusive locks would conflict with each other and hence prevent WW Conflicts

• _{A read request will be granted if no lock is on the object}

Discussion on Locks as a Solution for

Concurrency Issues

•

Question: Is having locks on objects enough?

•

Repeatable Reads

• _{Will need both Shared and Exclusive locks} • _{SX, XS, and XX locks will conflict}

• Since we have Shared lock on an object, will have repeatable reads

•

Are we done?

•

No!

•

Two Reasons

When Can a Transaction Release a Lock That

It has Gained?

• Assume that Transaction t wants to update tuples A and B

• E.g., Transfer $500 from Bank Account A to Bank Account B

• Begin • Get X-lock(A) • R(A) • Aß A-500 • W(A) • Unlock X-lock(A) • Get X-lock(B) • R(B) • Bß B+500 • W(B) • Unlock X-lock(B) • _Commit

When Can a Transaction Release a Lock That

It has Gained?

• Assume that Transaction t wants to update Bank Accounts (Tuples) A and B (Transfer $500 from A to B) • Begin • Get X-lock(A) • R(A) • Aß A-500 • W(A) • Unlock X-lock(A) • Get X-lock(B) • R(B) • Bß B+500 • W(B) • Unlock X-lock(B) • Commit

• What is the problem of this scenario?

• Problem 1: What if issuer of this transaction decides to abort it at this point?

• Problem 2: What if t cannot gain the lock on B and t cannot proceed further.

• Will need to regain the lock on A to switch back its value to what A was before the transaction started. • But, may be this is not possible, because another

transaction may have grabbed A’s lock and is acting on A’s new value.

à Problem

Two-Phase Locking Protocol

•

A transaction cannot access a database object before it gains the

appropriate lock on it (shared or exclusive)

•

A transaction cannot release a lock until it has gained all its needed

locks

•

A transaction cannot request any locks after it starts releasing locks.

Two-Phase Locking Protocol

•

A transaction cannot access a database object before it gains the

appropriate lock on it (shared or exclusive)

•

A transaction cannot release a lock until it has gained all its needed

locks

•

A transaction cannot request any locks after it starts releasing locks.

Two-Phase Locking Protocol

• 2PL may result in dirty reads

• An exclusive lock released by Transaction t before t commits may be locked by another Transaction u

• à Dirty read

• If t aborts, u will have to abort • àCascaded aborts (Bad)

• Another problem is that if u commits but t decides to abort we cannot rollback u because u has committed

• àUnrecoverable schedule (Bad)

• For 2PL to avoid dirty reads, cascaded aborts, and avoid having unrecoverable schedules

• t should not release any locks until the end, and then release all the locks at once

• à Rigorous 2PL No. Locks Time No. Locks Phase 1: Gaining

Deadlock Problem with 2PL

•

T2: R2PL

•

Begin

• Get X-lock(B) Get X-lock on B

• R(B)

• Bß B-400 • W(B)

• Get X-lock(A) Wait for X-lock on A

• R(A) • Aß A+400 • W(A) • Unlock X-lock(A) • Unlock X-lock(B)

•

Commit

•

T1: R2PL

•

Begin

• Get X-lock(A) Get X-lock on A

• R(A)

• Aß A-500 • W(A)

• Get X-lock(B) Wait for X-lock on B

Deadlock Problem with 2PL

•

T2: R2PL

•

Begin

• Get X-lock(B) Get X-lock on B

• R(B)

• Bß B-500 • W(B)

• Get X-lock(A) Wait for X-lock on A

• R(A) • Aß A-500 • W(A) • Unlock X-lock(A) • Unlock X-lock(B)

•

Commit

•

T1: R2PL

•

Begin

• Get X-lock(A) Get X-lock on A

• R(A)

• Aß A-500 • W(A)

• Get X-lock(B) Wait for X-lock on B

Lock Manager (LM)

•

Separate manager that maintains all the locks of the dbms.

•

Handles all Shared and Exclusive locks for all database objects.

•

For each database object, maintains a queue

•

Upon a lock request on object o, LM checks o’s status

• _{If o is locked by another transaction, insert request into o’s queue, and} transaction is put on wait status

•

Upon a lock release on object o, LM checks o’s queue

• _{If o’s queue is not empty, pick the lock request at the top of the queue and} grant this lock to the requesting transaction, and awaken this waiting

Handling Deadlocks with 2PL

•

Deadlock detection

Deadlock Detection

•

Lock Manager (LM) maintains a wait-for graph

•

Every running transaction is a node in the graph

•

When LM receives a lock request on o by transaction t, and o is

already locked by transaction u, LM inserts an edge from t to u

• _{Indicates that t is waiting on u}

•

Two options:

• Upon every insertion of an edge, check if a cycle is formed, or

• To avoid overhead of checking for cycles per insertion of an edge, but rather check periodically for cycles in the graph

Deadlock Detection (2)

• In the case when a deadlock is detected, LM needs to break the cycle by aborting one of the transactions involved in the cycle

• Which transaction to choose? • _{Several alternatives:}

• Choose one transaction at random to break the cycle, or

• Choose the transaction that has performed the least amount of work so far and abort it to break the cycle

• Choose the transaction that when aborted, no other transactions will have to be aborted because of that, i.e., pick the transcation with the least amount of dependencies and abort it • Choose the transaction with the youngest time stamp and abort it

Deadlock Prevention

• Do not need to maintain a wait-for graph

• Maintain a priority per transaction, e.g., the timestamp that reflects the age of the transaction

• _{Apply a conservative strategy}

• _{Before LM inserts into o’s queue, check priority of the requester transaction t} against the priority of the holder transaction u.

• Wait-die:

• If priority(t) > priority (u), then insert t in the queue (wait) • Else abort t

• Guarantees that no cycle can form in a wait-for graph if it were to be maintained • Wound-wait:

• If priority(t) > priority (u), then abort u • Else insert t in the queue (wait)

Locking Overhead

•

Requesting too many locks introduce significant overhead

•

Example:

• Update an entire table of investors by distributing profit to all stakeholders based on their shares

• _{Will need to lock each shareholder’s record} • Solution:

• Support locks at coarser granularity levels

• Also, provides a summary of the locks provided at the lower level

Multi-granularity Locks

• Provide locks at the multiple levels of granularity:

• The database level • The table level • The disk-page level • The tuple level • The attribute level

• Locks are provided at the appropriate level to reduce the number of locks issued

• Coarser locksà less locks à lower locking overhead but lower concurrency

• Finer locks à more locks à higher locking overhead but higher concurrency

• Protocol:

• Grant a lock at the lower level in the hierarchy only if the lock at the higher level is granted

• Use intention locks (as opposed to ”real” locks) at the higher levels and grant

Types of Intention Locks

•

Intention Lock Types

• IS: Intention to have a real shared lock at the lower level • _{IX: Intention to update at the lower level}

• i.e., intend to have a real exclusive lock at one or more objects at the lower level

• SIX: (S+IX) Share with the intention to update at the lower level

• Popular when, e.g., scanning an entire table checking for some condition and updating only a few tuples

Multiple Granularity Locking Protocol

• Need to abide by Compatibility Matrix for Intention Locks

• Determines which locks can co-exist concurrently in an object

• But need additional set of rules to decide which locks an object can get in the parent/child hierarchy

• Follows an extended two-phase locking protocol

• Cannot lock a node after it has unlocked any node

• Also, cannot unlock a parent node when a child node still has a lock

• Parent has S or X à All children and grandchildren need not have any lock because they all inherit the lock of the parent • Parent has IS à Child can have either S or IS

• _{Parent has IX à Child can have either X or IX or SIX (child} can also have S or IS if decides to read only and not update ) • Parent has SIX à Child can have SIX or IX or X

Example Usages of Multiple Granularity

Concurrency

•

Example 1: Transaction t reads an entire table

• Obtain one IS lock at the table level granularity and multiple S locks at the page or tuple levels, or

• Obtain one S lock at the table level granularity

•

Example 2: Transaction t reads an entire table and only modify a few tuples

• Obtain one SIX at the table level

• Obtain X locks only at the tuples that need to be updated

•

Example 3: Transaction t uses an index to read some tuples of a table

• Obtain one IS lock on the entire table

• Obtain an S lock for each tuple identified by the index to be read

•

Example 4: Transaction t uses an index to update few tuples of a table

• Obtain one IX lock on the entire table

Note on Lock-based Approaches (So Far)

•

Locks are obtained on tuples or objects that exist in the database

•

Tuples that do not exist in the database cannot be locked

•

What happens to transaction semantics that may get effected by

Concurrency Problems Due to Dynamic

Databases

• Example Problematic Scenario with Repeatable Reads: Non-repeatable read due to data insertion

• Remember that with repeatable reads, we use read locks on objects to guarantee that we can read them back

• Assume that Transaction t is scanning a range of tuples and then computes their average.

• For example, report the average salary for employees with age between 18 and 21 • Repeatable read may be violated if some employee with 18 ≤ age ≤ 21 gets inserted

• Insertion cannot be prevented because tuple did not exist at begin of transaction and hence cannot be locked. (Phantom Tuple)

Time Average salary for

Employees with

Average salary for Employees with Insert Employee

How to Deal with Phantom Tuples?

•

Violates repeatable read isolation level

•

Intuition to Solution:

• To reclaim repeatable read isolation level even in the presence of insertions, need to lock a range of data items, e.g., lock the range 18 ≤ age ≤ 21

Time Average salary for

Employees with

Average salary for Employees with Insert Employee

The same Time

Lock the range

Concurrency in the Presence of Dynamic

Databases

•

Need to support:

• Predicate locking

Predicate Locking

•

Lock an entire predicate so that insertions or updates to tuples that

satisfy this predicate are denied

• Will need to maintain list of the locked predicates

• _{With every lock request, need to check the requested lock against all the} registered predicate locks

• Performance bottleneck

Range Locking

• Special case of Predicate Locking

• _{Instead of checking a new lock request}

against all registered and locked predicates:

• Insert a lock into an index so that the lock covers the range

• Lock is localized to where it belongs

• When range is accessed to insert tuple in range, it will be blocked due to the lock

• May need more than one lock to cover the

entire range if there is no one node responsible for the entire range

• Add lock to multiple nodes that cover the range

• Key value locking is a special case, where we insert a lock for a given key value in

18≤age

≤21

🔓

Locks vs. Latches

•

Locks are over data

•

Locks are issued by transactions

•

Locks are managed by the lock

and concurrency managers

•

Locks may introduce deadlocks,

and hence need deadlock

detection and avoidance

techniques

•

Latches are over data structures

•

Latches are issued and managed

by the data structure algorithms

Supporting Concurrent Operations Over B

+

_-trees

•

How to support concurrent activities over B

_-trees?

•

Example: Searching for a key value while an inserting and possibly a

node split is taking place

Supporting Concurrent Operations

In B

+

_-trees

• Search:

Start at root and get Shared lock on it Repeat:

Get Shared lock on child node Release the Shared lock

on the parent node Until Leaf node is reached

Supporting Concurrent Operations

In B

+

_-trees

• Insert/Delete:

Start at root and get Exclusive lock on it Repeat:

Get Exclusive lock on child node If child node is safe

(For Insert: Safe ≡ Has empty space) (For Delete: Safe ≡ More than half full)

Release Exclusive lock from all ancestors

Until Leaf node is reached

Insert item into leaf node and propagate the update into parents as needed

Unsafe Node Next Safe Node First Safe Node 🔓 Full Node

Last Safe Node from Leaf

Release lock from this safe node, assign

lock to next safe node 🔓

Observation 1

•

Most of the time the leaf nodes will be safe

•

Example:

• Assume:

• A maximum occupancy of 100 keys/node, and • A minimum of 50% required occupancy

• After every 50 safe inserts, only 1 insert is unsafe

Supporting Concurrent Operations In

B

+

_{-trees (Bayer-Schkolnick Algorithm)}

• Insert/Delete:

• Start at root and get Shared lock on it

• Repeat:

• Get Shared lock on child node

• Release the Shared lock from the parent node

• Until Leaf Node is reached • If Leaf Node is safe

• Get an Exclusive Lock on Leaf Node

• Insert item into leaf node

• Else

• Release all locks and apply the previous (conservative) insert/delete alg. 38 Shared Lock

1

2

3

Using Multiple Granularity Locks for B

+

_-tree

Concurrency

•

Can use IX locks as we descend the B

_{-tree instead of X locks to allow}

for more concurrency

•

Can use SIX locks higher in the tree as it is less likely to need to set

Exclusive locks higher in the tree.

•

Most likely, will find safe nodes at lower levels

•

Can use a hybrid approach

Disadvantages of Lock-based Concurrency

Control Approaches

•

Locking approaches are conservative

• Assumes worst-case scenario of conflicting transactions

•

Managing locks incur overhead

•

Need provision for deadlock detection and prevention

Optimistic Concurrency Control

•

If we assume that conflicts are not that frequent

•

Can access objects concurrency without the need for locking

•

Worry about conflicts at the time a transaction is about to commit

•

See it would conflict with other transactions if committed

•

Will need some bookkeeping to be able to make this decision at

commit time

Timestamp-Based Concurrency Control

Protocols

•

Timestamps assigned:

1. Each transaction, say T, is assigned a timestamp, say TS(T), once it starts

• If T1 started before T2, then TS(T1) < TS(T2)

• Transaction timestamp may serve as a prioritization mechanism for the transactions

2. Each object, say O, in the database, e.g., each tuple, is assigned two time stamps

1. A read timestamp RTS(O): Reflects the timestamp of the most recent transaction (youngest) that has read O

Correctness Invariants for Timestamp-based

Concurrency Control Protocols

•

Given a series of concurrent transactions with different timestamps,

the purpose of these protocols is to identify and imitate one

permutation of the transactions that reflect some serial execution of

these transactions.

•

Invariants based on the timestamps:

• _{A transaction T with a given timestamp TS(T) can only see updates of objects} that happened in the past, i.e., ones with RTS and WTS < TS(T)

• à Cannot see future updates (even if they are committed), i.e., ones with WTS> TS(T) • à Cannot write into an object O if another younger (future) transaction has already read

Reading an Object in Timestamp-based CC

• Transaction T with Timestamp TS(T) issues Read(O)

• T.Read(O):

• If WTS(O) > TS(T)

• //This object has been written after T has started (a future object w.r.t. T), and hence cannot be accessed by T

• Abort T

• If WTS(O) < TS(T)

• //Allow T to read O and a local copy of O (ensures

repeatable read) Does not say whether WTS(O) has been committed or not

• Set RTS(O) ß Max(RTS(O), TS(T))

• Does it allow reading uncommitted data (dirty reads)? Yes

• _{Whenever a transaction restarts, it is assigned}

WTS(a) TS(T) WTS(b) T b a

Writing an Object in Timestamp-based CC

•

Transaction T with Timestamp TS(T)

issues Write(O)

•

If successful, WTO(O) will be equal to

TS(T)

•

T.Write(O):

• If RTS(O) > TS(T) or WTS(O) > TS(T)

• Need to abort T and restart T with a new timestamp

• Else

• Write O

• Set WTS(O) ß TS(T)

RTS(O) > TS(T): Means that a younger transaction has read old value of O, so T cannot update it since TS(T) is smaller than RTS(O), which is TS for the

transaction that last read O. In this case, T has to be restarted.

WTS(O) > TS(T): Means that a younger transaction has written a new value of O, so the older T cannot update O since this will overwrite the more recent value of O. In this case, T has to be restarted

RTS(a) TS(T) WTS(b) T b a

T cannot write a or b but can write c c

Example

•

The following schedule is not allowed in 2PL

but is serializable (equivalent to T2 then T1)

•

Is it allowed in a timestamp-based protocol? YES

TS(T2) < TS(T1)

TS(T2) = 1 (The time for its first action)

TS(T1) = 3 (The time for its first read)

Example

-T1 can read a because TS(T1) > WTS(a) - RTS(a) = Max(RTS(a), TS(T1)) = Max(1,3)

TID RTS WTS

a 3 1

Example

-T1 can write a because RTS(a) ≤ TS(T1) and WTS(a) ≤ TS(T1) - WTS(a) = TS(a)

TID RTS WTS Value

a 3 3 a2

Example

- Similar to Object a, T2 then T1 can read then write b - Both T1 and T2 will commit

TID RTS WTS

a 3 3

b 3 3

Example 2

•

The following schedule is not allowed in 2PL

and is not serializable

•

Is it allowed in the timestamp-based protocol? NO

TS(T2) < TS(T1)

Example 2

Example 2

Example 2

Example 2

Example 2

- T2 cannot read b because TS(T2) < WTS(b) - T2 cannot read a value of an object in the

future

Discussion on Timestamp-based CC Protocol

1. Timestamp ordering guarantees that in the precedence graph there will be no cycles

à Prioritization using timestamp ordering ensures serializability - Timestamps determine the serializability order.

2. Overhead due to the need to update the objects’ timestamps globally

à _{Similar to the lock manager’s bottleneck}

3. No transaction ever waits

à No deadlocks can happen in timestamp ordering

3. The abortion of one transaction can result in the abortion of other transactions

à Timestamp ordering can result in cascaded aborts

How to Make Timestamp Ordering

Recoverable and Cascade-Free?

•

Timestamp ordering suffers from the following two problems:

• Cascaded Aborts (also termed Cascaded Rollbacks):

• If T1 writes A, T2 (with younger time stamp) reads A, then T1 aborts

àT2 must abort

àAny transaction that reads what T2 writes must also abort

• Unrecoverable Schedules:

• if T2 commits after reading T1’s write, and then T1 aborts after T2 commits

à Non-recoverable schedule

•

How to deal with cascaded aborts and unrecoverable schedules?

• Perform all the writes of a transaction T2 at the end

• Remember the WTSs of all the objects that T2 reads (T2’s dirty reads)

• Reflects the ids of the transactions that T2 depends on

Thomas Write Rule

• A modification over the Timestamp ordering CC protocol

• Admits more schedules and allows more concurrency

• Detect obsolete Writes and ignore them • T1 has timestamp less than T2

• Execution should be equivalent to T1 then T2

• T1’s write W(a) will have TS(T1) < WTS(a) = TS(T2) • According to Timestamp ordering, T1 will have to abort

• But, because TS(T2) is after TS(T1), T2’s write needs to overwrite T1’s write, which is the case now

Optimistic Concurrency Control (Kung-Robinson’s

Validation-based Algorithm)

•

Validation-based Concurrency Control Algorithms (Also termed Optimistic

Concurrency Control)

•

Each transaction T has three phases:

• Read and Execute Phase

• T Reads all the needed data items into a local copy and executes all the transaction logic • Validation Phase

• T preforms validation tests to decide whether conflicts with other executing transactions exist or not, and whether T can commit or not

• Write Phase

• If T passes the validation step then

• T writes its updated pages that are in its local buffers into the database and make them public and visible for other transactions to access

• Else

Parameters for the Validation-based Algorithm

•

TS(T):

• T’s Time stamp

•

RS(T), WS(T):

• T’s read and write sets

•

BTS

(T), ETS

(T):

• Begin and End Times for the Read Phase of T

•

BTS

(T), ETS

(T):

• Begin and End Times for the Validation Phase of T

•

BTS

(T), ETS

(T):

• Begin and End Times for the Write Phase of T TS(T) = BTSread(T) ETSread(T) = BTSrvalidate(T) ETS_validate(T) =

BTSwrite(T) ETSwrite(T)

Read & Execute Validate Write

Read and Execution Phase

•

During the Read and Execution

Phase for Transaction T:

• Read all data items into local copies

• Execute the transaction and

write all updates into local copies • Populate RS(T) and WS(T) with all

the ids of all the objects that T reads or writes TS(T) = BTSread(T) ETSread(T) = BTSrvalidate(T) ETS_validate(T) =

BTSwrite(T) ETSwrite(T)

Read & Execute Validate Write

Validation Phase (For Transaction T)

•

Case 1:

• Any transaction U that has

committed before T starts does not conflict with T

• _{TS(T)> ETSwrite(U)}

TS(T) = BTSread(T)

ETSread(T) =

BTSrvalidate(T)

ETS_validate(T) =

BTSwrite(T) ETSwrite(T)

Read & Execute Validate Write TS(U) =

BTSread(U)

ETSread(U) =

BTSrvalidate(U)

ETSvalidate(U) =

BTSwrite(U) ETSwrite(U)

Transaction T

Transaction U

Validation Phase (For Transaction T)

•

Case 2:

• Any transaction U that has started before T starts and has committed before T starts its write phase

• ETS_write(U) < BTS_write(T)

• RS(T) ∩ WS(U) = ∅ (No Dirty Reads) Read & Execute Validate Write TS(T) =

BTSread(T)

ETSread(T) =

BTSrvalidate(T)

ETS_validate(T) =

BTSwrite(T) ETSwrite(T)

Read & Execute Validate Write TS(U) =

BTSread(U)

ETS_read(U) = BTSrvalidate(U)

ETSvalidate(U) =

BTS_write(U) ETSwrite(U)

Transaction T

Transaction U

Notice that we need to globally share RS and TS for all transactions as well as the status of the transactions

Validation Phase (For Transaction T)

• Case 3:

• Any transaction U that has started

before T and has finished its read phase before T’s read phase and has

committed before T commits • ETS_write(U) < ETS_write(T)

• RS(T) ∩ WS(U) = ∅ (No dirty reads) • WS(T) ∩ WS(U) = ∅ (No racing or

overwrites)

Read & Execute Validate Write TS(T) =

BTSread(T)

ETSread(T) =

BTSrvalidate(T)

ETS_validate(T) =

BTSwrite(T) ETSwrite(T)

Read & Execute Validate Write TS(U) =

BTSread(U)

ETSread(U) =

BTSrvalidate(U)

ETSvalidate(U) =

BTSwrite(U) ETSwrite(U)

Transaction T

Transaction U Notice that we need to globally share RS and TS