Concurrency Control Protocols Flashcards
What is a Concurrency Control Protocol?
A protocol that defines the way in which the database manages concurrent transactions and handles conflicts.
What are the 2 techniques of concurrency control protocols? Define each…
Pessimistic : Assumes transaction conflicts will occur, therefore, implements pre-emptive procedures to prevent conflicts. These include locking and timestamping.
Optimistic : Assumes that transaction conflicts are unlikely to occur. Therefore, doesn’t check for conflicts until the time of commit. This is less computationally expensive than pessimistic.
What is mean by locking? Which conflict management type uses it?
Locking is when an aspect of the database is locked during a transaction. This prevents other transactions operating on the locked component.
Locking is used in pessimistic control.
What are the 2 types of locking? Define each…
Shared : This is used in read operations. The lock can be acquired simultaneously by multiple transactions. This is because read transactions don’t risk transaction conflict.
Exclusive : This is used in write transactions. This lock on the database can only be acquired by one transaction at a time. This means only one transaction can write at a time.
What is an issue with single locking?
It doesn’t enforce serialisability.
What is the solution to the single locking issue?
Implement 2 phase locking.
Define 2 phase locking… What are the 2 phases in it?
- 2 phase locking implements serialisability into the transactions. This prevents conflicts. It does this by ensuring all locking operations are completed before the first unlock operation is completed.
- Growing phase : The locking of all the database components in question.
- Shrinking phase : The release of the locked items. No new locks can be acquired.
What issue can 2 phase locking cause?
Deadlock : When 2 or more transactions are waiting to acquire a lock that is held by the other.
What are the solutions to Deadlock caused by 2PL?
Timeout : Transactions are given a time limit to acquire the lock. Rollback occurs if the time limit is exceeded.
Issue with Timeout : The time may be exceeded even if the transaction is not waiting to acquire a lock, and a rollback may occur despite deadlock not being present.
Deadlock Detection and Recovery: A wait-for-graph is created a analysed for cycle. If a cycle is present, deadlock has occurred and a rollback can be conducted.
Deadlock Prevention : Each transaction is timestamped, and wait-die or wound-wait algorithms are executed if needed.
What are the wait-die and wound-wait algorithm?
Wait-die : Old transactions wait for younger transactions to complete first.
Wound-die : Young transactions wait for older transactions to complete first.
Define Timestamping…
Each transaction has its creation time noted, thus the age of the transaction can be monitored. This means that older transactions can take precedence over younger transactions in the case of conflicts. No locks are used.
Define what is meant by optimistic techniques…
Transactions are only checked for conflict just before the commit takes place. If a conflict is found, a rollback occurs.
