Introduction to Transaction Processing

1
Introduction to Transaction Processing

• DBMS

2
Typical OLTP Environments

• Airline/ Railway Reservation Systems
• Banking Systems (ATM, EFT, ...)
• Trading and Brokerage Systems
• Hotel / Hospital Systems
• Standard Commercial Systems

3
Types of Failures

• occurs several times a week
• recovery required in a few minutes

4
Types of Failures

• occurs intermittently
• recovery time depends on the nature of failure

5
Types of Failures

• occurs once or twice a year
• recovery required in a few hours

6
Types of Failures

• occurs 10-100 times in a minute
• recovery required in transaction execution time

7
Motivating Scenario
0 (Acct.2565)? bal 2500
3 (Acct.165)? bal - 2500
Bank 1
Bank 2
Account 165 from bank 2 sends Rs. 2500/- to
Account 2565 in bank 1.
8
Motivating Scenario
0 (Acct.2565)? bal 2500
3 (Acct.165)? bal - 2500
Bank 1
Bank 2
Bank 2 crashes at time2
Account 165 from bank 2 sends Rs. 2500/- to
Account 2565 in bank 1.
9
Motivating Scenario
0 (Acct.2565)? bal 2500
1 (Acct.165)? bal - 1500 3 (Acct.165)? bal -
2500
Bank 1
Bank 2
Account 165 from bank 2 sends Rs. 2500/- to
Account 2565 in bank 1.
10
Transactions

• A transaction is a logical unit of program
  execution
• A combination of database updates which have to
  be performed together

11
What is a Transaction?
A logical unit of work.
12
What is a Transaction?
A unit of work with respect to concurrency and
recovery.
13
What is a Transaction?
A sequence of operationsincluding database
operationsthat is atomic with respect to
concurrency and recovery.
14
What is a Transaction?
An atomic execution unit that, when applied to a
consistent database, generates a consistent but
possibly different database.
15
What is a Transaction?
A short sequence of operations with the database
which represents one meaningful activity in the
user's environment.
16
ACID Property of Transactions

• Atomicity Either all updates are performed or
  none
• Consistency If the database state at the start
  of a transaction is consistent, it will be
  consistent at the end of the transaction
• Isolation When multiple transactions are
  executed concurrently, the net effect is as
  though each transaction has executed in isolation
  
• Durability After a transaction completes
  (commits), its changes are persistent

17
Atomicity
Consider the case of funds transfer from account
A to account B. A.bal - amount B.bal
amount A.bal - amount CRASH RECOVERY A.b
al amount
Rollback
18
Consistency
Consider the case of funds transfer from account
A to account B. A.bal - amount B.bal
amount B.bal amount A.bal - amount
(FAILS!! As balance is 0) B.bal - amount
Rollback
19
Isolation
Consider the case of funds transfer from account
A to account B. Transaction T1 A.bal -
amount (Let As balance become 0 after
this) B.bal amount Transaction T2 A.bal
- amount2 Net effect should be either T1,T2
(in which case T2 fails) or T2,T1 (in which case
T1 fails)
20
Durability
Consider the case of funds transfer from account
A to account B. Account A should have a balance
of amount Transaction T1 A.bal - amount
B.bal amount Commit Account A should have
a balance of 0.
21
Transaction States

• Active Initial state when the transaction is
  executing
• Partially Committed When the last statement has
  finished execution
• Failed On discovery that normal execution can no
  longer proceed
• Aborted After the rollback is performed from a
  failed transaction
• Committed After successful completion
• Terminated Either committed or aborted

22
Transaction States
Partially Committed
Committed
Active
Failed
Aborted
23
ACD using Shadow Copy

• Simple but extremely inefficient implementation
• Assumes database to be a file
• Assumes only one transaction is active at any
  time

24
ACD using Shadow Copy
DB
Copy of DB
DB
DB
25
ACD Using Shadow Copy

• Atomicity Delete old DB on commit or new DB on
  Failed transactions (all or nothing)
• Consistency Delete new DB, if update fails.
• Isolation Not supported..
• Durability One of the copies is persistent

26
Serializability
On executing a set of concurrent transactions on
a database the net effect should be as though
the transactions were executed in some serial
order.
Transaction T2 read (A) t A 0.1 A
A t write (A) read (B) B B t
write (B)
Transaction T1 read (A) A A 50 write
(A) read (B) B B 50 write (B)
27
Serial Schedules
read (A) A A 50 write (A) read (B) B
B 50 write (B)
Equivalent to T1 followed by T2
read (A) t A 0.1 A A t write (A)
read (B) B B t write (B)
28
Serial Schedules
read (A) t A 0.1 A A t write (A)
read (B) B B t write (B)
Equivalent to T2 followed by T1
read (A) A A 50 write (A) read (B) B
B 50 write (B)
29
Serial Schedules
read (A) t A 0.1 A A t write
(A) read (A) A A 50 write (A)
Equivalent to T1 followed by T2
read (B) B B t write(B) read (B) B
B 50 write (B)
30
Conflict Serializability

• Let instructions I and J belonging to
  transactions T1 and T2
• be executed consecutively by the DBMS.
• I and J can be swapped in their execution order
  if I and J refer to different data elements
• I and J can be swapped in their execution order
  iff I and J refer to the same data element and
  both perform a read operation only.
• I and J are said to conflict if I and J belong to
  different transactions and at least one of them
  is a write operation.

31
Conflict Serializability

• If a schedule S can be transformed to another S
  by swapping non conflicting instructions, then S
  and S are said to be conflict equivalent.
• A schedule S is said to be conflict serializable
  if it is conflict equivalent to some serial
  schedule.

32
View Serializability

• If S is a schedule, then S is a view equivalent
  schedule if
• For each data item Q, if a transaction Ti reads
  the initial value of Q in S, then it should read
  the initial value in S also
• In schedule S, for each data item Q, if write(Q)
  of Tj precedes read(Q) of Ti, it should be the
  same in S
• The same transaction that performs the final
  write(Q) in S, should perform it in S.

33
View Serializability
T1 Read(Q) Write (Q)
T2 Write (Q)
T3 Write(Q)
A view equivalent schedule T1 Read(Q) T2
Write(Q) T1 Write(Q) T3 Write(Q)
34
View Serializability

• Every conflict serializable schedule is also view
  serializable however some view serializable
  schedules are not conflict serializable
• Example in previous slide
• A schedule that is view serializable but not
  conflict serializable is characterized by blind
  writes.

35
Recovery

• So far study of schedules that are acceptable
  from the viewpoint of consistency of database-
  assuming no transaction failures.
• If transaction T fails- undo the effect of the
  transaction to ensure atomicity.
• Also, it is necessary to abort any other
  transaction T1 that is dependent on T.
• To achieve the above two, restrictions on the
  type of schedules permitted has to be laid down.

36
Recoverable Schedules

• Consider the following schedule
• T8 T9
• read(A)
• write(A)
• read(A)
• read(B)

37
Recoverable Schedules

• Suppose T9 commits before T8
• If T8 fails before it commits then T9 also has to
  be aborted.
• However, T9 is already committed.
• A situation where it is impossible to recover
  from the failure of T8
• This is an example of non recoverable schedule
• Database systems require recoverable schedules.

38
Cascading Rollback

• Even if a schedule is recoverable, to recover
  from the failure of a transaction, there is a
  need to rollback several transactions.
• T T1 T2
• read(A)
• read(B)
• write(A)
• read(A)
• write(A)
• read(A)if T fails, then it will lead to
  rolling back T1 and T2.
• This is an example of cascading rollback.

39
Cascadeless Schedule

• Cascading rollback is undesirable- leads to
  undoing a lot of work
• Restrict the schedules to those where cascading
  rollbacks do not occur.
• Such schedules are cascadeless schedule.

40
Implementation of Isolation

• Concurrency-control schemes to ensure that even
  when multiple transactions are executed
  concurrently,only acceptable schedules are
  generated.
• An example, a transaction acquires a lock on an
  entire database before it starts and then
  releases the lock after it finishes.
• No other transaction is allowed to access the
  database.
• Therefore, only serializable schedules are
  produced.

41
Implementation of isolation

• The above discussed scheme leads to poor
  performance.
• Goal of such schemes is to provide a high degree
  of concurrency.

42
Transaction definition in SQL

• SQL provides TCL(Transaction control language)
• Commit- commits the current transaction and
  begins a new one
• Rollback- causes the current transactions to
  abort

43
Testing for serializability

• When designing concurrency control schemes, we
  must show that schedules generated by the scheme
  are serializable.
• A directed graph called precedence graph is used
  for this purpose
• T1 -------? T2
• This indicates that all transactions of T1 are
  executed before T2
• T2 ------? T1
• This indicates that all transactions of T2 are
  executed before T1

44
Testing for Serializability

• If the precedence graph for a schedule s has a
  cycle, then s is not conflict serializable.
• If the precedence graph for a schedule s does not
  have a cycle, then s is conflict serializable.
• Serializability order of the transaction obtained
  through topological sorting which determines a
  linear order consistent with the partial order of
  the precedence graph