Data Management Systems Transaction Processing Concurrency control - - PowerPoint PPT Presentation

Data Management Systems

• Transaction Processing
• Concurrency control and

recovery

• Transactions
• Recovery

Gustavo Alonso Institute of Computing Platforms Department of Computer Science ETH Zürich

1 Transactions - 2PL

2 Phase Locking Deadlocks Locking Table Snapshot isolation

2 Phase Locking (2PL)

Transactions - 2PL 2

2PL Background

• 2 Phase Locking
• Widely used since many decades
• The canonical implementation of concurrency control
• With important implications for recovery
• Although alternatives exist (snapshot isolation), the underlying

locking mechanisms are still widely used even in those alternative systems

• How locking is implemented has a huge impact on performance and

scalability.

Transactions - 2PL 3

Locking in databases

• Concurrency control in databases is implemented using locking
• Generally using a lock table (see later)
• Locks cover tuples, tables, indexes, blocks …
• Locks can have different semantics that indicate whether they can be

shared or are exclusive:

• Compatibility Matrix
• Shared lock (read)
• Exclusive lock (write)
• Other modes possible

Transactions - 2PL 4

NL S X S

 

• X



• request

2 Phase Locking Protocol

• 1. Before accessing an object, a transaction must acquire lock.
• 2. A transaction acquires a lock only once. Lock upgrades are possible.
• 3. A transaction is blocked if the lock request cannot be granted

according to the compatibility matrix.

• 4. A transaction goes through two phases:

1. Growth: Acquire locks, but never release a lock. 2. Shrink: Release locks, but never acquire a lock.

• 5. At EOT (commit or abort) all locks must be released.

Transactions - 2PL 5

Example

Transactions - 2PL 6

Why two phases?

• 2PL prevents histories where a transaction must be aborted due to

concurrency control issues:

• An operation is not allowed unless we are sure it is safe
• Without the two phases, the following can happen:
• … L1[x]w1[x]U1[x]L2[x]w2[x]L2[y]w2[y]U2[y] …
• If T1 now accesses y for read or write, it can do so, creating a non serializable

history

• This cannot happen if the two phase rule is enforced: if transaction T2 gets a

lock from another transaction T1, T1 cannot get a lock from T2 since the two phase rule would be violated (T2 would be acquiring a lock after it has released a lock)

Transactions - 2PL 7

2PL results in conflict serializable histories

• Locks prevent from two transactions modifying the same item at the

same time

• To do an operation, a lock must be held
• To get a lock, it must be free or shared
• This guarantees the atomicity of single operations
• The two phase rule ensure no cycles can occur in the conflict graph
• A cycle implies a circular dependency created by conflicting operations
• T1 -> T2 implies T1 released a lock before T2 got it
• For a cycle to exist, T2 must release a lock before T1 acquires it
• That would violate the two phase rule

Transactions - 2PL 8

2PL and SQL isolation levels

• Uncommitted read
• Typically only allowed for read-only transactions (see later)
• Implemented by not acquiring a lock before reading
• Read committed
• Keep write locks until end => read committed
• Get a short lived lock for reading (release after reading) => non repeatable read
• Repeatable Read
• Keep write locks until end
• Keep read locks until end => what is read will not change
• Serializable
• Like repeatable read
• Read lock on table to prevent phantoms (or cursor locking)

Transactions - 2PL 9

What about recovery?

• Locking affects recovery in terms of deciding what to do when a

transaction aborts

• Basic 2PL does not say anything about aborting or committing

transactions:

• … L1[x]w1[x]U1[x]L2[x]w2[x]L2[y]w2[y]U2[y] …
• In principle, it is serializable
• If T1 aborts, recovery using before images would not work as undoing T1

would undo the changes done by T2 (one could argue that there is no need to recover anything as x has been rewritten, but if T2 aborts, then T2 cannot be recovered by restoring a before image)

Transactions - 2PL 10

Strict 2PL

• Strict 2PL combines 2PL with Strict histories:
• Enforce 2PL
• Keep read and write locks until the transaction commits or aborts
• Strict 2PL results in serializable and strict histories
• 2PL ensures serializability
• Keeping all locks to the end ensures no uncommitted data can be read or
• ver-written
• All engines implement Strict 2PL (research papers sometimes propose

to weaken the rule but it is neither a good nor practical idea)

Transactions - 2PL 11

Deadlocks

Step T1 T2 Comment 1. BOT 2. lockX(A) 3. BOT 4. lockS(B) 5. read(B) 6. read(A) 7. write(A) 8. lockX(B) T1 must wait for T2 9. lockS(A) T2 must wait for T1 10. ... ...  Deadlock

Deadlock Detection: in theory

Wait-for Graph

• T1  T2  T3  T4  T1
• Aborting T3 will resolve the cycle
• Building the graph is expensive in

systems running many concurrent transactions

T1 T2 T3 T4

Deadlock detection: in practice

• In practice, no wait-for-graph is built:
• Transactions/queries have a timer
• If the timer expires, the transaction/query is aborted
• Deadlocks are considered rare enough to prefer making mistakes

every now and then (aborting a transactions that was fine) than implementing something as expensive as a wait for graph

• This approach has many implications in practice:
• A long transaction might block queries
• The queries might abort because their timer expires while waiting for the

transaction

• Badly written applications can create such scenarios

Transactions - 2PL 14

Locking Table

Transactions - 2PL 15

Transaction Manager

• A database engine uses a

transaction manager to enforce concurrency control

• Implemented in many different

forms

• Deeply connected with the

recovery manager and I/O system

• We will cover the classical

implementation

Transactions - 2PL 16

Transaction-Manager TM Scheduler Recovery-Manager Buffer-Manager Data-Manager T2 T3 T1 Tn ...... Storage System

Basic functions of a Transaction Manager

Transactions - 2PL 17

Transaction Table Ti Lock Table handler Log Lock Hash Table hash

Lock Header X Lock Request Tj Lock Request Ti Lock Header Y Lock Request Ti

Transaction Lock List w[x] w[y] Log Record Ti, w[x] Log Record Ti, w[y] …….. …….. ……..

Basic transaction Manager

• Transaction table: list of active transactions in the system, generally

maintained by the engine in the common area

• Transaction Handler: pointer to the structures containing al the

relevant information related to a transaction (these data structures could be in the private area of a session)

• Lock Table: a hash table containing entries that correspond to active
• locks. Locks on the same item are captured as a linked list
• Log: entries that capture the operation performed and are kept in

memory until it is time to write them to disk

Transactions - 2PL 18

Basic operations transaction manager

• Begin Transaction:
• create an entry in the transaction table (no log entry is created unless

explicitly requested)

• Read/write operation:
• hash the tuple id to find the corresponding entry in the lock table
• If empty, lock is granted
• If there is a list, attached request at the end of the list (grant request is earlier

requests are compatible)

• Write operation:
• Create a log entry (with Log Sequence Number (LSN), before and/or after

image, transaction id, and pointers (LSN) to the previous log entry of the same transaction)

Transactions - 2PL 19

Basic operations transaction manager

• Commit transaction:
• Release locks using the transaction lock list
• Resume transactions waiting for the locks now released
• Finalize log entries
• Write log entries to storage
• May write actual data modified to storage
• Abort transaction
• Release locks using the transactional lock list
• Resume transactions waiting for the locks now released
• Use log entries to undo changes (or discard changes)
• May write log entries to storage

Transactions - 2PL 20

Real systems

• Actual database engines implement this basic design in many

different ways:

• Often, there is an actual lock table similar to the one described
• Sometimes, no lock table (e.g., Oracle): locks stored in the blocks containing

the tuples (makes sense instead of using the tuple id (block id, offset) to hash to the lock table, use the corresponding block as the entry in the lock table for all tuples in that block => no need to keep a separate table)

• Log records can take many forms (redo, undo, undo/redo, etc.; see later)
• Lock entries can also vary in their structure
• Recall the headers in the blocks: also used to implement some of these data

structures, including information about what is locked

Transactions - 2PL 21

Implementation aspects

Transactions - 2PL 22

What is a lock? What to lock?

• We have so far studied locks in a limited context
• Read/write, Shared/Exclusive
• In reality, many more modes.
• Read/write locks are too coarse and may result in more conflicts than

necessary

• Some type of locks (table locks) are too big (lock many tuples) and having

more information helps to reduce conflicts

• What lock modes are supported and what can be locked is system

specific

Transactions - 2PL 23

Hierarchical locking

• As we have seen for phantoms, locks are obtained not only on tuples

but also on other database objects:

• Tables
• Blocks
• Indexes
• Index Blocks
• Schema
• All these locks have the same purpose: prevent conflicts when

updating and modifying the system.

Transactions - 2PL 24

Cursor locking

• Cursors are used to traverse tables
• They can also be used (and are used) as a

way to implement concurrency control.

• Get a cursor on a table
• As the cursor advances, the corresponding

locks are obtained on the next tuple and released on the previous

• Cursors cannot get ahead of each other
• Useful for select statements scanning a

table and using a complex predicate to decide what to update

Transactions - 2PL 25

EXEC SQL declare new_curs cursor for select * from customer_notansi for update;

https://www.ibm.com/support/knowledgecenter /en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_0 660.htm

Lock conversion

• SQL manages tables rather than tuples
• Concurrency control operates at the tuple level
• An update statement might have to read many tuples until it finds the
• ne it wants to update:
• Get a read lock on all items in a table
• Convert to a write lock when the tuple matches the predicate
• Lock conversion might lead to deadlocks:
• Get the low level lock (shared)
• Another transaction also gets a shared lock
• When the lock needs to be upgraded, we have to wait
• Will happen with transactions that are doing similar operations

Transactions - 2PL 26

Examples of locks (SQL Server)

• Shared locks => for reads
• Update locks => for updates but do not allow to write
• Exclusive locks => for writing
• Intent locks => used to lock objects higher in the hierarchy to prevent
• ther transactions form preventing operations lower down
• Schema locks => prevent changes to the schema or access to objects

while the schema is changing (dropping a table, dropping a column)

• Key range locks => lock a range of tuples to prevent phantoms

Transactions - 2PL 27

https://www.sqlteam.com/articles/introduction-to-locking-in-sql-server

Examples of Table locks (Oracle)

• Row Share: allows concurrent access to the table but does not allow

to lock the entire table in exclusive access (allows updates without locking the entire table)

• Row Exclusive: does not allow either shared or exclusive locks on the

table (used when updating, inserting, or deleting)

• Share: Allows concurrent queries but no updates
• Share Row Exclusive: allows to read a table and allows others to read

the table but they cannot lock the table

• Exclusive: allows queries but it does not allow anything else (no

updates, no locking)

Transactions - 2PL 28

Snapshot isolation

Transactions - 2PL 29

Snapshot isolation

• Initially used by Oracle, it is a form of multiversion concurrency

control (MVCC)

• MVCC applies serializability but keeping in mind that there are several

versions of objects:

• Writes generate a new version
• Reads read from a concrete version of the database
• Useful for many reasons, among others that it can be implemented

without using locks

Transactions - 2PL 30

Snapshot isolation

• When a transaction starts, it receives a timestamp T.
• Reads:
• All reads of that transaction will only access items that where committed as of time T

(it reads the database version as of T).

• Writes:
• All writes are carried out in a separate buffer (shadow paging).
• Writes only become visible after a commit.
• When a transaction commits, the engine checks for conflicts
• First-committer-wins rule:
• Abort T1 with timestamp T1 if exists T2 such that
• T2 committed after T1 started and before T1 commits
• T1 and T2 updated the same object

Transactions - 2PL 31

Is it serializable?

• Two transaction T1 and T2 accessing two data items x and y
• r1[x]r1[y] r2[x]r2[y] w1[x] w2[y] c1 c2
• History is not serializable T2 -> T1 -> T2
• But is it wrong?
• Depends …
• Correct form the application perspective:
• The transactions want to book seats in a theater
• They look to the same seats x and y, find them empty
• Each one books a different seat T1 gets x, and T2 gets y

Transactions - 2PL 32

Is it serializable?

• Incorrect from the database perspective:
• The transactions are payments against two bank accounts
• The accounts belong to the same owner
• The bank allows individual accounts to be negative as long as the total

balance of all the accounts with the same owner is positive

• Constraint: x + y >= 0
• Both read the total balances and find the payment they want to perform is

fine

• However, if T1 subtracts money from account x and T2 subtracts money from

account y, there is no guarantee that the total will still be positive since none

• f them sees the other

Transactions - 2PL 33

A controversial matter

• The behavior of snapshot isolation was the source of many

controversies and debates among companies and systems (Oracle implemented it while everybody else implemented traditional serializability)

• Snapshot isolation has significant performance advantages
• Today used by almost everybody, especially in modern engines
• Definitely used in all modern main-memory OLTP databases
• It also has advantages in distributed settings and with replication (see

later in the course)

Transactions - 2PL 34

SI and SQL isolation levels (Oracle)

• Uncommitted Reads:
• Not supported
• Read Committed:
• Called Read Consistency in Oracle (it is more than ANSI Read Committed)
• Reads from committed version as of start (ANSI level allows to read later versions)
• Write locks until end of transaction
• Repeatable Reads:
• Not supported as a separate level (it is given by the definition of snapshot

isolation)

• Serializable:
• As discussed, also provides repeatable reads

Transactions - 2PL 35

SI Implementation

• Snapshot isolation can be implemented without locks
• Reads do not wait for writes and vice-versa
• Conflicting writes will lead to one transaction being aborted
• Reads:
• Get the query/transaction timestamp
• The database keeps versions of the tuples by adding a LSN to the tuple entry in each block (a

max LSN can also be included for the block)

• Read the tuple with the correct LSN (highest LSN that is lower than the start timestamp)
• Write:
• Create a new copy of the tuple
• Check at the end whether the latest version of all written tuples is the one written. If not,

somebody committed a new version and the transaction must abort

• Locks still used to avoid too many conflicts (table locks)

Transactions - 2PL 36

SI implementation (Oracle)

• Transactions create undo records of

every modification they make

• These undo records are kept in an

undo segment

• When a query/transaction starts, it

is assigned a SCN (System Change Number)

• Reads are over blocks with an SCN

lower than the start SCN

• If a block is too new, a copy is

made, and undo records applied to recreate a version with a suitable SCN

Transactions - 2PL 37

https://docs.oracle.com/cd/E25054_01/server.1111/e25789/consist.htm

Undo Tablespaces and segments

• Each block is used by a single

transaction

• Segments organized as a ring
• Transactions write undo records

using blocks assigned to them within a segment, when segment is full, continue on a new segment that does not have active transactions

• Several segments can be active at

the same time

Transactions - 2PL 38

https://docs.oracle.com/cd/E25054_01/server.1111/e25789/logical.htm#CHDGJJEJ

Read Consistency (Oracle)

Read Committed (SQL ANSI)

• Only read committed data
• Not repeatable reads
• May still read data that is

inconsistent by reading from two different transactions when it should read form only one:

w1[x]C1r3[x]w2[x]w2[y]c2r3[y] Read Consistency (Oracle)

• Read data that is “transaction

consistent”

• Repeatable read
• Will see a single version of the

database

• Will not see updates done after it

started

• Much stronger guarantee than

read committed

Transactions - 2PL 39

Flashback queries

• Undo records have a retention period
• For undoing active transactions, undo

records are needed only until the transaction commits

• But SI and flashback queries may require

those undo records to recreate older versions

• For SI, undo records only need to be kept

until there is no active transaction that would read them

• For flashback queries, they can be kept

much longer (or made persistent)

Transactions - 2PL 40

Undo and Recovery

• As proof of the deep interaction between concurrency control and

recovery, we have started discussing the undo records

• In other systems, also called the undo log
• Used for rolling back active transactions but also for recovery
• Flashback to an early, logical consistent point (user errors)
• Undoing committed transactions for database recovery
• We will discuss the undo log, redo log, and how they are used for

recovery in the next section.

Transactions - 2PL 41