CSE 480: Database Systems

1
CSE 480 Database Systems

• Lecture 23 Transaction Processing and Database
  Recovery

2
Online Transaction Processing Systems

• Systems that need real-time support for querying
  and updating of databases by one or more
  concurrent users
• Examples of OLTP
• Banking Credit card transaction processing
  systems
• Airline/Railway reservation systems
• Trading/Brokerage systems
• Online E-commerce (Amazon, Walmart, etc)
• What makes the requirements for OLTP different
  than other systems?
• Database gets updated in real time frequently,
  but it must always maintain correctness of the
  database state (in spite of failures and
  concurrent access)

3
Motivating Example 1

• Transfer 100 from one bank account to another

Balance (Account 1)
Balance (Account 2)
Operation
200
100
time
4
Motivating Example 1

• Transfer 100 from one bank account to another

Balance (Account 1)
Balance (Account 2)
Operation
1. Check balance of Account 1
200
100
time
5
Motivating Example 1

• Transfer 100 from one bank account to another

Balance (Account 1)
Balance (Account 2)
Operation
1. Check balance of Account 1 2. Reduce balance
of Account 1 by 100
100
100
time
6
Motivating Example 1

• Transfer 100 from one bank account to another

Balance (Account 1)
Balance (Account 2)
Operation
1. Check balance of Account 1 2. Reduce balance
of Account 1 by 100 3. Check balance of Account 2
100
100
time
7
Motivating Example 1

• Transfer 100 from one bank account to another

Balance (Account 1)
Balance (Account 2)
Operation
1. Check balance of Account 1 2. Reduce balance
of Account 1 by 100 3. Check balance of Account
2 4. Increase balance of Account 2 by 100
100
200
time
Require 4 database operations
8
Motivating Example 1

• Transfer 100 from one bank account to another

Balance (Account 1)
Balance (Account 2)
Operation
1. Check balance of Account 1 2. Reduce balance
of Account 1 by 100 3. Check balance of Account
2 4. Increase balance of Account 2 by 100
100
100
System crash (write operation fails)
time
Database is in an inconsistent state after system
failure!
9
Motivating Example 2

• Two students registering for the same class

Student Enrollment Database
NumEnrolled 39 MaxEnrolled 40
10
Motivating Example 2

• Two students registering for a class

Student Enrollment Database
NumEnrolled 40 MaxEnrolled 40
NumEnrolled 41 MaxEnrolled 40
Database is in an inconsistent state (violate
semantic constraint) when processing requests
from multiple concurrent users!
11
Challenges of OLTP

• Although your SQL code is written correctly, the
  database may still be in an inconsistent state
  after processing transactions due to
• System failures
• Concurrent processing of database operations
• A consistent state of the database means it
  satisfies all the constraints specified in the
  schema as well as any other constraints (e.g.,
  semantic constraints) on the database that should
  hold

12
What this chapter is about?

• This chapter is about
• Transactions
• DBMS support to ensure correctness of transaction
  processing
• Recovery manager to deal with system failures
• Concurrency control to process database
  operations requested by multiple users

13
Transactions

• A transaction is an executing program that forms
  a logical unit of database processing
• Examples
• Bank processing deposit/withdrawal transactions
• Student registration enrolment/withdrawal
  transactions
• Airline reservation reservation/cancellation
  transactions
• Each transaction consists of one or more database
  operations
• Example bank deposit transaction
• begin_transactionread_item(acct)acct.bal
  acct.bal amountwrite_item(acct)end_transaction

1 logical unit gt 1 transaction
14
ACID Properties of Transactions

• But transactions are no ordinary programs
• Additional requirements are placed on the
  execution of transactions beyond those placed on
  ordinary programs
• Atomicity
• Consistency
• Isolation
• Durability

15
ACID Properties of Transactions

• Atomicity
• A transaction must either run to its completion
  or, if it is not completed, has no effect at all
  on the database state
• Consistency
• A transaction should correctly transform the
  database from one consistent state to another
• Isolation
• A transaction should appear as though it is being
  executed in isolation from other transactions
• The execution of a transaction should not be
  interfered with by other transactions executing
  concurrently
• Durability
• Changes applied to the database by a committed
  transaction must persist in the database
• These changes must never be lost because of any
  failure

16
ACID Properties

• Ensuring consistency is the responsibility of
  application programmers
• Ensuring atomicity, isolation, and durability
  properties are the responsibilities of the DBMS
• Atomicity and durability properties are enforced
  by the recovery subsystem of DBMS
• Isolation property is enforced by the concurrency
  control subsystem of DBMS (next lecture)

17
Transaction Support in MySQL

• For transaction processing, make sure you use the
  INNODB storage engine (instead of MyISAM)
• How can we tell what type of storage structure
  used for each table?
• Mysqlgt show table status from database_name like
  table_name
• How to create table with a particular storage
  engine?
• Mysqlgt create table tableName (id int, name
  char(20)) engineinnodb
• How to convert from MyISAM to INNODB?
• Mysqlgt alter table tableName engineinnodb

18
MySQL Example

• Client 1
• Mysqlgt create table account (id int primary key,
  balance double) engine innodb
• Mysqlgt start transaction
• Mysqlgt insert into account values (1, 1000)
• Mysqlgt select from account
• ----------------
• id balance
• -----------------
• 1 1000
• -----------------
• Mysqlgt commit

• Client 2
• Mysqlgt select from account
• Empty set (0.00 sec)
• Mysqlgt select from account
• ----------------
• id balance
• -----------------
• 1 1000
• -----------------

19
MySQL Example (Aborted Transaction)

• Client 1
• Mysqlgt start transaction
• Mysqlgt insert into account values (1,1000)
• Mysqlgt select from account
• ----------------
• id balance
• -----------------
• 1 1000
• -----------------
• Mysqlgt rollback

• Client 2
• Mysqlgt select from account
• Empty set (0.00 sec)
• Mysqlgt select from account
• Empty set (0.00 sec)

DBMS will automatically undo the effect of
insertion
20
MySQL Example (Concurrency Control)

• Client 1
• Mysqlgt create table acct2 (id int primary key,
  balance double) engineinnodb
• Mysqlgt start transaction
• Mysqlgt insert into acct2 values (1,1000)
• Query OK, 1 row affected (0.00 sec)
• Mysqlgt commit

• Client 2
• Mysqlgt start transaction
• Mysqlgt select from acct2
• Empty set (0.00 sec)
• Mysqlgt insert into acct2 values (1,50)
• (Client 2 will be kept waiting until client 1
  commits or rollback)
• ERROR 1062 (00000) Duplicate entry '1' for key 1

21
MySQL Example (Concurrency Control)

• Client 1
• Mysqlgt create table acct2b (id int primary key,
  balance double) engineinnodb
• Mysqlgt start transaction
• Mysqlgt insert into acct2b values (1,1000)
• Query OK, 1 row affected (0.00 sec)
• Mysqlgt rollback
• Query OK, 0 row affected (0.00 sec)

• Client 2
• Mysqlgt start transaction
• Mysqlgt select from acct2b
• Empty set (0.00 sec)
• Mysqlgt insert into acct2b values (1,500)
• (Client 2 will be kept waiting until client 1
  commits or rollback)
• Query OK, 1 row affected (5.98 sec)

22
MySQL Example (Concurrency Control)

• Client 1
• Mysqlgt create table acct3 (id int, balance
  double) engineinnodb
• Mysqlgt start transaction
• Mysqlgt insert into acct3 values (1,1000)
• Query OK, 1 row affected (0.00 sec)
• Mysqlgt select from acct3
• ------------------
• id balance
• ------------------
• 1 1000
• ------------------

• Client 2
• Mysqlgt start transaction
• Mysqlgt select from acct3
• Empty set (0.00 sec)
• Mysqlgt insert into acct3 values (1, 50)
• Query OK, 1 row affected (0.00 sec)
• (OK because id is not primary key)

23
MySQL Example (Concurrency Control)

• Client 1
• Mysqlgt commit
• Mysqlgt select from acct3
• ------------------
• id balance
• ------------------
• 1 1000
• ------------------

• Client 2
• Mysqlgt select from acct3
• ------------------
• id balance
• ------------------
• 1 50
• ------------------
• Mysqlgt select from temp3
• ------------------
• id balance
• ------------------
• 1 1000
• 1 50
• ------------------
• Mysqlgt commit

24
Types of Failures

• Computer failure or system crash (e.g., media
  failure)
• Transaction/system error (e.g., integer overflow,
  division by zero, user interrupt during
  transaction execution)
• Local errors or exception conditions detected by
  the transaction (e.g., insufficient balance in
  bank account)
• Concurrency control enforcement (e.g., aborted
  transaction)
• Physical problems and catastrophes
• Recovery manager of DBMS is responsible for
  making sure that all operations in a transaction
  are completed successfully and their effect
  recorded permanently

25
Recovery

• For recovery purposes, the recovery manager of
  DBMS must keep track of the following operations
• BEGIN_TRANSACTION
• READ or WRITE
• END_TRANSACTION
• COMMIT_TRANSACTION
• This signals a successful end of the transaction
  so that any changes executed by the transaction
  can be safely committed to the database and will
  not be undone
• ROLLBACK (or ABORT)
• This signals that the transaction has ended
  unsuccessfully, so that any changes or effects
  that the transaction may have applied to the
  database must be undone.

26
Transaction State

• At any point in time, a transaction is in one of
  the following states
• Active state
• Partially committed state
• Committed state
• Failed state
• Terminated State

27
System Log

• Mechanism for dealing with failures is the system
  log
• A log is a sequence of records that describes
  database updates made by transactions
• Used to restore database to a consistent state
  after a failure
• Log should be stored on a different disk than the
  database
• Survives processor crash and media failure
• Log should be periodically backed up to archival
  storage (tape) to guard against catastrophic
  failures

28
System Log

• Types of entries in a log record (T
  transaction ID)
• start_transaction,T transaction T has started
  execution.
• write_item,T,X,old_value,new_value transaction
  T has changed the value of database item X from
  old_value to new_value
• Old_value is called before image (BFIM)
• New_value is called after image (AFIM)
• read_item,T,X transaction T has read the value
  of X.
• commit,T transaction T has completed
  successfully, and affirms that its effect can be
  committed (recorded permanently) to the database.
• abort,T transaction T has been aborted
• If the system crashes, we can recover to a
  consistent database state by examining the log

29
Commit Point

• A transaction reaches its commit point when
• All of its database operations have been executed
  successfully
• Effect of all the operations has been recorded in
  the log
• The transaction then writes an entry commit,T
  into the log
• Beyond the commit point, the transaction is said
  to be committed, and its effect is permanently
  recorded in the database

30
Recovery from Transaction Aborts

• When a transaction T aborts
• Scan the log backward (rollback)
• Apply the before image in each of the
  transactions update records to database items to
  restore them to their original state.
• Scan the log backward up to Begin_transaction for
  T
• Write an entry abort, T into the log

31
Example

• Suppose transaction T2 is aborted

B begin transaction U update record
B1
U1
B2
U1
U2
U1
U2
End of log when T2 is aborted
Begin rollback scan
32
Example

• Suppose transaction T2 is aborted

B begin transaction Ui update record
of Transaction i
B1
U1
B2
U1
U2
U1
U2
Undo changes made by T2
Rollback scan
33
Example

• Example Aborting transaction T2

B begin transaction Ui update record of
transaction i
B1
U1
B2
U1
U2
U1
U2
No need toundo changes made by T1
Rollback scan
34
Example

• Example Aborting transaction T2

B begin transaction U update record
B1
U1
B2
U1
U2
U1
U2
Undo changes made by T2
Rollback scan
35
Example

• Example Aborting transaction T2

B begin transaction U update record
B1
U1
B2
U1
U2
U1
U2
No need toundo changes made by T1
Rollback scan
36
Example

• Example Aborting transaction T2

B begin transaction U update record
B1
U1
B2
U1
U2
U1
U2
End of rollback scan when T2 is aborted
Rollback scan
37
Example

• Example Aborting transaction T2

B begin transaction U update record
B1
U1
B2
U1
U2
U1
U2
A2
Add entry forabort T2 to log
38
Recovery from System Crash

• More complicated than rollback due to aborted
  transaction
• After system crash, active transactions must be
  identified and aborted when the system recovers
• When scanning the log backwards
• if the first record encountered for a transaction
  is an update record, the transaction must still
  be active
• If the first record encountered for a transaction
  is a commit/abort record, the transaction has
  already completed and thus can be ignored

39
Example
B - begin U - update C - commit A - abort
Crash

• The Commit/Abort records are insufficient to
  identify active transactions
• How far back should we scan to determine the
  active transactions when the system crashes?

40
Checkpointing

• Need a better mechanism to identify active
  transactions so that the recovery process can
  stop backward scan
• System periodically appends a checkpoint record
  that lists all the currently active transactions
• During recovery, system must scan backward at
  least to the last checkpoint record
• If all active transactions recorded in the
  checkpoint record has committed prior to system
  crash, recovery process can stop
• If some active transactions recorded in the
  checkpoint record has not committed prior to
  system crash, backward scan must continue past
  the checkpoint record until the begin records for
  such transactions are encountered

41
Example
42
Log and Database Updating

• Both the log and database must be updated when a
  transaction modifies an item.
• Which one should be updated first?
• Update the log first or update the database
  first?
• What if system crashes when one is updated but
  not the other?

43
Write-Ahead Log

• DBMS use a write-ahead log
• Update the record in log first before applying
  the update to database item
• If database is updated first and system crashes
  before log is updated
• On recovery, database item is in the new state
  but there is no before image to roll it back.
  Transaction cannot be aborted.
• If log is updated first and system crashes before
  log is updated
• On recovery, database item in old state and
  before image in log. Converting After image to
  Before image has no effect.