- Consider the following
2 transactions:
Notice that:
- T1 and T2 only reads the DB elements A and B
- A possible schedule of
T1
and
T2 is:
- Technically:
- T2
could proceed when
T1 holds
the lock for
DB element A
because:
- The value of the DB element A will not be changed by T1
- So there was no technical reason to block the T2....
- T2
could proceed when
T1 holds
the lock for
DB element A
because:
- Conclusion:
- The exclusive lock is too restrictive in some situations
- The shared/exclusive
locking method:
- Uses 2 kinds of
locks:
- Shared lock
- Exclusive lock
- Lock semantics:
- DB element X can have
one of the
following locks:
- 1 exclusive lock
- any number of shared locks
- DB element X can have
one of the
following locks:
- Uses 2 kinds of
locks:
- Transactions
using
shared/exclusive
locking:
- In order to perform
a read operation
on a DB element X:
- the transaction must be holding a shared lock on the DB element X
- In order to perform
a write operation
on a DB element X:
- the transaction must be holding a exclusive lock on the DB element X
- A shared/exclusive lock operation must be followed by an unlock operation !!!
- In order to perform
a read operation
on a DB element X:
- Notations:
- sli(X) =
transaction Ti
requests a
shared lock on
the DB element X
- xli(X) =
transaction Ti
requests a
exclusive lock on
the DB element X
- ui(X) =
transaction Ti
unlocks on
the DB element X
- ui(X) can only be applied if transaction Ti currently holds a (shared or exclusive) lock on the DB element X
- sli(X) =
transaction Ti
requests a
shared lock on
the DB element X
- The following 3 types of requirements
can guarantee that
current execution
transactions will be
conflict-serializable:
- Transaction consistency (semantics):
- In order to perform
a read operation
on a DB element X:
- the transaction must be holding a shared lock on the DB element X
- In order to perform
a write operation
on a DB element X:
- the transaction must be holding a exclusive lock on the DB element X
- A shared/exclusive lock operation must be followed by an unlock operation !!!
- In order to perform
a read operation
on a DB element X:
- 2 Phase Locking:
- All the unlock operations must follow all the lock operations within one transaction
- Legality of the
lock schedule:
- A DB element X can be in
one of the
following states:
- Unlocked
- exclusively locked by
one transaction
(with no shared locks !!!)
- shared locked by one or more transactions
- Unlocked
- A DB element X can be in
one of the
following states:
Note 1:
- A transaction
cannot hold:
- a shared lock and
- an exclusive lock
on the same DB element
- Therefore,
a transaction that
- read the DB element X and then
- write the DB element X
must request an exclusive lock for the DB element X
Note 2:
- We can prove
formally that:
- Shared/exclusive locking can guarantee that current execution transactions will be conflict-serializable
(Using induction -- just like the case with exclusive lock)
(The text book has omitted the proof)
- Transaction consistency (semantics):