1 Transaction Management

3 o The transaction is a set of logically related operation. It contains a group of tasks.

4 o A transaction is an action or series of actions. It is performed by a single user to perform
5 operations for accessing the contents of the database.

7 Example: Suppose an employee of bank transfers Rs 800 from X's account to Y's account. This
8 small transaction contains several low-level tasks:
9 o X's Account

10 1. Open_Account(X)
11 2. Old_Balance = X.balance
12 3. New_Balance = Old_Balance - 800
13 4. X.balance = New_Balance
14 5. Close_Account(X)

15 Y's Account

16 1. Open_Account(Y)
17 2. Old_Balance = Y.balance
18 3. New_Balance = Old_Balance + 800
19 4. Y.balance = New_Balance
20 5. Close_Account(Y)

21 Operations of Transaction:
22 Following are the main operations of transaction:

23 Read(X): Read operation is used to read the value of X from the database and stores it in a buffer
24 in main memory.

25 Write(X): Write operation is used to write the value back to the database from the buffer.

26 Let's take an example to debit transaction from an account which consists of following operations:

27 1. 1. R(X);
28 2. 2. X = X - 500;
29 3. 3. W(X);

30 Let's assume the value of X before starting of the transaction is 4000.

 1 o The first operation reads X's value from database and stores it in a buffer.
2 o The second operation will decrease the value of X by 500. So buffer will contain 3500.
3 o The third operation will write the buffer's value to the database. So X's final value will be
4 3500.

5 But it may be possible that because of the failure of hardware, software or power, etc. that trans-
6 action may fail before finished all the operations in the set.

7 For example: If in the above transaction, the debit transaction fails after executing operation 2
8 then X's value will remain 4000 in the database which is not acceptable by the bank.

9 Transaction property
10 The transaction has the four properties. These are used to maintain consistency in a database,
11 before and after the transaction.

12 Property of Transaction
13

14

15

16

17

18

19

20 Atomicity:
21 By this, we mean that either the entire transaction takes place at once or doesn’t happen at all.
22 There is no midway i.e. transactions do not occur partially. Each transaction is considered as
23 one unit and either runs to completion or is not executed at all. It involves the following two
24 operations.
25 —Abort: If a transaction aborts, changes made to the database are not visible.
26 —Commit: If a transaction commits, changes made are visible.
27 Atomicity is also known as the ‘All or nothing rule’.

28 Example: Let's assume that following transaction T consisting of T1 and T2. A consists of Rs 600
29 and B consists of Rs 300. Transfer Rs 100 from account A to account B.

T1 T2
Read(A) Read(B)
A:=A-100 Y:= Y+100
Write(A) Write(B)
 1 After completion of the transaction, A consists of Rs 500 and B consists of Rs 400.

2 If the transaction T fails after the completion of transaction T1 but before completion of transac-
3 tion T2, then the amount will be deducted from A but not added to B. This shows the inconsistent
4 database state. In order to ensure correctness of database state, the transaction must be executed
5 in entirety.

6 Consistency:
7 This means that integrity constraints must be maintained so that the database is consistent be-
8 fore and after the transaction. It refers to the correctness of a database. Referring to the example
9 above,
10 The total amount before and after the transaction must be maintained.
11 Total before T occurs = 500 + 200 = 700.
12 Total after T occurs = 400 + 300 = 700.
13 Therefore, the database is consistent. Inconsistency occurs in case T1 completes but T2 fails. As a
14 result, T is incomplete.

15 Isolation
16 o It shows that the data which is used at the time of execution of a transaction cannot be used
17 by the second transaction until the first one is completed.
18 o In isolation, if the transaction T1 is being executed and using the data item X, then that data
19 item can't be accessed by any other transaction T2 until the transaction T1 ends.
20 o The concurrency control subsystem of the DBMS enforced the isolation property.

21 Durability
22 o The durability property is used to indicate the performance of the database's consistent state.
23 It states that the transaction made the permanent changes.
24 o They cannot be lost by the erroneous operation of a faulty transaction or by the system failure.
25 When a transaction is completed, then the database reaches a state known as the consistent
26 state. That consistent state cannot be lost, even in the event of a system's failure.
27 o The recovery subsystem of the DBMS has the responsibility of Durability property.
28

29 States of Transaction
30 In a database, the transaction can be in one of the following states –
 1 I

2 Active state:- The active state is the first state of every transaction. In this state, the trans-
3 action is being executed.
4 o For example: Insertion or deletion or updating a record is done here. But all the records are
5 still not saved to the database.
6 Partially committed
7 o In the partially committed state, a transaction executes its final operation, but the data is still not saved to the data-
8 base.
9 o In the total mark calculation example, a final display of the total marks step is executed in this state.

10 Committed:- A transaction is said to be in a committed state if it executes all its operations successfully.
11 In this state, all the effects are now permanently saved on the database system.

12 Failed state
13 o If any of the checks made by the database recovery system fails, then the transaction is said to be in the failed state.
14 o In the example of total mark calculation, if the database is not able to fire a query to fetch the marks, then the trans-
15 action will fail to execute.

16 Aborted
17 o If any of the checks fail and the transaction has reached a failed state then the database recovery system will make
18 sure that the database is in its previous consistent state. If not then it will abort or roll back the transaction to bring
19 the database into a consistent state.
20 o If the transaction fails in the middle of the transaction then before executing the transaction, all the executed trans-
21 actions are rolled back to its consistent state.
22 o After aborting the transaction, the database recovery module will select one of the two operations:
23 1. Re-start the transaction
24 2. Kill the transaction
 1 Types of Schedules in DBMS:- A sequential execution of a transaction with respect
2 to time is called schedule ,Which can have many transaction in it each compromising several task
3 in it.

4 Serial Schedules:
5 Schedules in which the transactions are executed non-interleaved, i.e., a serial schedule is one in
6 which no transaction starts until a running transaction has ended are called serial schedules.
Example: Consider the following schedule involving two transactions T1 and T2.

T1 T2
R(A)

W(A)

R(B)

W(B)
R(A)

R(B)

7 where R(A) denotes that a read operation is performed on some data item ‘A’
8 This is a serial schedule since the transactions perform serially in the order T1 —> T2 .
9
10 Non-SerialSchedule:
11 If interleaving of operations is allowed, then there will be non-serial schedule.

12 o It contains many possible orders in which the system can execute the individual operations
13 of the transactions.

14

15
 1 . Serializable schedule
2 The serializability of schedules is used to find non-serial schedules that allow the transaction to execute concur-
3 rently without interfering with one another.
4 o It identifies which schedules are correct when executions of the transaction have interleaving of their op-
5 erations.
6 o A non-serial schedule will be serializable if its result is equal to the result of its transactions executed seri-
7 ally.

8 Here,
9 Schedule A and Schedule B are serial schedule.
10 Schedule C and Schedule D are Non-serial schedule.
11 Conflict Serializable:
12 A schedule is called conflict serializable if it can be transformed into a serial schedule by swap-
13 ping non-conflicting operations. Two operations are said to be conflicting if all conditions satisfy:
14 They belong to different transactions .
15 They operate on the same data item .
16 At Least one of them is a write operation .
 1 View Serializable:
2 A Schedule is called view serializable if it is view equal to a serial schedule (no overlapping trans-
3 actions). A conflict schedule is a view serializable but if the serializability contains blind writes,
4 then the view serializable does not conflict serializable.
5

6 Recoverable Schedule:
7 Schedules in which transactions commit only after all transactions whose changes they read com-
8 mit are called recoverable schedules. In other words, if some transaction Tj is reading value up-
9 dated or written by some other transaction Ti, then the commit of Tj must occur after the com-
10 mit of Ti.
11 Example – Consider the following schedule involving two transactions T1 and T2.
12
T1 T2
13
14 R(A)
15 W(A)
16
17 W(A)
18 R(A)
19
20 Commit
21
commit
22
23
24 This is a recoverable schedule since T1 commits before T2, that makes the value read by T2 cor-
25 rect.
26 There can be three types of recoverable schedule:
27
28 A.Cascading Schedule:
29 Also called Avoids cascading aborts/rollbacks (ACA). When there is a failure in one transaction
30 and this leads to the rolling back or aborting other dependent transactions, then such scheduling
31 is referred to as Cascading rollback or cascading abort.
32 Example:
 1 B. Cascadeless Schedule:-
2 Schedules in which transactions read values only after all transactions whose changes they are
3 going to read commit are called cascadeless schedules. Avoids that a single transaction abort
4 leads to a series of transaction rollbacks. A strategy to prevent cascading aborts is to disallow a
5 transaction from reading uncommitted changes from another transaction in the same schedule.
6 In other words, if some transaction Tj wants to read value updated or written by some other
7 transaction Ti, then the commit of Tj must read it after the commit of Ti.
8 Example: Consider the following schedule involving two transactions T1 and T2.
9

10
T1 T2
11
R(A)
12
W(A)
13
W(A)
14
Commit
15
R(A)
16
commit
17

18 This schedule is cascadeless. Since the updated value of A is read by T2 only after the updating
19 transaction i.e. T1 commits.
20 Example: Consider the following schedule involving two transactions T1 and T2.
21
T1 T2
22
R(A)
23
W(A)
24
W(A)
25
R(A)
26
abort
27
abort
28

29 It is a recoverable schedule but it does not avoid cascading aborts. It can be seen that if T1 aborts,
30 T2 will have to be aborted too in order to maintain the correctness of the schedule as T2 has al-
31 ready read the uncommitted value written by T1.
 1 C. Strict Schedule:
2 A schedule is strict if for any two transactions Ti, Tj, if a write operation of Ti precedes a conflict-
3 ing operation of Tj (either read or write), then the commit or abort event of Ti also precedes that
4 conflicting operation of Tj.
5 In other words, Tj can read or write updated or written value of Ti only after Ti commits/aborts.
6 Example: Consider the following schedule involving two transactions T1 and T2.
7
T1 T2
8
R(A)
9
R(A)
10
W(A)
11
Commit
12
W(A)
13
R(A)
14
Commit
15
16 This is a strict schedule since T2 reads and writes A which is written by T1 only after the commit
17 of T1.
18

19 DBMS Concurrency Control

20 Concurrency Control is the management procedure that is required for controlling concurrent ex-
21 ecution of the operations that take place on a database.

22 Concurrently control is a very important concept of DBMS which ensures the simultaneous execu-
23 tion or manipulation of data by several processes or user without resulting in data inconsistency.
24 Concurrency Control deals with interleaved execution of more than one transaction.

25 Problems with Concurrent Execution:-In a database transaction, the two main op-
26 erations are READ and WRITE operations. So, there is a need to manage these two operations in
27 the concurrent execution of the transactions as if these operations are not performed in an in-
28 terleaved manner, and the data may become inconsistent. So, the following problems occur
29 with the Concurrent Execution of the operations:

30 Problem 1: Lost Update Problems (W - W Conflict)

31 The problem occurs when two different database transactions perform the read/write operations
32 on the same database items in an interleaved manner (i.e., concurrent execution) that makes the
33 values of the items incorrect hence making the database inconsistent.
 1 For example:

2 Consider the below diagram where two transactions TX and TY, are performed on the same ac-
3 count A where the balance of account A is $300.

4
5 o At time t1, transaction TX reads the value of account A, i.e., $300 (only read).
6 o At time t2, transaction TX deducts $50 from account A that becomes $250 (only deducted
7 and not updated/write).
8 o Alternately, at time t3, transaction TY reads the value of account A that will be $300 only
9 because TX didn't update the value yet.
10 o At time t4, transaction TY adds $100 to account A that becomes $400 (only added but not
11 updated/write).
12 o At time t6, transaction TX writes the value of account A that will be updated as $250 only,
13 as TY didn't update the value yet.
14 o Similarly, at time t7, transaction TY writes the values of account A, so it will write as done at
15 time t4 that will be $400. It means the value written by TX is lost, i.e., $250 is lost.

16 Hence data becomes incorrect, and database sets to inconsistent.

17 Dirty Read Problems (W-R Conflict)

18 The dirty read problem occurs when one transaction updates an item of the database, and some-
19 how the transaction fails, and before the data gets rollback, the updated database item is accessed
20 by another transaction. There comes the Read-Write Conflict between both transactions.
 1 For example:

2 Consider two transactions TX and TY in the below diagram performing read/write operations on
3 account A where the available balance in account A is $300:

4 A is $300:

5 ADVERTISEMENT

7 o At time t1, transaction TX reads the value of account A, i.e., $300.

8 o At time t2, transaction TX adds $50 to account A that becomes $350.
9 o At time t3, transaction TX writes the updated value in account A, i.e., $350.
10 o Then at time t4, transaction TY reads account A that will be read as $350.
11 o Then at time t5, transaction TX rollbacks due to server problem, and the value changes back
12 to $300 (as initially).
13 o But the value for account A remains $350 for transaction TY as committed, which is the dirty
14 read and therefore known as the Dirty Read Problem.

15 Unrepeatable Read Problem (W-R Conflict)

16 Also known as Inconsistent Retrievals Problem that occurs when in a transaction, two different values are read for
17 the same database item.

18 For example:

19 Consider two transactions, TX and TY, performing the read/write operations on account A, having
20 an available balance = $300. The diagram is shown below:
 1

2 o At time t1, transaction TX reads the value from account A, i.e., $300.
3 o At time t2, transaction TY reads the value from account A, i.e., $300.
4 o At time t3, transaction TY updates the value of account A by adding $100 to the available
5 balance, and then it becomes $400.
6 o At time t4, transaction TY writes the updated value, i.e., $400.
7 o After that, at time t5, transaction TX reads the available value of account A, and that will be
8 read as $400.
9 o It means that within the same transaction TX, it reads two different values of account A, i.e.,
10 $ 300 initially, and after updation made by transaction TY, it reads $400. It is an unrepeatable
11 read and is therefore known as the Unrepeatable read problem.

12 Thus, in order to maintain consistency in the database and avoid such problems that take place in
13 concurrent execution, management is needed, and that is where the concept of Concurrency Con-
14 trol comes into role.

15