03 - Four Concurrency Problems

Hi,

Here we’ll cover below:

-          Concepts of transactions and locking.

-          Types of Locks & Lock compatibility

-          Locking issues - Blocking (Long-term locking), and Deadlocking

'Famous Four' Concurrency Problems

The 'famous four' database concurrency problems occur when more than one user attempts to:

-          Read data that another is modifying.

-          Modify data that another is reading.

-          Modify data that another transaction is trying to modify.

The famours four are as below – PLND (Planned)

PHANTOM READS	-          Phantom reads occur due to a transaction being able to read a row on the first read, but not second time, as the same row is been deleted by another transaction rows from the same table. -          The phantom is the missing or new row.	For example suppose that a banker would like to offer a low interest rate all individuals with a balance >= $3000. The banker runs a SP two queries, to retrieve the list first and then Update their interest rate. The first query at 10.00 AM returns names of individuals with a balance of $3000 or higher, where it found Mr. Green’s account. In the mean time at 10.01 , Mr. Green closes his account since he's about to move out of town. The second query at 10.02 got fail as the second query does not show Green's address on the list since he no longer holds an account with this bank. The phantom is the missing or new row.
LOST UPDATES	-          Lost updates occur if you let two transactions modify the same data at the same time, and the transaction that completes first is lost. -          Reason - READ UNCOMMITTED isolation level.
NONREPEATABLE READS	-          Non-repeatable reads occur if a transaction is able to read the same row multiple times and gets a different value each time. -          These occur when a transaction is reading data while a second transaction is modifying the same data. Data retrieved from the first query does not match the second query. -          Reason - READ UNCOMMITTED isolation level.	Same as above, ecxcept minor change in Blue Text. In the mean time at 10.01 , Mr. Green withdrawn 2500$. The second query at 10.02 got fail as the second query will found less balance.
DIRTY READS	-          Dirty reads are a special case of non-repeatable read. -          Dirty reads occur while a transaction is updating a row, and a second transaction reads the row before the first transaction is committed. -          If the original update rolls back, the data read by the second transaction is not the same, hence a dirty read has occurred.	Suppose that I intend to withdraw $200 from ATM, and my beginning balance is $1000. I punch in my account number and PIN, and the ATM reduces my balance to $800. At this point, I check my wallet, and am pleasantly surprised that my wife hasn't spent $300 from the last paycheck. I cancel my transaction immediately because I don't feel safe walking around with $500 in my wallet. If a banker were to run a report after I started my transaction but before I cancelled it, my balance would show up as $800, even though I never did withdraw the $200.

Thanks & Regards,

Arun Manglick || Senior Tech Lead