2: Locks

Question 1

What is a lock ?

Answer 1

A transaction can acquire a lock on a data item prior to accessing that item.

This guarantees exclusive use of that item, and the lock is released when the transaction is complete.

Question 2

What is the point of a lock ?

Answer 2

Locks prevent other transactions from reading inconsistent data, as this cannot be guaranteed during a transaction.

Question 3

Who manages locking procedures ?

Answer 3

DMBS lock manager initiates and enforces the procedures.

Question 4

At which levels can lock granularity take place?

Answer 4

Database, table, page, row, attribute

Question 5

What are the two lock types?

Answer 5

• Binary locks: 2 states (1 locked, 0 unlocked), has to unlock after termination
• Shared/ Exclusive locks: shared allows other transactions to read the object, exclusive only the chosen one.

Question 6

What are the two main locking issues ?

Answer 6

Locks prevent data inconsistencies but can cause transactions to be:

• Non-serialisable
• Deadlocks

Question 7

What can be used to solve locking issues ?

Answer 7

Two Phase Locking Protocol (2PL).

It uses a ‘growing phase’ to acquire all the locks w/o unlocking any that time, and then conversely uses a ‘shrinking time’ to release them without locking anything else in the meantime.

Question 8

What are deadlocks ?

Answer 8

T1 accesses items X and Y whilst T2 accesses items Y and X and neither has unlocked each others’ required item.

May happen if one of the transactions wants an exclusive lock on an item.

Does not occur amongst shared locks.

Question 9

What are the 3 ways to control deadlocks ?

Answer 9

• Deadlock Prevention: transaction requesting a new lock is aborted if there is possibility of deadlock. Abort rolls back the transaction and releases all of its locks that are being held at the time. (Wow, this like in the Flash)
• Deadlock Detection: DMBS tests for deadlocks and aborts one of transactions whilst allowing the other to continue
• Deadlock Avoidance: Transaction must obtain all locks before it can execute. This avoids rollback but may be slower due to having to acquire all locks that are needed.

Question 10

Can deadlock occur when a transaction requests a shared lock on an item?

Answer 10

No – deadlock occurs on exclusive lock issues.

Question 11

Which of the following can be used for deadlock detection?

I. Timeout
II. Precedence Graph
III. 2PL Protocol
IV. Wait-For Graph
V. Rolling back a transaction

Answer 11

I. Timeout

and

IV. Wait-For Graph