Chapter 10 - Transaction Management and Concurrency Control

Difference between request and transaction

Transaction - logical unit of work that must be entirely completed or aborted. No intermediate states acceptable

DB Request - equivelent of single SQL statement in transaction

Transactions are formed by two or more DB requests

_____ are formed by two or more DB requests

Transactions

What is concurrency control

coordinating the simulatneous execution of transactions in a multiuser DB system

Objective: ensure serializability of transactions in multi-user DB environment

Why is concurrency control important?

Simultaneous execution of transactions over a shared DB can create several data integrity/consistency problems

Concurrency control seeks to solve which 3 problems?

1. Lost updates

-two concurrent transactions update same data element... one is lost

2. Uncommitted data

-two transactions executed concurrently, first rolled back after second accessed uncommitted data

3. Inconsistent retrievals

-transactions might read some data before they are changed by another transaction, reading different data after the change

What is the purpose of the scheduler?

established the order in which the opeations are executed within concurrent transactions

What is a lock

Guarantees unique access to data item by transaction

Why do we need locks?

To prevent anther transaction from reading inconsistent data

Different lock levels

DB level

-entire DB locked while one person is updating... bad idea

Table level

-entire table is locked

-still bad idea

Page level

-diskpage locked

Row level

-concurrent Ts can access different rows of same table

Field level

-can access same rw as long as they use different attributes in that row

What type of lock is usually most useful

Row-level

What are the types of locks?

Binary

Shared/Exclusive

What are binary locks?

only have two states: locked or unlocked

What are exclusive locks?

access is reserved specifically for the transaction that locked the object

only some have access until T is over

Ex: when DB reads 'insert into' or any similar command, it will lock until T is done

Shared lock

concufrent transactions granted read access on basis of a common lock

Problems exclusive/binary locks can cause

1. resulting transaction schedule might not be serializable

2. DEADLOCKS

What is two phased locking

-defines how transactions acquire and relinquish locks

-ensures serializabilty

Two phases in two phase locking

1. Growing phase

-T acquires all required locks without unlocking any data

2. Shrinking

-T releases all locks and cannot obtain new lock

What are deadlocks

two transactions wait indefinitely for other to unlock data

two-phase locking ensures ______ and prevents ____

serializability

deadlocks

3 techniques to control deadlocks

1. Prevention

2. Detection

3. Avoidance

Deadlock prevention

Transaction requesting new lock is aborted when there is a possibility that deadlock can occur

Deadlock detection

DBMS periodically tests DB for deadlocks

If found, "victim" transaction aborted and other continues

As soon as deadlock happens, intervenes

Deadlock avoidance

Transaction must obtain all locks needed before can be executed

Avoids rolling back of conflicting transactions by requiring locks be obtained in succession

Takes a lot more processing ---> increases response times, not good for transaction heavy environmnet