TM - Locking

Question 1

Locking

Answer 1

Mechanisms for central repository concurrency control.

Locks are variables linked with instances and holds state of instances acceptable access operations.

Question 2

Binary Locks

Answer 2

Can be locked or unlocked (1 or 0). Ideal for mutual exclusion. Indivisible operations.

Question 2

For transaction model, a Binary lock is invoked when:

Answer 2

Lock before R/W. Unlock after complete. No issue of Lock(x) if it already holds Lock. Same with unlock.

Question 3

Problem with Binary locks

Answer 3

Their Totality

Question 4

Multiple Mode Locks (Shared (R) or Exclusive (W) )

Answer 4

Lock can be in three states: Read, Write or Unlock.

Question 4

For transaction Model, a Multiple Mode lock is invoked when:

Answer 4

Issues R/W lock before r(X), Issues W lock before W(x). Issus unlock after all operations conclude, only if it holds R/W lock. Doesn’t issue locks if x already has a lock.

Question 4

Problem with Multiple Mode Locking

Answer 4

Use of locks alone does not guarantee serializability of transaction schedules. Stricter rules needed such as Two Phase Locking.

Question 5

Two Phase Locking

Answer 5

2PL Scheduler manages locks by controlling when to lock or unlock a transaction.

Question 5

If 2PL Theorem Holds:

Answer 5

No need to test for serializability of schedules.
Protocol enforces serializability.
Limits level of concurrency in a schedule.

Question 5

Important 2PL Theorem

Answer 5

If all transactions in a scheduler adhere to a 2PL protocol then the schedule is serializable.

Question 6

2PL protocol phases:

Answer 6

Lock Hoarding (Growing) phase [No locks released]
Releasing of Locks (Shrinking) phase [No locks required]

Question 7

Types of 2PLs

Answer 7

Conservative (Static)
Strict

Question 8

Conservative/Static 2PL

Answer 8

Hoards locks at start. If lock cannot be accessed it waits. Deadlock free. Difficult to use due to predeclaring R/Ws.

Question 8

Sharing Phenomena

Answer 8

SQL Standard tries to explain sharing protocol through phenomena. Running concurrent transactions at different levels of isolation allows application designers to trade throughput for correctness. Lower isolation levels increase transaction concurrency but risk assessing object’s state that have not yet been committed

Question 8

Strict 2PL

Answer 8

Releases all transaction locks after commit.
Not Deadlock free.
Guarantees Strict Schedule.
Recoverable Schedules and avoids cascading rollbacks.

Question 8

Scheduler needs a strategy for avoiding deadlocks:

Answer 8

This can be done simply by use of a time out span, after time limit exceeded, abort transaction.

Question 9

ANSI/ISO SQL Specs. 4 Isolation Levels:

Answer 9

Serializable (Most strict)
Repeatable Read
Read Committed
Read uncommitted (Least Strict)

Question 9

Common Criteria to know which transaction to kill in deadlock

Answer 9

Amount of effort already invested, Cost of abortion, Amount of effort to finish executing transaction, Number of cycles that contain transaction.

Question 10

Granularity of Locks

Answer 10

Concurrency control mechanism that works on database items. A RDBMS can include:

Relation tuple, tuple attribute, disk block, file structure, complete database.

Question 10

Design Considerations for locks;

Answer 10

Larger the size, lower the concurrency level. Smaller the size, larger number of locks that have to be maintained.

Question 11

Phantom Phenomenon

Answer 11

Any transaction processing related command needs to be translated to basic and explicit R/W on DB objects.

Object may be a tuple, attribute or even physical ex. disk.