CSE 480 Database Systems Lecture 23 Transaction Processing

CSE 480: Database Systems Lecture 23: Transaction Processing and Database Recovery 1

Online Transaction Processing Systems l Systems that need real-time support for querying and updating of databases by one or more concurrent users l Examples of OLTP: – – l 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) 2

Motivating Example 1 l Transfer $100 from one bank account to another Balance (Account 1) $200 Operation Balance (Account 2) $100 time 3

Motivating Example 1 l Transfer $100 from one bank account to another Balance (Account 1) $200 Operation 1. Check balance of Account 1 Balance (Account 2) $100 time 4

Motivating Example 1 l Transfer $100 from one bank account to another Balance (Account 1) $100 Operation 1. Check balance of Account 1 Balance (Account 2) $100 2. Reduce balance of Account 1 by $100 time 5

Motivating Example 1 l Transfer $100 from one bank account to another Balance (Account 1) $100 Operation 1. Check balance of Account 1 Balance (Account 2) $100 2. Reduce balance of Account 1 by $100 3. Check balance of Account 2 time 6

Motivating Example 1 l Transfer $100 from one bank account to another Balance (Account 1) $100 Operation 1. Check balance of Account 1 Balance (Account 2) $200 2. Reduce balance of Account 1 by $100 3. Check balance of Account 2 4. Increase balance of Account 2 by $100 time Require 4 database operations 7

Motivating Example 1 l Transfer $100 from one bank account to another Balance (Account 1) $100 Operation 1. Check balance of Account 1 Balance (Account 2) $100 2. Reduce balance of Account 1 by $100 3. Check balance of Account 2 4. Increase balance of Account 2 by $100 System crash (write time operation fails) Database is in an inconsistent state after system failure! 8

Motivating Example 2 l Two students registering for the same class Student Enrollment Database Read Num. Enrolled: 39 Read Max. Enrolled: 40 Num: 39 Max: 40 9

Motivating Example 2 l Two students registering for a class Student Enrollment Database Register Num. Enrolled: 41 40 Register Max. Enrolled: 40 Database is in an inconsistent state (violate semantic constraint) when processing requests from multiple concurrent users! 10

Challenges of OLTP l 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 l 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 11

What this chapter is about? l This chapter is about – Transactions – DBMS support to ensure correctness of transaction processing u Recovery manager – to deal with system failures u Concurrency control – to process database operations requested by multiple users 12

Transactions l A transaction is an executing program that forms a logical unit of database processing – Examples: u Bank processing – deposit/withdrawal transactions u Student registration – enrolment/withdrawal transactions u Airline reservation – reservation/cancellation transactions – Each transaction consists of one or more database operations – Example: bank deposit transaction begin_transaction read_item(acct) acct. bal : = acct. bal + amount write_item(acct) end_transaction 1 logical unit => 1 transaction 13

ACID Properties of Transactions l But transactions are no ordinary programs l Additional requirements are placed on the execution of transactions beyond those placed on ordinary programs – – Atomicity Consistency Isolation Durability 14

ACID Properties of Transactions l Atomicity – A transaction must either run to its completion or, if it is not completed, has no effect at all on the database state l Consistency – A transaction should correctly transform the database from one consistent state to another l 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 l 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 15

ACID Properties l Ensuring consistency is the responsibility of application programmers l 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) 16

Transaction Support in My. SQL l For transaction processing, make sure you use the INNODB storage engine (instead of My. ISAM) l How can we tell what type of storage structure used for each table? Mysql> show table status from database_name like ‘table_name’ l How to create table with a particular storage engine? Mysql> create table. Name (id int, name char(20)) engine=innodb l How to convert from My. ISAM to INNODB? Mysql> alter table. Name engine=innodb 17

My. SQL Example Client 2: Client 1: Mysql> create table account (id int primary key, balance double) engine = innodb; Mysql> start transaction; Mysql> insert into account values (1, 1000); Mysql> select * from account; +----------+ | id | balance | +-----------+ | 1000 | Mysql> select * from account; Empty set (0. 00 sec) +-----------+ Mysql> commit; Mysql> select * from account; +----------+ | id | balance | +-----------+ | 1000 | +-----------+ 18

My. SQL Example (Aborted Transaction) Client 2: Client 1: Mysql> start transaction; Mysql> insert into account values (1, 1000); Mysql> select * from account; +----------+ | id | balance | +-----------+ | 1000 | +-----------+ Mysql> select * from account; Empty set (0. 00 sec) Mysql> rollback; Mysql> select * from account; Empty set (0. 00 sec) DBMS will automatically undo the effect of insertion 19

My. SQL Example (Concurrency Control) Client 1: Client 2: Mysql> create table acct 2 (id int primary key, balance double) engine=innodb; Mysql> start transaction; Mysql> insert into acct 2 values (1, 1000); Query OK, 1 row affected (0. 00 sec) Mysql> start transaction; Mysql> select * from acct 2; Empty set (0. 00 sec) Mysql> insert into acct 2 values (1, 50); (Client 2 will be kept waiting until client 1 commits or rollback) Mysql> commit ERROR 1062 (00000): Duplicate entry '1' for key 1 20

My. SQL Example (Concurrency Control) Client 1: Client 2: Mysql> create table acct 2 b (id int primary key, balance double) engine=innodb; Mysql> start transaction; Mysql> insert into acct 2 b values (1, 1000); Query OK, 1 row affected (0. 00 sec) Mysql> start transaction; Mysql> select * from acct 2 b; Empty set (0. 00 sec) Mysql> insert into acct 2 b values (1, 500); (Client 2 will be kept waiting until client 1 commits or rollback) Mysql> rollback; Query OK, 1 row affected (5. 98 sec) Query OK, 0 row affected (0. 00 sec) 21

My. SQL Example (Concurrency Control) Client 1: Client 2: Mysql> create table acct 3 (id int, balance double) engine=innodb; Mysql> start transaction; Mysql> insert into acct 3 values (1, 1000); Query OK, 1 row affected (0. 00 sec) Mysql> select * from acct 3; +------------+ | id | balance | +------------+ | 1000 | +------------+ Mysql> start transaction; Mysql> select * from acct 3; Empty set (0. 00 sec) Mysql> insert into acct 3 values (1, 50); Query OK, 1 row affected (0. 00 sec) (OK because id is not primary key) 22

My. SQL Example (Concurrency Control) Client 1: Client 2: Mysql> select * from acct 3; +------------+ | id | balance | +------------+ | Mysql> commit; 1 | 50 | +------------+ Mysql> select * from acct 3; +------------+ | id | balance | +------------+ | 1000 | +------------+ Mysql> select * from temp 3; +------------+ | id | balance | +------------+ | 1000 | 1 | 50 | | +------------+ Mysql> commit; 23

Types of Failures l l l 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 24

Recovery l For recovery purposes, the recovery manager of DBMS must keep track of the following operations – – BEGIN_TRANSACTION READ or WRITE END_TRANSACTION COMMIT_TRANSACTION u 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) u 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. 25

Transaction State l 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 26

System Log l Mechanism for dealing with failures is the system log l 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 u Survives processor crash and media failure – Log should be periodically backed up to archival storage (tape) to guard against catastrophic failures 27

System Log l 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) u New_value is called after image (AFIM) u – [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 l If the system crashes, we can recover to a consistent database state by examining the log 28

Commit Point l 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 l The transaction then writes an entry [commit, T] into the log l Beyond the commit point, the transaction is said to be committed, and its effect is permanently recorded in the database 29

Recovery from Transaction Aborts l When a transaction T aborts: – Scan the log backward (rollback) – Apply the before image in each of the transaction’s 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 30

Example l Suppose transaction T 2 is aborted B 1 U 1 B 2 U 1 U 2 B – begin transaction U – update record End of log when T 2 is aborted Begin rollback scan 31

Example l Suppose transaction T 2 is aborted B 1 U 1 B 2 U 1 U 2 B – begin transaction Ui – update record of Transaction i Undo changes made by T 2 Rollback scan 32

Example l Example: Aborting transaction T 2 B 1 U 1 B 2 U 1 U 2 B – begin transaction Ui – update record of transaction i No need to undo changes made by T 1 Rollback scan 33

Example l Example: Aborting transaction T 2 B 1 U 1 B 2 U 1 U 2 B – begin transaction U – update record Undo changes made by T 2 Rollback scan 34

Example l Example: Aborting transaction T 2 B 1 U 1 B 2 U 1 U 2 B – begin transaction U – update record No need to undo changes made by T 1 Rollback scan 35

Example l Example: Aborting transaction T 2 B 1 U 1 B 2 U 1 U 2 B – begin transaction U – update record End of rollback scan when T 2 is aborted Rollback scan 36

Example l Example: Aborting transaction T 2 B 1 U 1 B 2 U 1 U 2 A 2 B – begin transaction U – update record Add entry for abort T 2 to log 37

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

Example B - begin U - update C - commit A - abort l Crash The Commit/Abort records are insufficient to identify active transactions l How far back should we scan to determine the active transactions when the system crashes? 39

Checkpointing l Need a better mechanism to identify active transactions so that the recovery process can stop backward scan l 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 40

Example Rollback up to here 41

Log and Database Updating l Both the log and database must be updated when a transaction modifies an item. l 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? 42

Write-Ahead Log l DBMS use a write-ahead log – Update the record in log first before applying the update to database item l 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. l 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. 43