Data Base week12_1

12/04/2010

Hi there,

I back again this class for theoretical exam:)

 
 

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 01

Insert a new record in SERVICE table

Stored Procedure 02

Insert a new record 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.

10. Phantom read SP01(SP03) and SP02

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

11. Appropriate concurrency control

SP01 INSERT SERVICE

Read Committed

SP03 INSERT CUSTOMER

Read Committed

SP02 INSERT ORDER and SELECT All table

Serializable