Exam Preparation DataBase Concurrency Problem

Concurrency Problems

1. Concurrency problems

• If locking is not available and several users access a database concurrently, problems may occur
• If their transactions use the same data at the same time.
• 
  
• Lost updates.
• Uncommitted dependency (dirty read).
• Inconsistent analysis (non-repeatable read).
• Phantom reads.
• http://msdn.microsoft.com/en-us/library/aa213029%28SQL.80%29.aspx

2. Lost update

• Successfully completed update is overridden by another user.

3. Uncommitted dependency (dirty read).

• Occurs when one transaction can see intermediate results of another transaction before it has committed.

4. Inconsistent analysis (non-repeatable read).

• Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time.
• Inconsistent analysis is similar to uncommitted dependency in that another transaction is changing the data that a second transaction is reading.
• However, in inconsistent analysis, the data read by the second transaction was committed by the transaction that made the change.

5. Inconsistent analysis (non-repeatable read) example
6. Phantom Reads

• Records that appear in a set being read by another transaction.
• Phantom reads can occur when other transactions insert rows that would satisfy the WHERE clause of another transaction's statement.
• http://db.apache.org/derby/docs/10.0/manuals/develop/develop71.html

7. Phantom Reads example
8. Back to Marcia's Drycleaning

• Stored Procedure 01Insert a new record in SERVICE table
• Stored Procedure 02 Insert a new record or UPDATE in ORDER and ORDER_ITEM table Also select data from CUSTOMER and SERVICE
• Stored Procedure 03 Insert a new record in CUSTOMER table
• Concurrency problems may occur between 01 and 02, between 02 and 03.

9. Dirty read SP01(SP03)and SP02

• SP02 trusts SERVICE table data, but it has not been committed yet.

10. Non-repeatable reads SP02 and User Operation

• Non-repeatable reads may occur between SP02 and other user operations.

11. Phantom read SP01(SP03) and SP02

• SP02 trusts SERVICE table data, but it has not been committed yet.

12. Appropriate concurrency control

• SP01 INSERT SERVICE Read Committed
• SP03 INSERT CUSTOMER Read Committed
• SP02 INSERT ORDER or UPDATE and SELECT All table Serializable

If you are interested in my PPT, please check it:)
https://docs.google.com/leaf?id=0B8gU4bWkOtLvNjlhNTA1YWYtMGY0Zi00YTA2LWIwYTYtYjdhYzY4ZDE1Y2Vj&hl=en