What are the advantages of multiple transactions that are allowed to run concurrently in the system?

A transaction is a unit of database processing which contains a set of operations. For example, deposit of money, balance enquiry, reservation of tickets etc.

Every transaction starts with delimiters begin transaction and terminates with end transaction delimiters. The set of operations within these two delimiters constitute one transaction.

main()
{
   begin transaction
} end transaction

There are three possible ways in which a transaction can be executed. These are as follows −

• Serial execution.
• Parallel execution.
• Concurrent execution.

Concurrent transaction or execution includes multiple transactions which are executed concurrently or simultaneously in the system.

Advantages

The advantages of the concurrent transactions are as follows −

• Increases throughput which is nothing but number of transactions completed per unit time.

• It reduces the waiting time.

Example

T1= 90sec
T2= 500sec
T3= 5sec.

If we execute serially by T1->T2->T3 then transaction T3 waits for 590 sec, so we go for non-serial or concurrent transactions to reduce waiting time.

i.e. T3 -> T1 -> T2.

Disadvantage

The disadvantage is that the execution of concurrent transactions may result in inconsistency.

Example 1

The concurrent execution given below is in a consistent state.

Example 2

The transaction given below is not consistent.

Concurrent schedules do not keep the database in the consistent state. The concurrent execution of the transactions has to be carried out in a controlled environment.

If a system consists of ‘n’ number of transactions, we will have more than ‘n!’ number of concurrent schedules, out of which only a few of them are keeping the database in consistent state.

Updated on 06-Jul-2021 14:47:29

• Related Questions & Answers
• Explain about nested queries in DBMS
• Explain about conflict serializability in DBMS
• Explain about 2NF with an example in DBMS
• Explain about triggers and active databases in DBMS
• Explain about two phase locking (2PL) protocol(DBMS)
• Explain about the Time stamp ordering protocol in DBMS
• Explain about insert command in Structured query language in DBMS
• Explain about Create, Insert, Select command in structure query language (DBMS)?
• Explain about financial strategy.
• Explain about Transfer pricing
• Explain about Varargs in java?
• Explain about StringJoiner in java8?
• Explain about derivatives in finance.
• Explain Select command in DBMS
• Explain set operators in DBMS

Concurrency Control deals with interleaved execution of more than one transaction. In the next article, we will see what is serializability and how to find whether a schedule is serializable or not.

What is Transaction? 

A set of logically related operations is known as a transaction. The main operations of a transaction are:

Read(A): Read operations Read(A) or R(A) reads the value of A from the database and stores it in a buffer in the main memory.

Write (A): Write operation Write(A) or W(A) writes the value back to the database from the buffer. 

(Note: It doesn’t always need to write it to a database back it just writes the changes to buffer this is the reason where dirty read comes into the picture) 

Let us take a debit transaction from an account that consists of the following operations:

1. R(A);
2. A=A-1000;
3. W(A);

Assume A’s value before starting the transaction is 5000.

• The first operation reads the value of A from the database and stores it in a buffer.
• the Second operation will decrease its value by 1000. So buffer will contain 4000.
• the Third operation will write the value from the buffer to the database. So A’s final value will be 4000.

But it may also be possible that the transaction may fail after executing some of its operations. The failure can be because of hardware, software or power, etc. For example, if the debit transaction discussed above fails after executing operation 2, the value of A will remain 5000 in the database which is not acceptable by the bank. To avoid this, Database has two important operations: 

Commit: After all instructions of a transaction are successfully executed, the changes made by a transaction are made permanent in the database.

Rollback: If a transaction is not able to execute all operations successfully, all the changes made by a transaction are undone.

Properties of a transaction:

Atomicity: As a transaction is a set of logically related operations, either all of them should be executed or none. A debit transaction discussed above should either execute all three operations or none. If the debit transaction fails after executing operations 1 and 2 then its new value of 4000 will not be updated in the database which leads to inconsistency.

Consistency: If operations of debit and credit transactions on the same account are executed concurrently, it may leave the database in an inconsistent state.

• For Example, with T1 (debit of Rs. 1000 from A) and T2 (credit of 500 to A) executing concurrently, the database reaches an inconsistent state.
• Let us assume the Account balance of A is Rs. 5000. T1 reads A(5000) and stores the value in its local buffer space. Then T2 reads A(5000) and also stores the value in its local buffer space.
• T1 performs A=A-1000 (5000-1000=4000) and 4000 is stored in T1 buffer space. Then T2 performs A=A+500 (5000+500=5500) and 5500 is stored in the T2 buffer space. T1 writes the value from its buffer back to the database.
• A’s value is updated to 4000 in the database and then T2 writes the value from its buffer back to the database. A’s value is updated to 5500 which shows that the effect of the debit transaction is lost and the database has become inconsistent.
• To maintain consistency of the database, we need concurrency control protocols which will be discussed in the next article.  The operations of T1 and T2 with their buffers and database have been shown in Table 1.

Table 1

Isolation: The result of a transaction should not be visible to others before the transaction is committed. For example, let us assume that A’s balance is Rs. 5000 and T1 debits Rs. 1000 from A. A’s new balance will be 4000. If T2 credits Rs. 500 to A’s new balance, A will become 4500, and after this T1 fails. Then we have to roll back T2 as well because it is using the value produced by T1. So transaction results are not made visible to other transactions before it commits.

Durable: Once the database has committed a transaction, the changes made by the transaction should be permanent. e.g.; If a person has credited $500000 to his account, the bank can’t say that the update has been lost. To avoid this problem, multiple copies of the database are stored at different locations.

What is a Schedule? 

A schedule is a series of operations from one or more transactions. A schedule can be of two types: 

• Serial Schedule: When one transaction completely executes before starting another transaction, the schedule is called a serial schedule. A serial schedule is always consistent. e.g.; If a schedule S has debit transaction T1 and credit transaction T2, possible serial schedules are T1 followed by T2 (T1->T2) or T2 followed by T1 ((T2->T1). A serial schedule has low throughput and less resource utilization.
• Concurrent Schedule: When operations of a transaction are interleaved with operations of other transactions of a schedule, the schedule is called a Concurrent schedule. e.g.; the Schedule of debit and credit transactions shown in Table 1 is concurrent. But concurrency can lead to inconsistency in the database.  The above example of a concurrent schedule is also inconsistent.

Question: Consider the following transaction involving two bank accounts x and y: 

1. read(x);
2. x := x – 50;
3. write(x);
4. read(y);
5. y := y + 50;
6. write(y);

The constraint that the sum of the accounts x and y should remain constant is that of? 

1. Atomicity
2. Consistency
3. Isolation
4. Durability                                                                                                                                                                                         [GATE 2015]

Solution: As discussed in properties of transactions, consistency properties say that sum of accounts x and y should remain constant before starting and after completion of a transaction. So, the correct answer is B. 

Advantages of Concurrency:

In general, concurrency means, that more than one transaction can work on a system.

The advantages of a concurrent system are:

• Waiting Time: It means if a process is in a ready state but still the process does not get the system to get execute is called waiting time. So, concurrency leads to less waiting time.
• Response Time: The time wasted in getting the response from the cpu for the first time, is called response time. So, concurrency leads to less Response Time.
• Resource Utilization: The amount of Resource utilization in a particular system is called Resource Utilization. Multiple transactions can run parallel in a system. So, concurrency leads to more Resource Utilization.
• Efficiency: The amount of output produced in comparison to given input is called efficiency. So, Concurrency leads to more Efficiency.
   

Next article- Serializability of Schedules 

Article contributed by Sonal Tuteja. Please write comments if you find anything incorrect, or if you want to share more information about the topic discussed above. 

What are the advantages of concurrently running transactions?

What are the benefits and challenges of concurrent transactions?

What are the advantages of concurrent execution over serial execution?

What is the main goal of allowing concurrent execution of transactions?