Question

Given the properties of a transaction and what types of concurrency problems we can experience, how does locking and blockers (subjects we covered earlier in the course) come into play in all of this?

Answer 1

Properties of Transaction are :

Generally there are four main key of the Transaction , which are ACID (Atomicity , Consistency , Isolation , Durability)

• Atomicity :   

  All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are.

  For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account.

• Consistency :

Data is in a consistent state when a transaction starts and when it ends.

For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.

• Isolation:

The intermediate state of a transaction is invisible to other transactions. As a result, transactions that run concurrently appear to be serialized.

For example, in an application that transfers funds from one account to another, the isolation property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither.

• Durability :

After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure.

For example, in an application that transfers funds from one account to another, the durability property ensures that the changes made to each account will not be reversed.

Problems due to concurrency:

There are many which may occur due to concurrency,

1) Dirty read problem

If a transaction reads an uncommitted temporary value written by some other transaction than it is called dirty read problem. In this one transaction read a data item updated by another uncommitted transaction that may be future be aborted or failed. In such cases, the read value disappears from the database upon abort this is turned on dirty read the reading transaction end with incorrect results.

Example

    T1      T2
            R(A)
            W(A)
    R(A)

The values of item x which is read by T2 is called dirty read data because this data can be created by a transactions that has not been committed yet.

2) Loss update problem/ write - write problem :

This problem occur when two transactions access the same data item and have their operations interleaved in a way that makes the value of some database items incorrect.

If there are two write operations of the different transaction on some data values and in between them there are no read operations then the second write over the first .consider the schedule below,

Example

    T1      T2
    R(A)
    W(A)
            W(A)

Here is a blind write that means write without a read. Here the changes made by transaction T1 are lost which is updated by a transaction T2.

3) Unrepeatable and phantom read problem:

When a transaction cannot repeat the read instructions because the variable is deleted by some other transaction then this problem is called phantom read problem. In this problem at different instances of time a transaction read gives different values it is because data item might have been updated by another transaction.

This causes a problem while execution of some aggregate by a transaction and due to changes in the values of the data item by another transaction it leads to incorrect results. When a transaction read values of data item twice and another transaction's updates data item in between then the results of two read operations will differ.

Example

    T1          T2
    R(A)                
                R(A)
    Delete(A)                         
                R(A)

4) Incorrect summary problem:

When one of the transactions is checking on aggregate summary function while other transactions are updating then this problem is called incorrect summary problem. The aggregate functions may calculate some values before they updated and others after they are updated.

Identify the root blockers and the total wait caused

DPA collects blocking data and the Blocking Tab provides visibility into the root blocker (the top of the tree) with aggregated wait time. This allows DBAs to see the top blocking data and its impact on response times. DPA reveals the top waiters, so the DBA can quickly drill down and find their blockers and optimize the queries that reduce blocking the most.

Focus on the important queries to reduce blocking time

The Top Blocking Sessions Tab reveals post-lock activity that prevents blocked sessions from completing their transactions and releasing locks. This lets the DBA optimize the activity of the top blocker queries, decrease the time that locks are held, and reduce blocking wait time.

See which objects are waiting as a result of blocking

It’s often not possible for a DBA to identify queries, programs, users, etc. that are waiting on blockers and slowing down transactions. DPA offers a remedy to this problem. DPA’s intuitive dashboard allows users to identify waiters and quickly drill down and find their blockers. All of this can be done from DPA’s dashboard in just a few clicks.

Identify the last query of an idle blocker

There are times when blockers become idle. In other words, they are blocking without performing any specific function. DPA tracks these blocker sessions backward in time to discover the last known queries that were running within the transaction before it went idle. Armed with that info, the DBA can collaborate with the developer to fix the application code causing the idle blocking. DPA provides visibility into idle blockers along with the top blockers.

I had written these answer by self please thumbs up, and if you have any other query please feel free to ask in comment.