19-04-2013, 03:03 PM
Concurrency Control and Recovery
Concurrency Control.ppt (Size: 99 KB / Downloads: 85)
In real life:
users access the database concurrently, and
systems crash.
Concurrent access to the database also improves performance,
yields better utilization of resources.
BUT: if not careful, concurrent access can lead to incorrect database
states. Crashes can also leave the database in incoherent states.
Basic concurrency/recovery concept: transaction
executed atomically. All or nothing.
We cover:
transactions in SQL
implementation of transactions and recovery.
Transactions
The user/programmer can group a sequence of commands so that
they are executed atomically and in a serializable fashion:
Transaction commit: all the operations should be done and recorded.
Transaction abort: none of the operations should be done.
In SQL:
EXEC SQL COMMIT;
EXEC SQL ROLLBACK;
Easier said than done...
Schedules
A schedule is an interleaving of a set of actions
of different transactions, such that the actions of
any single transaction are in order.
A schedule represents some actual sequence of
database actions.
In a complete schedule, every transaction either
commits or aborts.
Initial state + Schedule -> Final state.
Deadlock Prevention
Give each transaction a timestamp. “Older” transactions have
higher priority.
Assume Ti requests a lock, but Tj holds a conflicting lock. We can follow two strategies:
Wait-die: if Ti has higher priority, it waits; else Ti aborts.
Wound-wait: if Ti has higher priority, abort Tj; else Ti waits.
Note: after aborting, restart with original timestamp!