2 of 13

Introduction to Transaction

Transaction: is defined as group of tasks . A logical unit of database processing An executing program (process) that includes one or more database access operations

Read operations (database retrieval, such as SQL SELECT)
Write operations (modify database, such as SQL INSERT, UPDATE, DELETE)

Example: Bank balance transfer of $100 dollars from a checking account to a saving account in a BANK database

Single task : It is the minimum processing unit which can’t be divided further more . At most one user can use the system.
Note: Each execution of a program is a distinct transaction with different parameters

Bank transfer program parameters: savings account number, checking account number, transfer amount

3 of 13

Introduction to Transaction Cont:-

Transaction boundaries: Begin and End transaction.

Note: An application program may contain several transactions separated by Begin and End transaction boundaries

Transaction Processing Systems: Large multi-user database systems supporting thousands of concurrent transactions (user processes) per minute
Two Modes of Concurrency

Interleaved processing: concurrent execution of processes is interleaved in a single CPU
Parallel processing: processes are concurrently executed in multiple CPUs .
Basic transaction processing theory assumes interleaved concurrency

4 of 13

Introduction to Transaction Cont:-

For transaction processing purposes, a simple database model is used:

A database - collection of named data items

Granularity (size) of a data item - a field (data item value), a record, or a whole disk block

TP concepts are independent of granularity

Basic operations on an item X:

read_item(X): Reads a database item named X into a program variable. To simplify our notation, we assume that the program variable is also named X.
write_item(X): Writes the value of program variable X into the database item named X.

5 of 13

Transaction Notation

Notation focuses on the read and write operations
Can also write in shorthand notation (Figure (below) shows two examples of transactions)

T1: b1; r1(X); w1(X); r1(Y); w1(Y); e1;
T2: b2; r2(Y); w2(Y); e2;

bi and ei specify transaction boundaries (begin and end)
i specifies a unique transaction identifier (TId)

6 of 13

Transaction Example

A transaction is understood as an execution of a user program
We can have an access to database by using two operations:

a) read(x): Performs the reading operation of data item x from the database.

b) write(x): Performs the writing operation of data item x to the database.

For example, Let T1 be a transaction that transfers Rs 50 from account X to account Y. This can be explained as:

T1: read(X); Y:=Y+50;

X:= X-50; write(Y);

write(X);

read(Y);

7 of 13

Transaction Concept issues

Two main issues to deal with:

Failures of various kinds, such as hardware failures and system crashes
Concurrent execution of multiple transactions

8 of 13

Required Properties of a Transaction

Consider a transaction to transfer $50 from account A to account B:

1.read(A)

2.A := A – 50

3.write(A)

4.read(B)

5.B := B + 50

6.write(B)

Atomicity requirement

If the transaction fails after step 3 and before step 6, money will be “lost” leading to an inconsistent database state

Failure could be due to software or hardware

The system should ensure that updates of a partially executed transaction are not reflected in the database

Durability requirement — once the user has been notified that the transaction has completed (i.e., the transfer of the $50 has taken place), the updates to the database by the transaction must persist even if there are software or hardware failures.

9 of 13

Required Properties of a Transaction (Cont.)

Consistency requirement in above example:

The sum of A and B is unchanged by the execution of the transaction

In general, consistency requirements include

Explicitly specified integrity constraints such as primary keys and foreign keys
Implicit integrity constraints

e.g., sum of balances of all accounts, minus sum of loan amounts must equal value of cash-in-hand

A transaction, when starting to execute, must see a consistent database.
During transaction execution the database may be temporarily inconsistent.
When the transaction completes successfully the database must be consistent

Erroneous transaction logic can lead to inconsistency

10 of 13

Required Properties of a Transaction (Cont.)

Isolation requirement — if between steps 3 and 6 (of the fund transfer transaction) , another transaction T2 is allowed to access the partially updated database, it will see an inconsistent database (the sum A + B will be less than it should be).� T1 T2

1. read(A)

2. A := A – 50

3. write(A)� read(A), read(B), print(A+B)

4. read(B)

5. B := B + 50

6. write(B

Isolation can be ensured trivially by running transactions serially

That is, one after the other.

However, executing multiple transactions concurrently has significant benefits, as we will see later.

11 of 13

ACID Properties

Atomicity. Either all operations of the transaction are properly reflected in the database or none are.
Consistency. Execution of a transaction in isolation preserves the consistency of the database.
Isolation. Although multiple transactions may execute concurrently, each transaction must be unaware of other concurrently executing transactions. Intermediate transaction results must be hidden from other concurrently executed transactions.

That is, for every pair of transactions T_i and T_j, it appears to T_i that either T_j, finished execution before T_i started, or T_j started execution after T_i finished.

Durability. After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.

12 of 13

Transaction States

13 of 13

Transaction States

A transaction is an atomic unit of work that is either completed in its entirety or not done at all. A transaction passes through several states.

Transaction states:

Active – the initial state; the transaction stays in this state while it is executing
Partially committed – after the final statement has been executed.
Failed -- after the discovery that normal execution can no longer proceed.
Aborted – after the transaction has been rolled back and the database restored to its state prior to the start of the transaction. Two options after it has been aborted:

Restart the transaction

can be done only if no internal logical error

Kill the transaction

Committed – after successful completion.