Question

Database Integration (Concurrent Transaction Execution) Any help would be appreciated please!

• Can you explain three common problems with concurrent transaction execution and how concurrency control can be used to avoid them?

• Describe an example that illustrates either:
• A problem with concurrent transaction execution and its effects on the operational performance of the system
• The effectiveness of concurrency control

Answer 1

Concurrent Transaction Execution

Multiple transaction happens simultaneously over a shared database. It allow multiple transaction to update the same data concurrently.

Problems

1. Temperory update problem or dirty read problem

If two transaction accessing the same database, before the first transaction complete its update, the second transaction trying to access the updated value from first tranasaction,then the problem occurs. In other words, when one transaction updates a table row and then cancels the update with ROLLBACK, the second tramsactopn access the temporarily updated before it is changed back to it's original value. This reslt is database inconsistency.

2. The lost update problem

The lost update problem occurs when 2 concurrent transactions try to read and update the same data. Consider 2 transaction viewing same stock amount as 12. First transaction sold 3 items and second transaction sold 2 items. But first transaction updated the stock as 9 and second transaction updated the stock as 10. This happens because of concurrent execution of same data on a database. This also will lead to data inconsistency.

3. The incorrent summary problem

This occurs when one transaction performs an aggreagate function on a number of records and other transaction updating values of some of these records. Example of such problem is what happens in stock related data when the stock returns while taking the overall stock details.

Concurrency control protocols

Lock-based

To perform a transaction on data, it needs a lock.

1. Shared lock - The transaction with this lock can only read and can't update the data.

2. Exclusive lock - Transaction with this lock can both read and write, but multiple trasaction can't do it

Time-stamp

A timestamp is a unique identifier used in DBMS to identify a transaction. Timestamp values are assigned in the order in which the transactions are submitted to the system, so a timestamp can be thought of as the transaction start time by using system time or logical counter. Concurrency control techniques based on timestamp ordering do not use locks; hence, deadlocks cannot occur.

The algorithm confirms the sequence for each item accessed and  does not break the sequencing .

Validation based

In this technique, no checking is done while the transaction is been executed. Database is not updated until all transactions are executed. All updates are applied to local copies of data items kept for transaction. At the end of transaction execution, validation phase ,checks whether any of transaction updates violate serializty. If there is no violation of serializability the transaction is ted; or else, the transaction is updated in the database.

All these method provide congestion control, database consistency and integirty to the data stored in database.