Define concurrency control
is the process of managing simultaneous operations on the database without having them interfere with one another.
what are the objectives of concurrency control
- schedule transactions to avoid interference
- guarantee serialisability
- Enforce ACID properties
What is pessimistic concurrency control
assumes that conflict is likely and take steps to prevent it
- Locking, timestamping
- relational DB mostly use
What is optimistic concurrency control
assumes that conflict is unlikely and only checks for it when transaction commits
Define locking
when a transaction is accessing the database, a lock may deny access to other transactions to prevent incorrect results.
What must a read operation acquire
- Shared/read lock
What must a write/update operation acquire
an exclusive/write lock
What can locks be acquired to
- The whole database
- a file
- a page/a block block
- a record
- A field value of a record
What are the rules of locking
― A transaction must issue read_lock(X), or write_lock(X) before any read(X).
― A transaction must issue write_lock(X) before any write(X).
― A transaction must issue unlock(X) after read(X) or write(X).
Does shared/exclusive locks guarantee serialisability
NO, more strict protocols are required i.e two phase locking
What is two phase locking
Two-Phase Locking (2PL): All locking operations precede the first unlock operation in a transaction.
What is the drawing/expanding phase
uring which new locks on items can be acquired but none can be released;
What is the shrinking phase
during which existing locks can be released but no new locks can be acquired.
Define deadlock
A circular situation where each of two (or more) transactions are waiting to acquire locks that are held by the other.
What are the techniques for handling deadlock
- Timeouts
- Deadlock detection and recovery
- Deadlock prevention
What is a time out
- A transaction will wait for a (database defined) period to acquire a lock.
- If this time runs out then the whole transaction is rolled back and restarted.
What is a wait for graph
a graph with a node for each transaction. There are directed edges from e.g. T1 to T2 if T1 is waiting to lock an item currently help by T2
When does a deadlock exist in the wait lock graph
A deadlock exists if the graph contains a cycle.
What does DBMS need to do once a deadlock is detected
DBMS needs to abort one or more of the transactions
What is starvation in deadlock detection and recovery
Starvation:thesametransactionisalwayschosenasavictim,andthetransactioncannever complete.
What are the issues to consider in recovery
― Which transaction to roll back?
* Abort transactions that incur the minimum costs.
― How far to roll a transaction back? * Totalrollbackorpartialrollback
― Avoid starvation
What are the two types of deadlock prevention
Wait-die algorithm
― only older transactions can wait for younger ones
Wound-wait algorithm
― only younger transactions can wait for older ones
Define time stamp
A unique identifier created by the DBMS that indicates the relative starting time of a transaction.
― Either a logical counter or the system clock.
Define time stamping
a concurrency control protocol that orders transactions in such
a way that older transactions get priority in the event of conflict.
