Concurrent Execution of a Transaction in DBMS pdf

Transaction:

A transaction is defined as a logical unit of work that they required. It consists of more or more operations on a database that must be completed together so that the database remains in a consistent state. 

Suppose any type of information on the stock is stored in the database. The database stores two things i.e. sold list and quantity, This is one operation on the database. However, the quantity of the sold item n must also is subtracted from the items currently in stock. If both steps are not completed together, the database will be in an inconsistent state. The number of items in stock will be wrong if items are sold but not subtracted from items in stock. 

Commit and Rollback:

A transaction is committed if it completes successfully and changes any type of data. if it fails and leaves the data unchanged, it is said that the transaction has been rolled back.

Transaction properties (ACID properties):

A transaction must have four properties that are known as ACID properties.these are followed:

Atomicity

A transaction must be an atomic or unique unit of work. if any statement in a transaction fails, the entire transaction fails completely.when a transaction is executed successfully it is to be committed.in case of failure of a statement, all previous successful statements in transactions are rolled back or reverted to the previous state of consistency. 

Consistency 

A transaction must leave any type of data in a consistent state after completion.for example, in a bank type of database, money should never be "created" or "deleted" without an appropriate deposit or withdrawal.

Isolation

All transactions that modify any type of data are isolated from each other. They do not access the same type of data at the same time. Transactions must have no dependence or effect on other transactions execute. A modifying transaction can access any type of data only before or after another transaction is completed.

Durability

The durability means that the modification made by a transaction are permanent and persistent. if the system is crashed or rebooted data should be guaranteed to be completed when the computer restarts. 

Concurrency:

concurrency is a situation in which two or more users access the same piece of data at the same time. In a multi-user environment, concurrency occurs very commonly. In some suitable situations, the concurrent access may arise to some serious problems coming in it.

Concurrency Problems

Different problems that may occur due to concurrency are as following:

Lost update Problem

The major problem arises when two or more transactions update the same type of data concurrently.

Each transaction is unaware of other transactions execute. The last update overwrites the new updates made by the previous transactions. It results in the loss of any type of data.

Uncommitted Dependency Problem 

This problem arises when two or more transactions work on the same table, one transaction retrieves or updates a certain part of any type of data before other transaction rollbacks update on the same type of data. The uncommitted dependency occurs when a second transaction selects a row that is being updated by another transaction to execute. The second transaction is reading any type of data that has not been committed yet and may be changed by the transaction or transmitted that is updating the row.

Inconsistent Analysis problem  

An inconsistent computer data analysis problem occurs when a transaction file reads several values from the computer database management system but another transaction updates some of them during the executing of the first transaction of a file. The inconsistent data analysis is similar to uncommitted dependency in that another transaction is changing any type of data that a second transaction is reading in memory.

Resource Locking:

One way to prevent concurrency problems is to lock the shared data which is of any type that we use. Locking ensures that the shared data of any type can be used by one user at one time only. When a user accesses the data, the second user has to wait until the first user finishes his work

Lock Terminology:

Some important terms related to the locking mechanism asr ads follows:

• Implicit Lock
• Explicit Lock
• Exclusive Lock
• Shared lock
• Lock Granularity

Serializable Transaction Schedules:

A transaction consists of one or more read/write operations according to a certain sequence. When two or more transactions are present in a field, we can examine which of these operations can be used without harming the original sequence of a file.

Serial Schedule:

It is a plan to execute transactions serially very easily. The internal sequencing of each transaction is preserved in a file. A serial schedule ensures that each transaction executes as if it is the only one accessing the database at one time.

Serializable Schedule:

It is a non-serial schedule in which transaction operations are interleaved while ensuring consistency.

Properties of Serializable Schedule:

• A serializable schedule is equivalent to some serial schedule that may be unique or not.
• It has no read and writes conflicts that can affect database consistency.
• It does not suffer from issues such as lost updates, dirty read, etc.

Deadlock:

Deadlock is a situation in which two transactions wait for the completion of each other. The first transaction needs a resource that is locked by the second transaction and the second transaction needs a resource that is locked by the first transaction. Deadlock is caused by locking.

Suppose there are two items A and B in the computer database management system. Two users need these two items to complete their work in a specific time. The following sequence of lock will result in a situated where user 1 has locked the item A and the user 2 has locked the item B. The user 1 needs item B to complete his work. The user 2 needs item A to complete his work. Both are waiting for each other. This situated is known as deadlock. 

The solution of Deadlock:

 The deadlock problem in DBMS can be solved in two ways:

1. Deadlock prevention
2. Deadlock Detection & Recovery

Deadlock Prevention:

There are different ways for preventing a computer deadlock in DBMS to occur. One way is to allow different users to issue only one lock request at a time. The users must lock all required at once. 

Deadlock Detection & Recovery:

If a deadlock occurs, it needs to be detected and then recovered. In order to recover from a deadlock situation, one transaction needs to be killed.