Transactions & Recovery
- 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