Transaction Management
Ed Knorr, Fall 2020, CPSC 404
Based on:
Chapter 16 of Ramakrishnan & Gehrke (textbook) with an introduction to content from Chapters 17 & 18
1
Some Learning Goals
Define the terms transaction, atomicity, and consistency; and describe the relationship among those terms.
Give reasons for allowing concurrency in a database system, and provide a case where concurrency is undesired.
Provide reasons for why a transaction may abort.
Explain how the strict two-phase locking protocol works, and explain
why it is useful.
Explain the purpose of log records in a database system.
2
Transactions
A transaction (tx.) is the DBMS’s abstract view of a user program; it is a sequence of reads and/or writes.
– e.g., withdraw cash from an account at a bank’s ATM
– There could be thousands of SQL statements in one transaction.
– How big should a transaction be?
Concurrent execution of user programs (concurrency) is essential for DBMSs.
– DBs applications are often concurrent. Examples:
– Since disk accesses are frequent, and relatively slow, it is OK to run many user programs concurrently.
Disk access time >> CPU time
3
– Foreign key constraints
Consistency
– Each transaction must leave the DB in a consistent state.
“Consistent” means the DBMS will guarantee the enforcement of all IC’s (whether declared in the schema or elsewhere).
– Domain constraints
– Primary/candidate key constraints
– General constraints (e.g., assertions, triggers)
– Triggers can implement more complicated constraints, upon changes to a table (e.g., before or after an insert or update)
Beyond this, the DBMS does not really understand the semantics (meaning) of the data.
– e.g., it does not understand how the interest on a bank account is computed
4
Concurrency
Users submit transactions, and can think of each transaction as executing all by itself (in isolation).
Concurrency is achieved by the DBMS, which interleaves the actions (reads/writes of DB objects) of various transactions.
Key issues include the effect of:
– interleaving transactions (integrity/consistency) – crashes
5
– How can a transaction abort? Cancelled by user at the terminal
Commit/Abort
A transaction might commit after completing all its actions, or it could abort (or be aborted by the DBMS) after executing some actions. Abort means rollback, that is, roll back the transaction; so, it’s as if the transaction never started, and the DB is unaffected.
Cancelled by operations/tech staff
Cancelled by program logic, intentionally
Run-time error
– e.g.,Dividebyzero(intentionalorunintentional)
Hardware crash
A conflicting transaction (more about this shortly) etc.
6
Atomicity = “all or none” principle
Atomicity
A very important property guaranteed by the DBMS for all transactions is that transactions are atomic.
– i.e., A user can think of a transaction as always executing all of its actions in one step, or not executing any actions at all.
The DBMS logs all actions so that it can undo the actions of aborted transactions.
7
– read/write actions
– lock/unlock actions
Schedules
Schedule: a time-ordered sequence of the important actions taken by one or more transactions
– Note: read/writes involve buffers in main memory, not necessarily physical I/Os to, or from, disk
Serial schedule: a schedule that does not interleave the actions of different transactions
– e.g., Consider two transactions Ti and Tj :
– DoallofTi,thenallofTj or
– Do all of Tj, then all of Ti
8
Transaction Example (Banking)
Consider two transactions:
T1: BEGIN A=A+100, B = B–100 END
T2: BEGIN A=1.01*A, B = 1.01*B END
Intuitively, the first transaction is transferring $100 to A’s account from B’s account. The second is crediting both accounts with a 1% interest payment.
There is no guarantee that T1 will execute before T2 or vice- versa, if both are submitted together. However, the net effect must be equivalent to these two transactions running serially in some order.
9
Transaction Example, Scheduling
Consider a possible (interleaving) schedule:
T1: A=A+100,
T2: A=1.01*A,
B=B-100
B=1.01*B
This is OK. But what about:
T1: A=A+100, T2:
B=B-100 A=1.01*A, B=1.01*B
The DBMS’s view of the second schedule:
T1: R(A), W(A), T2:
R(B), W(B) R(A), W(A), R(B), W(B)
10
More on Schedules
Equivalent schedules: For any database state, the effect (on the set of objects in the database) of executing the first schedule is identical to the effect of executing the second schedule.
Serializable schedule: a schedule that is equivalent to some serial execution of the transactions
Ifeachtransactionpreservesconsistency,everyserializable schedule preserves consistency.
11
Anomalies with Interleaved Execution
Reading Uncommitted Data (WR Conflicts, “dirty reads”): T1: R(A), W(A), R(B), W(B), Abort
T2: R(A), W(A), C
Unrepeatable Reads (RWR Conflicts):
T1: R(A), R(A), W(A), C
T2: R(A), W(A), C
Normally, we want serializable transactions; but, sometimes we’re OK with a different isolation level.
12
Anomalies (cont.)
Overwriting Uncommitted Data (WW Conflicts): T1: W(A), W(B), C
T2: W(A), W(B), C
Suppose T1 sets an interest rate charge of 5%, and T2 sets an interest rate charge of 10%.
In this example, instead of A and B both paying 5% or both paying 10% (depending on the serial order), we get 10% and 5%, for A and B, respectively. This is not good.
– In reality, the application programmers should re-think T1 and T2; but, from the DBMS’s perspective, it shouldn’t interleave such conflicting operations.
Another problem: If T1 aborts late, instead of committing, what value of A do we roll back to when we “undo” T1?
13
Lock-Based Concurrency Control
Strict Two-Phase Locking (Strict 2PL) Protocol
– Each transaction must obtain an S (shared) lock on the DB object before reading it, and an X (exclusive) lock on the DB object before writing it.
– All locks that are held by a transaction are released when the transaction completes.
Non-strict2PL(“2PL”)allowsavariantofthis,wherebylocks can be released before the end of the transaction, but a transaction cannot request new locks once it releases any lock.
14
Lock-Based Concurrency Control (cont.)
If a transaction holds an X lock on an object, no other transaction can get a lock (either S or X) on that object.
Summary of Strict 2PL:
– All lock requests must precede all unlock requests
– Phase 1: acquisition of locks
– Phase 2: release of locks at the end of the transaction
Strict 2PL allows only serializable schedules.
15
Aborting a Transaction
If a transaction Ti is aborted, all its write actions have to be undone. Not only that, but if Tj reads an object last written by Ti , then Tj must be aborted, as well.
Most systems avoid such cascading aborts by releasing a transaction’s locks only at commit time.
– If Ti writes an object, Tj can read this only after Ti commits.
In order to undo the actions of an aborted transaction, the DBMS maintains a log in which every write is recorded. When recovering from system crashes, all active transactions at the time of the crash are aborted when the system comes back up.
16
The following actions are recorded in the log:
1.
Whenever Ti writes an object:
Theoldvalueandthenewvaluearerecorded.
disk.
Whenever Ti commits/aborts:
The Log
– Write-ahead logging (WAL):
– Thelogrecordmustgotodiskbeforethechangedpageiswrittento
2.
Log records are chained together by transaction ID, so it’s
Alogrecordindicatesthisaction
easy to find and undo a specific transaction.
The log is often duplexed and archived on stable storage.
– Stable storage can survive media failures, via dual writes.
Log-related activities (and in fact, all concurrency control activities such as locking, unlocking, dealing with deadlocks, etc.) are handled automatically by the DBMS.
17
ARIES Crash Recovery Algorithm
ARIES = “Algorithms for Recovery and Isolation Exploiting Semantics”
There are 3 phases in the ARIES recovery algorithm:
1. Analysis: Scan the log forward (from the most recent checkpoint)
to identify:
– all transactions that were active (“in flight”)
– all dirty pages in the buffer pool at the time of the crash
2. Redo: Re-does all updates to dirty pages in the buffer pool, as needed, to ensure that all logged updates are in fact carried out and written to disk.
18
ARIES (cont.)
3. Undo: The writes of all transactions that were active at the time of the crash are undone (by restoring the before value of the update, which is found in the log record for the update).
– We work backwards in the log.
– Some care must be taken to handle the case of a crash occurring during the recovery process.
– This is not a problem for ARIES—we can recover from a crash that takes place during a crash recovery.
19
–
Operates in full or incremental mode:
Full: backs up everything in the table(space)—i.e., all of the
Backup & Recovery
These next few slides focus on general backups and recoveries.
Backup and recovery are important parts of a DBA’s job. An image copy of a table is a backup.
– –
Do on a regular basis (e.g., daily, weekly, monthly)
Do before and after important jobs, especially jobs with lots of updates
tables
Incremental: only backs up the changes since the last full or incremental backup (e.g., backup 5,000 pages that changed, instead of all 500,000 pages in a table)
20
Backup & Recovery (cont.)
Backups are useful for creating a test environment, too. – How?
Be sure to make on-site and off-site image copies. – The latter could involve the cloud.
DBMS logs are a form of “backup” themselves, but we don’t want to store these (very large) logs for too long.
– They’re too slow to recover to a particular point in time.
– Furthermore, perhaps << 1% of the log records apply to your situation.
21
Abort/rollback a transaction
Types of Recovery
Automatically done by the DBMS at certain points (see Concurrency units); could be initiated by the user cancelling a transaction
Point-in-time recovery
Very common type of recovery, managed by DBA
Recover to a specific image copy determined by the programming or support team
Crash recovery (DBMS goes down) Automatically handled by the DBMS upon restart
Disaster recovery (major business disruption)
The organization needs to plan for this. A major effort. Expensive.
22
Payroll Example: Image Copies: Backup & Recovery
[02:00] Stop online access to users, and place specific tables in utility-only access mode
[02:00] Take image copies of all relevant tables = “Before case”
[02:10] Run payroll system batch updates – May involve many jobs and many tables
[03:10] Take image copies = “After case”
[03:20] Bring tables back online
23
Payroll Example (cont.)
[03:20] DB available to users for queries and updates
[09:05] Realization that last night’s updates were incorrectly done due to problems with input coming from another application
[09:10]
– DBA initiates recovery by taking tables offline
– Users/managers/help desk notified
– DBA recovers tables to the “before” case from last night
– Jobs from last night are re-run with correct data
[11:00] Payroll tables become available again
24
Summary
Concurrency control and recovery are among the most important functions provided by a DBMS.
Users need not worry about concurrency.
– The DBMS automatically inserts lock/unlock requests and schedules actions of different transactions in such a way as to ensure that the resulting execution is equivalent to executing the transactions one after the other in some serial order.
Write-ahead logging (WAL) is used to undo the actions of aborted transactions and to restore the system to a consistent state after a crash.
Crash recovery is different than application recovery.
Backup and recovery are an important parts of a DBA’s job.
25