Seminar Week 11: Transaction Processing

Transactions & Recovery

1. Scenario

John Smith has current and savings accounts with one of the popular UK banks (Current account No. 1234567890, Savings account No. 2345678901). His salary is directly transferred to his current account at the end of each month. Immediately after themonthly payment he transfers part of it to his savings account for later use, while the rest he keeps in his current account for withdrawing money from ATM machines using a debit card. The bank information system of his bank maintains a database, which represents all bank accounts using the following relation

account(accNo:INTEGER,accType:CHAR,holdName:STRING,addr:STRING,balance:MONEY)

where accTypecan have one of the following values: C for current, S for savings, R for credit, I for investment.

2. Tasks for the seminar

a)Identify the initial, final and intermediate states of the bank database, which can be changed by the banking operations included in a typical bank transaction - e.g. for money transfer, for cash withdrawal, etc.; specify all the operations together with theirparameters.

b)Identify all possible transaction states which can be encountered during executing of each of the above transactions and propose checkpoints for explicit control of the transactions

c)Draw diagrams for modeling database state transition during cash withdrawal and bank transfer transactions (Hint: You may consider using UML sequence diagrams, process modeling diagrams, or other process flow charttechniques)

d)Draw diagrams for modeling transaction progress for the above transactions (Hint: You may consider using UML state diagrams, data flow modeling techniques, or simple state chart)

e)Write PL/SQL proceduresfor controlof thetransactions according to the above diagrams (Hint: assume all input/output operations are performed using predefined functions for each data type, e.g. InputString, OutputString, etc.)

f)Please explain how the implementation of ACID properties by the DBMS can influence the way bank system operates in the following cases:transactions are managed without atomicity, isolation is not guaranteed, consistency is not checked

g)Supposing transaction processing have to be controlled explicitly by the application itself, propose database schema for logging of all bank transactions. It should allow safe recovery of the database in the case of failure (Note: You can use ER model, UML or even SQL DDL to specify it).

h)Design transaction log formatfor file log which can be used to recoverthe database in the case of failureaccording to the above schema. Show log entries forthe first cash withdrawal of 50 pounds, assuming John Smith executesit through his VISA debit card No. 9876543210 using PIN code 1234. Assume also that thiswithdrawal happensafter the payment of his monthly salary of 1800 pounds hasbeen done, and after 1/3of it hasalready been transferred to his savings account. (Note: You can use SQL DML for insertinginto the logging tablesor plain English to describe the log format).
Sample Solutions

a)Database operations

Transaction: MoneyTransfer

Parameters: sourceAccNo, destAccNo, sourceBalance, destBalance, transferSum

Initial state: sourceBalance = X, destBalance = Y

Final state: sourceBalance = X – transferSum, destBalance = Y + transferSum

Operations: Approve, Locate, Authorize, Debit, Credit

Intermediate states: Sum requested, Transfer approved/rejected,Source located, Transfer initiated/aborted, Source debited, Destination credited

Transaction:CashWithdrawal

Parameters: accNo, cardNo, cardPIN, PINcode, cashSum, balance

Initial state: balance = Y

Final state: balance = Y – cashSum

Operations: Login, Authenticate, Request, Authorize, Debit, Handle

Intermediate states: Card entered, Credentials supplied, Access allowed/denied, Sum requested, Withdrawal confirmed/rejected,Account debited, Cash received

b)Transaction states

Transaction: MoneyTransfer

Transaction states: Transaction started, Request entered, Balance checked, Destination located, Source updated, Destination updated, Success, Failure

Possible savepoints: Before entering request (to allow next transfer), Before locating destination account (to allow alternative transfer)

Transaction: CashWithdrawal

Transaction states: Transaction started, Credentials entered, Request entered, Balance checked, Balance updated, Success, Failure

Possible savepoints: Before entering credentials (to allow new authentication), Before entering request (to allow next operation)

c)Control Flow diagram

d)Cash withdrawal transaction state diagram

e)PLSQL procedure for transaction control

procedure cashWithdrawal (cardID INTEGER) is

PINcode, PINkeyed, accNo integer;

requestedSum, availSummoney;

error, deficit exception;

begin

PINkeyed := inputInteger(‘Please, enter Your PIN’); -- input pin code

SELECT c.pincode, c.accnoINTOPINcode, accNo -- read card pin

FROM cards c

WHERE c.cardid = cardID;

ifnot (PINcode = PINkeyed) thenraiseerror; -- authentication

requestedSum := inputMoney(‘Enter cash amount’); -- input request

SELECT a.balance INTO availSum -- read account balance

FROM accounts a

WHERE a.accno = accNo;

if(requestedSum > availableSum) thenraise deficit;-- authorization

UPDATE accountsa -- write balance

SET a.balance = a.balance - requestedSum

WHERE a.accno = accNo;

outputString(‘Operation successful’);

COMMIT; -- commit transaction

exception

outputString(‘Operation cancelled’); -- rollback transaction

whenerror or deficitthen ROLLBACK; -- logical problems

when others then ROLLBACK; -- physical problems

end;

f)ACID properties influence

If the DBMS does not guarantee all ACID properties of the transactions, then the application must control in parallel both transaction data manipulationoperations and system transaction processing operations

• If atomicity of the transaction is not guaranteed, in practice it means implicit COMMIT after each SQL statement processed, and all database states become true transactional states. This situation can happen when data processing is performed over the Web, because of the lack of continuous persistent sessions with the database. To prevent this all transaction states and parameters should be controlled explicitly by the application itself using ordinary non-transactional DML statements (i.e., INSERT/UPDATE in dedicated transactional tables). Each WRITE operation in such a case, for example, should be enclosed in a pair of INSERT statements, which control the execution of the transaction. If it is not done properly, we can come to a situation, when we lose money.
• In order to guarantee isolation of the transactions, database operations should explicitly lock the database tables for preventing intervention while the current transaction is active. This situation is typical for some desktop databases, which usually do not work in client/server mode (e.g. Access). If it is not managed properly, we can easily come to a database state when the accounts do not balance.
• When DBMS does not guarantee data consistency (e.g. MySQL), then the application should enforce all referential integrity constraints itself. If it is not managed correctly, we can come to a database state with foreign keys containing nulls where values are expected and this could cancel transactions, which are entirely correct and make some operations impossible.

g)LoggingSchema

operation (transID:STRING, opID:INTEGER, opType:STRING,

opName:STRING,argName:STRING, newVal:DATA, oldVal:DATA)

h)Transaction Log Entries

Begin [T1, 0, MoneyTransfer]no args, internal

Input [T1, 1, transferSum, 600.00]1 arg, external

Input [T1, 2, sourceAccNo, 1234567890]1 arg, external

Input [T1, 3, destAccNo, 2345678901]1 arg, external

Read[T1, 4, sourceBalance, 1800.00]1 arg, internal

Read[T1, 5, destBalance, 0.00]1 arg, internal

Write [T1, 6, sourceBalance, 1800.00, 1200.00]2 args, internal

Write [T1, 7, destBalance, 0.00, 600.00]2 args, internal

Commit[T1, 8]no args,internal

Begin[T2, 0, CashWithdrawal]no args,internal

Input[T2, 1, cardNo, 9876543210]1 arg, external

Input[T2, 2,PIN,1234]1 arg, external

Read[T2, 3, accNo, 1234567890]1 arg, internal

Input[T2, 4, cashSum, 50.00]1 arg, external

Read[T2, 5, balance, 1200.00]1 arg, internal

Write[T2, 6, balance, 1200.00,1150.00] 2 args, internal

Commit[T2, 7]no args,internal