Transaction Management in Database Management Systems

Transaction Management in Database Management Systems, Transaction Processing Concepts in DBMS, Transactions in DBMS, Serializability, Concurrency Control, Recovery in Database

Transactions Management in DBMS

• Definition

• ACID Properties - Atomicity, Consistency, Isolation, Durability

• Types of failures that causes a transaction to fail

• Transaction States

• Concurrent Execution of Transactions and its Advantages

• Need for Concurrency Control in DBMS

• Schedules - Defined with examples

• Serial Schedules

• Serializable Schedules

• Serializability

• Conflict Serializability
• Testing for conflict serializability using precedence graph

• View Serializability

• Recoverable Schedules
• Why do we need recoverable schedules?

• Cascadeless Schedules

• Serializability Testing

• Properties of schedule of transactions in DBMS

• Concurrency Control - Definition

• Lost Update Problem (due to Write-Write conflict) - Example

• Uncommitted / Dirty Read Problem (Write-Read conflict) - Example

• Inconsistent Analysis Problem - Example

• Two Phase Locking (2PL) protocol

•  Important facts about serializable transactions and schedules

***************

Go to Advanced DBMS concepts page

Go to Transactions in Distributed DBMS page

Transactions in Database Management Systems - Definition

Transactions in DBMS, Define transaction in database, Explain database transactions with example

Transactions

Transaction is a logical unit of work carried out by a single execution (of a program or an application) that reads or writes (updates/changes) the contents’ (one or more data items) values of the database.

Discussion:

Example 1

Assume that you have a savings account with account number ‘A101’ and you want to check your latest account balance. Here,

Data Item(s): Your account with account number ‘A101’ is the data item.

Operation (Read/Write): You want to access your balance. That means you like to read the balance.

Program/Application: You may use, for example, the ATM machine interface. That is the application/program which does read your balance from database.

Example 2

Assume that you want to transfer money from your account ‘A101’ to your friend’s account ‘A345’. Here,

Data Item(s): Your account with account number ‘A101’ is one data item. Your friend’s account ‘A345’ is another data item.

Operation (Read/Write): You want to transfer fund from your account to others. This action reduces your balance and increases your friend’s. This means you have to write new balances in both data items.

Program/Application: You may use, for example, the internet banking facility. Here, internet banking is the application/program which does change (write) your balances in the database.

Other definitions:

A transaction is a unit of program execution that accesses and possibly updates various data items – Silberschatz, Korth, Sudarshan - Database System Concepts.

A transaction symbolizes a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions. A transaction generally represents any change in database - Wikipedia.

A transaction is an executing program that forms a logical unit of database processing. A transaction includes one or more database access operations—these can include insertion, deletion, modification, or retrieval operations - Ramez Elmasri, Shamkant B. Navathe - Fundamentals of Database Systems.

A transaction is defined as any one execution of a user program in a DBMS. A transaction is seen by the DBMS as a series, or list, of actions. The actions that can be executed by a transaction include reads and writes of database objects. A transaction can also be defined as a set of actions that are partially ordered. That is, the relative order of some of the actions may not be important – Raghu Ramakrishnan, Johannes Gehrke – Database Management Systems.

********************

Go back to Transactions Management in DBMS home page

ACID Properties in DBMS Explained

What are ACID properties? / Why ACID properties must be held by a transaction? / ACID properties explained with  example / What would be the problems with transactions without ACID properties?

ACID Properties

ACID properties are set of properties that guarantee a reliable, error free and consistent database transaction. It means a safer transaction. [Click on the following links Atomicity, Consistency, Isolation, Durability to know more]

Atomicity	It refers to “all or nothing”. That means, in a transaction every instruction is executed and the changes (specified through instructions) are made successfully in the database.
Consistency	It refers to a consistent database after execution. The consistent state may be old or new.
Isolation	All transactions that are executed simultaneously need to ensure the individuality. That is, the resultant state of database, after the simultaneous execution of several transactions should be equivalent to the serial execution of all the transactions.
Durability	It refers to the persistence of the changes made in the database. That is, after the successful completion of the transaction, the system must store the changes permanently in the hard disk.

 

Example:

Let us assume that a transaction T1 transfers 5000 from, say account A to account B. Also assume that the initial balance of A is 10000 and B is 10000. Transaction T1 shown below depicts the actual implementation of transfer of money from one account to the other;

Transaction T1

1. Read (A) 2. A:=A-5000; 3. Write(A) 4. Read(B) 5. B:=B+5000; 6. Write(B)

 Figure 1 - Transaction T1

Figure 2 - Transaction States

The table shown above clearly shows the instructions that are executed to complete the transaction T1. At first, T1 reads the value of A and perform subtraction (the amount that is to be transferred to B must be deduced from A). Then T1 writes the new value of A into the table. Next, T1 reads B, perform an addition, and writes the new value of A into the table. Hence, at the end of successful completion of T1, we will have 5000 in account A and 15000 in account B as shown in Figure 2.

Let us assume that system which executes the transaction T1 crashes exactly after the instruction ‘Write (A)’. After the crash, when the system recovers, we would find a new value of A, i.e, 5000. But, the value of B will be 10000, i.e, B is untouched. The reason is that the amount deduced from A is not added with B. This is an unwanted situation.

To get rid of the above situation, we need to ensure that every transaction is reliable. The ACID properties ensure this reliability for every transaction.

Atomicity ensures that either T1 can enter into the new consistent state S2 after successful completion of transaction or T1 can remain in the old consistent state S1 without affecting A and B.

Consistency ensures that the changes made are legal. That is, either S1 or S2, both should be valid states. It can be evaluated in many ways. For example, for our transaction T1, the sum of A and B, before and after the transaction will be same.

At S1 (Before transaction), A + B = 10000 + 10000 = 20000

At S2 (After transaction), A + B = 5000 + 15000 = 20000

Isolation: Assume that there is one more transaction T2 executed simultaneously along with T1 as given in Figure 3; also assume that the initial balance of account C is 5000.

Transaction T1	Transaction T2
1. Read (A) 2. A:=A-5000; 3. Write(A) 4. Read(B) 5. B:=B+5000; 6. Write(B)	1. Read (A) 2. A:=A-2000; 3. Write(A) 4. Read(C) 5. C:=C+2000; 6. Write(C)

  Figure 3 - Transaction T1 and T2 (Concurrent Execution)

T1 transfers 5000 from account A to B, and T2 transfers 2000 from account A to C. Instruction 3 in both transactions write new value of A into the database. If both transactions read A at the same time, then the Write which happened later would decide the new value of A. that is, T1 writes new value of A after T2 writes A, then the new value of A would be 5000. If T2 writes A after T1 has written, then the new value of A would be 8000. Both of these values are inconsistent. In a consistent transaction execution, at the end of the transactions, we need A with balance 3000, B with 15000, and C with 7000. Hence, these two transactions should be executed in isolated manner. Isolation property ensures the serial execution of transaction and leads to consistent databases.

Durability ensures that at the end of every successful transaction the data must be written permanently into the hard disk. That is, at the end the table should have new consistent values of A, B, and C in the hard disk.

************************

Go back to Transactions Management in DBMS home page