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) Operation Balance (Account 2) $200 $100 time

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

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

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

7. Motivating Example 1 Transfer $100 from one bank account to another Balance (Account 1) Operation Balance (Account 2) $100 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 $200 time Require 4 database operations

8. Motivating Example 1 Transfer $100 from one bank account to another Balance (Account 1) Operation Balance (Account 2) $100 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 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 Read Read Num:39Max: 40 Num:39Max: 40 Student Enrollment Database NumEnrolled: 39 MaxEnrolled: 40

10. Motivating Example 2 Two students registering for a class Register Register 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 => 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? Mysql> show table status from database_name like ‘table_name’ How to create table with a particular storage engine? Mysql> create table tableName (id int, name char(20)) engine=innodb How to convert from MyISAM to INNODB? Mysql> alter table tableName engine=innodb

18. MySQL Example 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 | +------+-----------+ | 1 | 1000 | +------+-----------+ Mysql> commit; Client 2: Mysql> select * from account; Empty set (0.00 sec) Mysql> select * from account; +------+----------+ | id | balance | +------+-----------+ | 1 | 1000 | +------+-----------+

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

20. MySQL Example (Concurrency Control) Client 1: Mysql> create table acct2 (id int primary key, balance double) engine=innodb; Mysql> start transaction; Mysql> insert into acct2 values (1,1000); Query OK, 1 row affected (0.00 sec) Mysql> commit Client 2: Mysql> start transaction; Mysql> select * from acct2; Empty set (0.00 sec) Mysql> 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: Mysql> create table acct2b (id int primary key, balance double) engine=innodb; Mysql> start transaction; Mysql> insert into acct2b values (1,1000); Query OK, 1 row affected (0.00 sec) Mysql> rollback; Query OK, 0 row affected (0.00 sec) Client 2: Mysql> start transaction; Mysql> select * from acct2b; Empty set (0.00 sec) Mysql> 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: Mysql> create table acct3 (id int, balance double) engine=innodb; Mysql> start transaction; Mysql> insert into acct3 values (1,1000); Query OK, 1 row affected (0.00 sec) Mysql> select * from acct3; +------+------------+ | id | balance | +------+------------+ | 1 | 1000 | +------+------------+ Client 2: Mysql> start transaction; Mysql> select * from acct3; Empty set (0.00 sec) Mysql> 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: Mysql> commit; Mysql> select * from acct3; +------+------------+ | id | balance | +------+------------+ | 1 | 1000 | +------+------------+ Client 2: Mysql> select * from acct3; +------+------------+ | id | balance | +------+------------+ | 1 | 50 | +------+------------+ Mysql> select * from temp3; +------+------------+ | id | balance | +------+------------+ | 1 | 1000 | | 1 | 50 | +------+------------+ Mysql> 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 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

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. Rollback up to here 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.