UNIT - IV

5 Database Transaction
Management
Syllabus
Introduction to Database Transaction, Transaction states, ACID properties, Concept of Schedule,
Serial Schedule. Serializability : Conflict and View, Cascaded Aborts, Recoverable and Non
recoverable Schedules. Concurrency Control : Lock-based, Time-stamp based Deadlock handling.
Recovery methods : Shadow-Paging and Log-Based Recovery, Checkpoints. Log-Based Recovery :
Deferred Database Modifications and Immediate Database Modifications.

Contents
Part I : Transaction Management
5.1 Introduction to Database Transaction
5.2 Transaction states Dec 17, .Marks 8
5.3 ACID Properties .Dec.-18,19, May-18,19, ..... Marks 8
5.4 Concept of Schedule
5.5 Serializability : Conflict and View... .Dec.-17,18,19, May-18, ..Marks 9
5.6 Recoverable and Non-Recoverable Schedules

Part II : Concurrency Control

5.7 Concurrency Control
5.8 Need for Concurrency
5.9 Lock based Protocol Dec.-17,18,19, May-18, 19, .. Marks 9
5.10 Time-stamp based Protocol. .May-19, Dec.-17, .Marks 9
5.11 Deadlocks Handling
5.12 Recovery Concepts
5.13 Recovery Methods
5.14 Shadow-paging
5.15 Check Points
5.16 Log-based Recovery : Deferred and Immediate Update
.May-18,19, .Marks 8
Multiple Choice Questions

(5-1)
Database Management Systems 5-2 Database Transaction Management

Part I : Transaction Management

5.1 Introduction to Database Transaction

Definition of Transaction : A transaction can be defined as a group of tasks that
form a single logical unit. For example - Suppose we want to withdraw 100 from
an account then we will follow following operations :
1) Check account balance
2) If sufficient balance is present request for withdrawal.
3) Get the money
4) Calculate Balarnce = Balance - 100
5) Update account with new balance.
The above mentioned five steps denote one transaction.
" A database is a collection of named data items.
Granularity or size of data items : The size of data items can be a field, a record or a
whole disk block.
Basic operations on data item A are -
1. read_item(X) 2. write_item(X)
1. read_item(X): This is a reading operation in which database item named Xis read
into a programming variable. We can name the program variable as X for
simplification.
2. write_item(X): This operation writes the value of program variable X into the
database item which is also named as X.
Basic unit of data transfer from the disk to the computer main memory is one block.
read_item(X) command includes the following steps:
Step 1: Find the address of the disk block that contains item X.
Step 2: Copy that disk block into a buffer in main memory (if that disk block is not
already in some main memory buffer).
Step 3: Copy item X from the buffer to the program variable named X.
write_item(X) command includes the following steps:
Step 1: Find the address of the disk block that contains item X.
Step 2 : Copy that disk block into a buffer in main memory (if that disk block is not
already in some main memory buffer).
Step 3:Copy item Xfrom the program variable named Xinto its correct location in
the buffer.

Step 4:Store the updated block from the buffer back to disk.
TECHNICAL PUBLICATIONS. an up- thrust for knowledge
 Database Transaction Management
Database Management Systems 5-3

write operations
Example of sample transaction performing read and
read item(X);
X=X+M;
Writeitem(X);
" Transaction Notations :
write operations. For example -
The transaction notations focuses on read and
Following are two transactions denoted by Tland T2
T1:bl;r1(X);w1(X);r1(Y); W1(Y);e1;
T2:b2;r2(X);r2(Y);e2
bl and b2 represents the
The r and w represents the read and write operations. The
beginning and el and e2 represents ending of transaction.
SPPU : Dec..-17. Marks 8
5.2 Transaction States
Each transaction has following five states:

Partially Committed
committed,

Active End

Failed Aborted

Fig. 5.2.1 Transaction states

1) Active : This is the first state of transaction. For example : Insertion, deletion or
updation of record is done here. But data is not saved to database.
2) Partially committed : When atransaction executes its final operation, it is said to be
in a partially committed state.
3) Failed :Atransaction is said to be in a failed state if any of the checks made by the
database recovery system fails. A failed transaction can no longer proceed further.
4) Aborted : If a transaction is failed to execute, then the database recovery system will
make sure that the database is in its previous consistent state. If not, it brings the
database to consistent state by aborting or rolling back the transaction.
5) Committed : If a transaction executes all its operations successfully, it is said to be
committed. This is the last step of a transaction, if it executes without fail.

TECHNICAL PUBLICATIONS an up- thrust for knowledge

Database Management Systems 5-5 Database Transaction Management
5.3 ACID Properties SPPU : Dec.-18,19.May-18,19. Marks 8

1) Atomicity :
" This property states that each transaction must be considered as a single unit and
must be completed fully or not completed at all.
" No transaction in the database is left half completed.
" Database should be in a state either before the transaction execution or after the
transaction execution. It should not be in a state 'executing.
" For example - In above mentioned withdrawal of money transaction all the five
steps must be completed fully or none of the step is completed. Suppose if
transaction gets failed after step 3, then the customer will get the money but the
balance willnot be updated accordingly. The state of database should be either at
before ATM withdrawal (i.e customer without withdrawn money) or after ATM
withdrawal (i.e. customer with money and account updated). This will make the
system in consistent state.

2) Consistency :
" The database must remain in consistent state after performing any transaction.
" For example : In ATM withdrawal operation, the balance must be updated
appropriately after performing transaction. Thus the database can be in consistent
state.

3) Isolation :
" In a database system where more than one transaction are being executed
simultaneously and in parallel, the property of isolation states that all the
transactions will be carried out and executed as if it is the only transaction in the
system.
" No transaction will affect the existence of any other transaction.
" For example : If a bank manager is checking the account balance of particular
customer, then manager should see the balance either before withdrawing the
money or after withdrawing the money. This will make sure that each individual
transaction is completed and any other dependent transaction will get the consistent
data out of it. Any failure to any transaction will not affect other transaction in this
case. Hence it makes all the transactions consistent.

TECHNICAL PUBLICATIONSan up- thrust for knowledge

Database Management Systems 5-6 Database Transaction Management

4) Durability :
" The database should be strong enough to handle any system failure.
" If there is any set of insert /update, then it should be able to handle and commit to
the database.
" If there is any failure, the database should be able to recover it to the consistent
state.

" For example : In ATM withdrawal example, if the system failure happens after
customer getting the money then the system should be strong enough to update
Database with his new balance, after system recovers. For that purpose the system
has to keep the log of each transaction and its failure. So when the system
recovers, it should be able to know when a system has failed and if there is any
pending transaction, then it should be updated to Database.
Review Question

1 State and explain in brief the ACID properties. During execution of transaction, a transaction
passes through several states, until it finally commits or aborts. List all possible sequences of
states through which a transaction may pass. Explain why each state transition occurs.
SPPU: May-18,19, Dec.-18,19, End Sem, Marks 8
5.5 Serializability : Conflict and View SPPU: Dec.-17.18,19. May-18, Marks 9

" When multiple transactions run concurrently, then it may lead to inconsistency of
data (i.e. change in the resultant value of data from different transactions).

TECHNICAL PUBLICATIONS. an up- thrust for knowledge

Database Management Systems 5-8 Database Trensaction Management

Serializability is a concept that helps to identify which non serial schedule and find
the transaction equivalent to serial schedule.,
T1 A B T2

Initial Value 100 100

A=A - 10

W(A)
B=B+10

W(B)
90 110

A=A-10

W(A)
110

In above transactions initially T1 will read the values from database as A = 100,
B= 100 and modify the values of A and B. But transaction T2 will read the modified
value i.e. 90 and will modify it to 80 and perform write operation. Thus at the end of
transaction T1 value of A will be 90 but at end of transaction T2 value of A will be
80. Thus conflicts or inconsistency occurs here. This sequence can be converted to a
sequence which may give us consistent result. This process is called serializability.
Difference between serial schedule and serializable schedule
Sr. Serial schedule Serializable schedule
No.

1 No concurrency is allowed in serial schedule. Concurrency is allowed in serializable

schedule.

2 In serial schedule, if there are two transactions In serializable schedule, if there are two
executing at the same time and no interleaving transactions executing at the same time and
of operations is permitted, then following can interleaving of operations is allowed there
be the possibilities of execution - can be different possible orders of executing
i) Execute all the operations of transactions T, an individual operation of the transactions.
in a sequence and then execute all the
operations of transactions T, in a sequence.
ii) Execute all the operations of transactions T,
in a sequence and then execute all the
operations of transactions T, in a sequence.

TECHNICAL PUBLICATIONS. an up- thrust for knowiedge

Database Management Systems 5-9 Database Transaction Management

Example of serial schedule Example of serializable schedule

T T, T T,

Read(A) Read(A)
A=A-50 A=A-50

Write(A) Write(A)
Read(B) Read(B)
B=B+100 B=B+100

Write(B) Write(B)
Read(A) Read(B)
A=A+10 Write(B)
Write(A)
 There are two types of serializabilities : Conflict serializability and view
serializability.
5.5.1 Conflict Serializability
Definition : Suppose T, and T, are two transactions and I, and I, are the instructions in
T, and T, respectively. Then these two transactions are said to be conflict serializable, if
both the instruction access the data item d, and at least one of the instruction is write
operation.
What is conflict ? : In the definition three conditions are specified for a conflict in
conflict serializability -
1) There should be different transactions
2) The operations must be performed on same data items
3) One of the operation must be the Write (W) operation.
" We can test a given schedule for conflict serializability by constructing a precedence
graph for the schedule, and by searching for absence of cycles in the graph.
Predence graph is a directed graph, consisting of G = (V,E) where V is set of vertices
and E is set of edges. The set of vertices consists of all the transactions participating
in the schedule. The set of edges consists of all edges T, ’I, for which one of three
conditions holds :
1 I, executes write() before T, executes read(Q).

TECHNICAL PUBLICA TIONS-an up- thust for knowledge

Database Management Systems 5- 10 Database Transaction Management

2 T, executes read(Q) before T, executes write(Q).
3 T, executes write(0) before T, executes write(O).

" A serializability order of the transactions can be obtained by finding a linear order
consistent with the partial order of the precedence graph. This process is called
topological sorting.
Testing for serializability
Following method is used for testing the serializability: To test the conflict
serializability we can draw a graph G = (V, E) where V = Vertices which represent
the number of transactions.

E- Edges for conflicting pairs.

Step 1: Create a node for each transaction.

Step 2: Find the conflicting pairs (RW, WR, WW) on the same variable (or data item) by
different transactions.

Step 3: Draw edge for the given schedule. Consider following cases
1. T, executes write(Q) before T, executes read(@), then draw edge from T, to T,.
2. TË executes read(Q) before T;executes write(Q), then draw edge from T, to T,
3. T; executes write(Q) before T; executes write(Q). . then draw edge from T, to T,.

Step 4: Now, if precedence graph is cyclic then it is a non confl 304/428

and if the precedence graph is acyclic then it is conflict serializable.
Example 5.5.1 Consider the follorwing two transactions and schedule (time goes from top to
bottom). Is this schedule conflict-serializable ? Explain why or why not.
T. T,

R(A)

W(A)
R(A)

R(B)

R(B)
W(B)
5.5.2 View Serializability
" If a given schedule is found to be view equivalent to some serial schedule, then it is
called as a view serializable schedule.
View Equivalent Schedule : Consider two schedules S1 and $2 consisting of
transactions T1 and T2 respectively, then schedules S1 and S2 are said to be view
equivalernt schedule if it satisfies following three conditions :
o If transaction Tl reads a data item A from the database initially in schedule S2,
then in schedule S2 also, T1 must perform the initial read of the data item X from
the database. This is same for all the data items. In other words - the initial reads
must be same for all data items.
o If data item A has been updated at last by transaction Ti in schedule S1, then in
schedule S2 also, the data item A must be updated at last by transaction Ti.
o If transaction Ti reads a data item that has been updated by the transaction TË in
schedule S1, then in schedule S2 also, transaction Ti must read the same data item
that has been updated by transaction TË. In other words the Write-Read sequence
must be same.

o Difference between conflict serializability and view serializability

Conflict serializability View serializability
Every conflict serializable is view serializable. Every view serializable schedule is not
necessarily conflict serializable.

It is easy to test conflict serializability. It is complex to test view serializability.

Steps to check whether the given schedule is view serializable or not
Step 1 : If the schedule is conflict serializable then it is surely view serializable because
conflict serializability is a restricted form of view serializability.

TECHNICAL PUBLICATIONS- an up- thrust for knowledge

Database Management Systems 5-21 Database Transaction Management

Step 2: If it is not conflict serializable schedule then check whether there exist any blind
write operation. The blind write operation is a write operation without reading a value. If
there does not exist any blind write then that means the given schedule is not view
serializable. In other words if a blind write exists then that means schedule may or may
not be view conflict.
Step 3: Find the view equivalence schedule
Example 5.5.7 Consider the following schedules for checking if these are view serializable or
n0t.

W(C)

R(A)
W(B) R(B)

R(C)
W(B)

W(B)
Solution:

i) The initial read operation is performed by T, on data item A o 315/428

Hence we will begin with T, or I. We will choose T, at the begitu
i)The final write is performed by T, on the same data item B. Hence T, will be at the
last position.
iii) The data item Cis written by T, and then it is read by T,. Hence T, should appear
before T,. Thus we get the order of schedule of view serializability as T,-I, -I,
5.9 Lock based Protocol SPPU: Dec.-17,18,19, May-18,19, Marks 9

5.9.1 Why Do We Need Lock ?

One of the method to ensure the isolation property in transactions is to require that
data items be accessed in amutually exclusive manner. That means, while one
transaction is accessing a data item, no other transaction can modify that data item.
" The most common method used to implement this requirement is to allow a
transaction to access a data item only if it is currently holding a lock on that item.
Thus the lock on the operation is required to ensure the isolation of transaction.

5.9.2 Working of Lock

Concept of protocol : The lock based protocol is a mechanism in which there is
exclusive use of locks on the data item for current transaction.

TECHNICAL PUBLICATIONS an up-thrust for knowiedge

Database Management Systems 5-34 Database Transaction Management

Types of locks : There are two types of locks used

Lock

Shared lock

Exclusive lock

Fig. 5.9.1 Types of locks

i) Shared lock: The shared lock is used for reading data items only. It is denoted by
Lock-S. This is also called as read lock.
ii) Exclusive lock : The exclusive lock is used for both read and write operations. It is
denoted as Lock-X. This is also called as write lock.
The compatibility matrix is used while working on set of locks. The concurrency
control manager checks the compatibility matrix before granting the lock. If the two
modes of transactions are compatible to each other then only the lock will be
granted.
" In a set of locks may consists of shared or exclusive locks. Following matrix
represents the compatibility between modes of locks.
S X

S T F

X F F

Fig. 5.9.2 Compatibility matrix for locks

Here T stands for True and F stands for False. If the control manager get the
compatibility mode as True then it grant the lock otherwise the lock will be denied.
For example : If the transaction T, is holding a shared lock in data item A, then the
control manager can grant the shared lock to transaction T, as compatibility is True.
But it cannot grant the exclusive lock as the compatibility is false. In simple words if
transaction T, is reading a data item A then same data item A can be read by
another transaction T, but cannot be written by another transaction.
Similarly if an exclusive lock (i.e. lock for read and write operations) is hold on the
data item in some transaction then no other transaction can acquire shared or
exclusive lock as the compatibility function denotes F. That means of some
transaction is writing a data item A then another transaction can not read or write
that data item A.
Database Management Systems 5-35 Database Transaction Management

" Hence the rule of thumnb is

i) Any number of transactions can hold shared lock on an item.
ii) But exclusive lock can be hold by only one transaction.
Example of a schedule denoting shared and exclusive locks : Consider following
schedule in which initially A=100. We deduct 50 from A in T1 transaction and Read
the data item A in transaction T2. The scenario can be represented with the help of
locks and concurrency control manager as follows :
T, Concurrency control manager
Lock-X(A)

Grant X(A,T,) because in T

there is write operation.

Exclusive lock R(A)

A=A- 50

W(A)
Unlock(A)
Lock-S(A)

Grant S(A,T,) because in T, there is

Shared lock read operation

R(A)
Unlock(A)

5.9.3 Two Phase Locking Protocol

" The two phase locking is a protocol in which there are two phases:
i) Growing phase (Locking phase) : It is a phase in which the transaction may
obtain locks but does not release any lock.
ii) Shrinking phase (Unlocking phase) : It is a phase in which the transaction may
release the locks but does not obtain any new lock.
" Lock Point : The last lock position or first unlock position is called lock point.

TECHNICAL PUBLICA TIONS-an up-thrust for knowiedge

Database Management Systems 5-36 Database Transaction Management

For example -
Lock(A) Lock point
Lock(B) Locks
Lock(C) get Locks are
acquired released
(Growing (Shrinking
Lock phase) phase)
Point!

Unlock(A) Transaction Transaction

begins ends
Unlock(B)
Unlock(C)
Consider following transactions
T, T,
Lock-X(A) Lock-S(B)

Read(A) Read(B)

A=A-50 Unlock-S(B)
Write(A)

Lock-X(B)

Unlock-X(A)
B=B+100 Lock-S(A)

Write(B) Read(A)
Unlock-X(B) Unlock-S(A)

The important rule for being a two phase locking is -All lock operations precede all
the unlock operations.
In above transactions T, is in two phase locking mode but transaction T, is not in two
phase locking. Because in T,, the shared lock is acquired by data item B, then data item B
is read and then the lock is released. Again the lock is acquired by data item A, then the
data item A is read and the lock is then released. Thus we get lock-unlock-lock-unlock
sequence. Clearly this is not possible in two phase locking.

TECHNICAL PUBLICATIONSan up-thrust for knowledge

 Tlase LoCKIng
1) Strict two phase locking : The strict 2PL protocol is a
all the exclusive mode locks be held
basic two phase protocol but
until the transaction commits. That means in
other words all the exclusive locks are unlocked only after the
transaction
committed. That also means that if T, has exclusive lock, then T, will release theis
exclusive lock only after commit operation, then only other transaction is allowed to
read or write. For example -Consider two
transactions

T,
W(A)

R(A)

TECHNICAL PUBLICATIONs- an up-thrust for knowledge

Database Management Systems 5- 40 Database Transaction Management

If we apply the locks then
T,
Lock-X(A)
W(A)
Commit

Unlock(A)
Lock-S(A)
R(A)
Unlock-S(A)
Thus only after commit operation in T, we can unlock the exclusive lock. This ensures
the strict serializability.
Thus compared to basic two phase locking protocol, the advantage of strict 2PL
protocol is it ensures strict serializability.
2) Rigorous two phase locking : This is stricter two phase locking protocol. Here all
locks are to be held until the transaction commits. The transactions can be
seriealized in the order in which they commit.
Example - Consider transactions

R(A)
R(B)
W(B)

If we apply the locks then

T,
Lock-S(A)
R(A)
334/428
Lock-X(B)
R(B)
W(B)
Commit

Unlock(A)
Unlock(B)
Thus the above transaction uses rigorous two phase locking mechanism.
5.10 Time-stamp based Protocol SPPU: May-19, Dec.-17, Marks 9
The time stamp ordering protocol is ascheme in which the order of transaction is
decided in advance based on their timestamps. Thus the schedules are serialized
according to their timestamps.
The timestamp-ordering protocol ensures that any conflicting read and write
operations are executed in timestamp order.
A larger timestamp indicates a more recent transaction or it is also called as
younger transaction while lesser timestamp indicates older transaction.
Assume a collection of data items that are accessed, with read and write operations,
by transactions.
" For each data item X the DBMS maintains the following values:
o RTS(X): The timestamp on which object X was last read (by some transaction
T,, ie., RTS(X):=TS(T) (Note that : RTS stands for Read Time Stamp]
o WTS(X) : The timestamp on which object X was last written (by some
transaction I,, ie, WTS(X):=TS(T)) (Note that : WTS stands for Write Time
Stamp]
For the following algorithms we use the following assumptions : A data item X in
the database has a RTS() and WTS(X). These are actually the timestamps of read
and write operations performed on data item Xat latest time.
A transaction T attempts to perform some action (read or write) on data item Xon
some timestamp and we call that timestamp as TS(T).
By timestamp ordering algorithm we need to decide whether transaction T has to be
aborted or T can continue execution.

TECHNICAL PUBLICATIONS- an up-thrust for knowledge

Database Management Systems 5- 43 Database Transaction Management

Basic Timestamp Ordering Algorithm

Case 1 (Read) : Transaction T issues a read(X) operation
i) If TS(T) < WTSX), then read(X) is rejected. T has to abort and be rejected.
ii) If WTS(X) < TS(T), then execute read(X) of T and update RTS(X).
Case 2 (Write): Transaction T issues a write(X) operation
i) If TS(T)<RTS(X) or if TS(T)<WTS(X), then write is rejected
ii) If RTS(X) < TS(T) or WTS(X) s TS(T), then execute write(X) of T and update WTS(X).
Example for Case 1 (Read operation)
(i) Suppose we have two transactions T, and T, with timestamps 10 sec. and 20 sec.
respectively.
10 Sec 20 Sec
T. T,
R(X)
W(X)

R(X)

RTS(0) and WIS(X) is initially = 0

Then RTS(X) = 10, when transaction T, executes
After that WTS(X) =20 when transaction T, executes
Now if read operation R(X) occurs on transaction T, at TS(T) =10 then
TS(T,) i.e. 10 <WTS(X) ie. 20, hence we have to reject second read o337/428 ie.
10 Sec 20 Sec
T, T
R(X)
W(X)

W(X)>
This read operation
gets rejected as it
occurs atthan
timestamp olderthe
write operation

Fig. 5.10.1
Database Management Systems 5-44 Database Transaction Management

ii)Suppose we have two transactions T, and T, with timestamps 10 sec. and 20 sec.
respectively.
10 Sec 20 Sec

T, T,
W()

ROX)

RTS(X) and WTS(X) is initially =0

Then WTS(X) =10 as transaction T, executes.
Now if Read operation R(X) occurs on transaction T, at TST,) = 20 then
TS(T) i.e. 20 >WTS(X) which is 10, hence we accept read operation on T,. The
transaction T, will perform read operation and now RTS will be updated as,
RTS(X) = 20

Example for Case 2 (Write Operation)

i) Suppose we have two transactions T, and T, with timestamps 10 sec. and 20 sec.
respectively.
10 Sec. 20 Sec.

T, T,
R(X)

W(X)
W(X)

RTS(X) and WTS(X) is initially = 0

Then RTS(X) = 10, when transaction T, executes
After that WTS(X) = 20 when transaction T, executes
Now if write operation W(X) occurs on transaction T, at TS(T) = 10 then
TS(T,) i.e. 10 <WTS(X), hence we have to reject second write operation on T, i.e.

TECHNICAL PUBLICATIONSan up-thrust for knowledge

Database Management Systems 5-45 Database Transaction Management

10 Sec 20 Sec
T, Tz
R(X)

W(X)

This write operation

gets rejected as it
occurs at older
timestamp than the
write operation
at transaction 2 339/428
Fig. 5.10.2

ii) Suppose we have two transactions T, and T, with timestamps 10 sec. and 20 sec.
respectively.
10 Sec 20 Sec

T, T,
W(X)

W(X)
 RTS() and WTS(X) is initially -0
Then WTS(X) = 10 as transaction T, executes.
Now if write operation W(X) occurs on transaction T, at TS(T,) = 20 then
TS(T,) i.e. 20 >WTS(X) which is 10, hence we accept write operation on T,. The
transaction T, will perform write operation and now WTS will be updated as,
WIS(X) = 20
Advantages and disadvantages of time stamp ordering

Advantages
1) Schedules are serializable
2) No waiting for transaction and hence there is no deadlock situation.
Disadvantages
1) Schedules are not recoverable once transactions occur.
2) Same transaction may be continuously aborted or restarted.
Database Management Systems 5- 57 Database Transaction Management

5.16 Log-based Recovery :Deferred and Immediate Update

SPPU: May-18,19, Marks 8
Before discussing the recovery algorithms(deferred and immediate update), let us see
the concept of Log and REDO and UND0 operations.
5.16.1 Concept of Log
" Log is the most commonly used structure for recording the modifications that is to
be made in the actual database. Hence during the recovery procedure a log file is
maintained.
A log record maintains four types of operations. Depending upon the type of
operations there are four types of log records -
1. <Startb Log record:It is represented as <T, Start>
2. <Update> Log record
3. <Commib Log record : It is represented as <T, Commit>
4. <Abortb Log record: It is represented as <T, Abort>
" The log contains various fields as shown in following Fig. 5.16.1. This structure is
for <update> operation
Transaction Data Item Old Value of New Value
ID(T) Name Data Item of Data Item

Fig. 5.16.1

" For example: The sample log file is

<T, Start> Here 10 represents the old value before commit

<T,a,10,20> operation and 20 is the new value that needs to be
<T,, Commit> updated in the database after commit operation.

The log must be maintained on the stable storage and the entries in the log file are
maintained before actually updating the physical database.
There are two approaches used for log based recovery technique - Deferred
Database Modification and Immediate Database Modification.

TECHNICAL PUBLICATIONS- an up-thrust for knowledge