G4G0-1/Semester 2/Database Systems/Week 6/Week 6 Database Systems.md

Locks

Transactions can ask DBMS to place locks on data items in the database. Locks prevent another transaction from modifying data, or in the case of an exclusive lock, reading too. Locks are implemented by inserting a flag on the object, or in a list of locked parts. Objects of various sizes can be locked ( database, table, tuple, data item ), the size determines the granularity.

Exclusive Locks

Necessary for write access - gives exclusive access to an item. If a transaction has an exclusive lock on an item, it can both read and update the item. Writes can conflict, so only one transaction can lock an item for writing.

Shared Locks

Sufficient for read-only access. If a transaction has a shared lock on an item, it can read, but not update, the item. Reads cannot conflict, more than one transaction can hold shared locks simultaneously.

Usage

Any transaction accessing data must first lock the item by requesting

• Shared Lock ( Read-Only Access )
• Exclusive Lock ( Read & Write Access ) If the item is not already locked, the lock will be granted. If the item is locked, the DBMS determines the compatibility of the requested lock and the existing lock.
• If shared lock requested on an item with a shared lock, the request is granted.
• Otherwise, the transaction must wait until lock released. A transaction holds a lock until explicit release during execution / termination.

Two-Phase Locking ( 2PL )

Transaction follows 2PL protocol if all locking operations precede first unlock operation in the transaction. Each transaction divided into 2 phases:

• Growing Phase: acquires all locks, but cannot release.
• Shrinking Phase: releases all locks, but cannot acquire. No requirement for locks to be obtained simultaneously. Rules:
• Transaction must acquire a read or write lock before operating on an item.
• On release, the transaction may not acquire new locks.

Observations on 2PL

• If every transaction follows 2PL, the schedule is guaranteed to be conflict serialisable.
• However, 2PL does not permit all possible serialisable schedules, or prevent deadlocks.
• Variations of 2PL exist, but lie outside of our scope.

Lost Update Problem

• We can prevent this issue by utilising exclusive locks, not allowing T1 to perform any conflicting operations before T2 has committed and unlocked balx:
• 

Uncommitted Dependency Problem

• Similarly to the previous issue, we can solve this by locking T3 until the data item is unlocked by T4 to preserve consistency.
• 

Inconsistent Analysis Problem

• We can solve this issue by locking the data items appropriately. By write locking balx and balz in T5, T6 must wait to modify or read any data from either of these data items to preserve consistency.
• 

Tutorial

1. .
   1. Locks are used to preserve database consistency by disallowing reads and / or writes.
   2. Locks are implemented by requiring transactions to request locks before reading or updating an item.
   3. An exclusive lock is a write lock, restricting read and write access of the database item to the transaction that requested it.
   4. An exclusive lock is granted if no other lock is currently in effect on a data item.
2. .
   1. False, a durable transaction is where all changes to data are persistent, even through power loss or system failure. The statement is an example of atomicity.
   2. True
   3. True
   4. False, mutual blocking of data access in 2PL causes a deadlock
3. .
   1. .

Time	Transaction 1	Transaction 2	balance
t1	begin		100
t2	read_lock( balance )		100
t3	read( balance )		100
t4	balance:=balance-60	begin transaction	40
t5	…	wait	40
t6	abort, rollback. Release lock ( balance )	wait	100
t7		read_lock( balance )	100
t8		read( balance )	100
t9		balance:=balance-10	90
t10		write( balance )	90
t11		commit. Release lock ( balance )	90

Laboratory

1. 
2. 
3. 
4.