Question

Why are the COMMIT and ROLLBACK statements necessary? Explain their relationship to one of the transaction properties. Explain all four possible concurrency problems and provide examples of each.

typed please

Answer 1

COMMIT and ROLLBACK are Transaction Control Language commands which are used to manage transactions in the database.These are used to manage the changes made to the data by Data Manipulation Language statements.

A transaction begins when data is read or written.

A transaction ends with a COMMIT or ROLLBACK statement or with an end of the application process.

We know COMMIT statement commits the database changes that were made during the current transaction ,making the changes stable.

The ROLLBACK statement backs out ,or cancels the database changes that are made by the current transaction and restores the changed data to the state before the transaction began.

The initiation and termination of a transaction defines points of consistency .Here point of consistency is a time when all recoverable data that an application can accesses is consistent with other data.If a transaction is successful and data is consistent and same at different points of time COMMIT will initiated to end the transaction. If a transaction fails ROLLBACK operation starts, with these two statements data will be in consistent state.

Concurrency Problems:

When multiple transactions execute concurrently in an uncontrolled or unrestricted manner then it might lead to concurrency problems.

Dirty Read Problem

Unrepeatable Read Problem

Lost Update Problem

Phantom Read Problem

Dirty Read Problem

Reading the data written by the uncommitted transaction is called as the dirty read problem because the uncommitted transaction might rollback later ,this makes other transactions read a value that does not even exist.

This leads to an inconsistency data.

Example:

Suppose a class teacher(T1) is entering the performance of the students in database,at that time only principal(T2) is reading the data and checking marks. due some problem (T1) has failed so all data is rolled back. but (T2) has the incorrect data(i.e partial data). therefore database becomes inconsistent.

Unrepeatable Read Problem:

This problem occurs when a transaction gets to read unrepeate i.e different values of the same variable in its different read operations even when it has not updated its value.

TRANSACTION T1                            TRANSACTION T2

R(x)                                                     R(x)

W(x)                                                    R(x)

Here T1,T2 reads the x value =10

T1 changes x=10 to x=15

now T2 reads x values as 15 instead of 10.

T2 does not know to the value has changed. According to it ,it runs in isolation.

Lost Update Problem:

This problem occurs when multiple transactions execute concurrently and updates from one or more transactions get lost.It occurs whenever there is a write-write conflict without any read in the middle.

Example:

                        Transaction (T1)                                               Transaction (T2)

                            R(A)

                           W(A)                                                                       W(A)

                                                                                                         COMMIT

                          COMMIT

Here

T1 reads A=10

T2 updates value A=15 in the buffer

T2 write A=25 write without read in buffer

T2 commits

when T1 commits it writes A=25 in the database.

In this T1 writes the overwritten value of A in the database.

thus update from T1 gets lost.

Phantom Read Problem:

This problem occurs when a transaction reads some variable from the buffer and when it reads same variable later ,it finds that the variable does not exist.

Example:

Transaction (T1)                               Transaction(T2)

   R(X)                                                     

                                                              R(X)

             DELETE(X)

                                                               R(X)

Here

T1 reads X

T2 reads X

T1 deletes x

T2 tries to read x.but does not find it.