SQL Shared and Exclusive Locks Problem With only

SQL – Shared and Exclusive Locks

Problem: With only one type of lock, a transaction must acquire the lock even if it only need to read. It then blocks other readers (which wouldn't have generated a conflict). Readers need to take that lock to prevent transactions that want to write from changing the value while the original transaction is working. This unnecessarily slows the performance of the database. We need different types of locks for reading and writing!

Types of locks Shared Lock: ◦ ◦ Used for reading It can be given if there isn't an exclusive lock on the object Once it has been given, other shared locks are allowed (but no exclusive locks) sli(X) transaction Ti requests a shared lock on X Exclusive Lock: ◦ ◦ Used for writing It can only be given if there are no other transactions have locks on the object Once it has been given, no other locks are allowed until it is released xli(X) transaction Ti requests an exclusive lock on X

1. Consistency of transactions: A transaction may not write without holding an exclusive lock, and you may not read without holding some lock. More precisely, in any transaction T i, ◦ A read action ri(X) must be preceded by sli(X) or xli(X), with no intervening ui(X). ◦ A write action wi(X) must be preceded by xli(X), with no interven ing ui(X). ◦ All locks must be followed by an unlock of the same element. 2. Two phase locking of transactions: Locking must precede unlocking. ◦ To be more precise, in any two phase locked transaction Ti, no action sli(X) or xli(X) can be preceded by an action ui(Y), for any Y. 3. Legality of schedules: An element may either be locked exclusively by one transaction or by several in shared mode, but not both. More precisely: ◦ If xli(X) appears in a schedule, then there cannot be a following xlj(X) or slj(X), for some j other than i, without an intervening uj(X). ◦ If sli(X) appears in a schedule, then there cannot be a following xlj(X), for j != i, without an intervening ui(X).

Why do writers need an exclusive lock instead of just a shared lock? Because you shouldn't be able to write if any other transaction is able to write or read to that element Because writers are bullies Because if doesn't pay enough to be a writer Because writing is hard and needs protection