Conception of Information Systems 2005

Exercise 3: Transactions

This exercise contains three parts. In the first part you will get familiar with transactions, observe the deadlock and learn how to undo the changes in the database in case of failure using rollback. You will work with the same server, username and database (bank) that you have been using in the previous exercise. In the second part you will learn about the schedule serialization and the serialization graph and in the third part you will understand database recovery principles after the crash using logging.

Part 1:

Getting familiar with local transaction commands: Write a JSP program that accesses the "account" and the "tx" tables in the problem database. Insert the following records using following SQL statements.

account: ('1', 'CHF', 'P',1900)

account: ('2', 'EUR', 'P',500)

tx: ('1', '1', -200,'W')

Put the statements into a transaction and commit the changes. See the lecture notes for the syntax (transactions lecture, 5th slide).

Deadlock: The record ('1', '1', -200,'W') in the "tx" table is an erroneous entry made by a bank employee after giving money to the client. The actual  amount taken by the client was 250CHF. So the amount in the transaction field should be changed from "-200" to "-250" and because of the 50 CHF difference the actual balance of the account should be decreased from 1900 to 1850 CHF, Two bank admins A & B were informed about the mistake and at the same time they decided to correct it.

 They use their local programs to make the necessary data management operations; both of them are very careful and decide to use their own transactions in their local JSP programs. However, since they are not so familiar with the transaction concept, they decide to do both corrections within a single transaction.

They take different orders to do the modifications: Admin A first wants to modify the table "account", pauses 10 seconds, and then modifies the table "tx"; on the contrary, B modifies the table "tx" first and pauses 10 seconds before correcting the typo in the table "account". Simulate such behaviors by introducing ".sleeps()" statements into the programs.

Write such two JSP files, execute them at the same time and observe the consequence. Describe what happens to the system and give an explanation. Use setAutoCommit(false) method to prevent auto commitment of each statement separately (see 5th slide).

Rollback: Add the new record in the "account" table and then the record in the "tx" table in one transaction. Simulate a crash (by raising an appropriate exception in java code) after inserting the record into the "tx" table. Rollback the transaction so that the account is not stored into the database because of the crash. Use rollback() method (see 5th slide).

Part 2: Schedule Serialization

2.1. List all the conflicts that may arise for this schedule.

2.2 Give a serialisation graph

         Each transaction represented by a node

         Each conflict (ri,wj / wi,rj/ wi,wj)
represented by a directed edge
from Ti to Tj

2.3 Is the schedule conflict-serializable? Why?

Part 3: Crash and Recovery

Operation  DB Buffer [page(LSN)] Database [page(LSN)] LogBuffer [LSN (page/transaction)] Log File [LSN (page/transaction)]
BOT(T1)     100(BOT/T1)  
BOT(T2)     101(BOT/T2)  
W1(p) p(102)   102(p/T1)  
W2(t) t(103)   103(t/T2)  
W1(q) q(104)   104(q/T1)  
EOT(T2)     105(EOT/T2) 100, 101,102, 103,104, 105
EOT(T1)     106(EOT/T1) 106
Flush(t)   t(103)    
System error        

3.1 Fill in the entries for page buffer, database, log buffer and log file for the given schedule!
            Start with  LSN 100
            Enter also BOT, EOT
            At a checkpoint enter all active transaction in log file


3.2 Perform a recovery after the system failure
            Determine winners and loosers
            Redo phase: which log pages have to be written anew into the database
            Undo phase: which pages have to be written anew into the database