- "Good" ordering
of locking operation:
- Transactions T1 and
T2 lock
DB elements A and B in
the same order:
- Interleaved execution of
transactions T1 and
T2
does not result in
deadlock:
- Transactions T1 and
T2 lock
DB elements A and B in
the same order:
- "Bad" ordering
of locking operation:
- Transactions T1 and
T1 locks
DB elements A and B in
the opposite order:
- A execution that
leads to
deadlock:
- Transactions T1 and
T1 locks
DB elements A and B in
the opposite order:
- The cause of the
deadlock:
- The transactions T1 and
T2 have each
locked the
DB elements A and B
in
reverse order:
- T1 locks A ⇒ B
- T2 locks B ⇒ A
- The transactions T1 and
T2 have each
locked the
DB elements A and B
in
reverse order:
- Technique
to prevent
deadlock from
developing in
locking:
- Impose
an ordering on
the DB elements
Example ordering:
- Consider database addresses (= a series of bits) of database element as a binary number
- Locking order:
- Transactions must request locks on DB elements in the ordering of the DB elements
- Impose
an ordering on
the DB elements
- Example:
- A transaction T wants to
lock the
DB elements:
- DB element 567
- DB element 789
- DB element 123
- Lock request order made
by transaction T:
- DB element 123
- DB element 567
- DB element 789
- A transaction T wants to
lock the
DB elements:
- Claim:
- If all transactions
request locks
on database elements in the
order of the
database elements,
then:
- Deadlock is not possible
- If all transactions
request locks
on database elements in the
order of the
database elements,
then:
- Proof: by
contradiction
- Suppose that:
- Transactions
T1, T2, ..., Tn
request locks
on database elements according to
the ordering of the
database elements
and
- Transactions T1, T2, ..., Tn are deadlocked
- Transactions
T1, T2, ..., Tn
request locks
on database elements according to
the ordering of the
database elements
and
-
Without lost of generality,
let's assume that:
- Transaction T1 waits for transaction T2
- Transaction T2 waits for transaction T3
- ....
- Transaction Tn-1 waits for transaction Tn
- Transaction Tn waits for transaction T1
Specifically:
- T1 has locked some DB element An and T2 is waiting for a lock on DB element An
- T2 has locked some DB element An-1 and T3 is waiting for a lock on DB element An-1
- T3 has locked some DB element An-2 and T4 is waiting for a lock on DB element An-2
- ...
- Tn−1 has locked some DB element A2 and Tn is waiting for a lock on DB element A2
- Tn has locked some DB element A1 and T1 is waiting for a lock on DB element A1
The wait-for graph is:
- Now:
- Consider the
database elements that
transaction T2
locks:
- T2 has locked the database element An-1 (first) and
- T2 is
waiting
(trying to lock) for the
database element An
(next)
Since transaction T2 will lock database element according to their order, we conclude that:
- An-1 < An
- Consider the
database elements that
transaction T3
locks:
- T3 has locked the database element An-2 (first) and
- T3 is
waiting
(trying to lock) for the
database element An−1
(next)
Since transaction T3 will lock database element according to their order, we conclude that:
- An-2 < An-1
- And so on
- Therefore, we have that:
- A1 < A2 < ..... < An−1 < An
- Consider the
database elements that
transaction T2
locks:
- But
we also have:
I.e.:
- T1 has locked the An and
- T1 is waiting for the A1
we conclude that:
- An < A1
- Therefore:
- An < A1 < A2 < ..... < An-2 < An-1 < An
And we have a contradiction:
- An < An
Therefore:
- The assumption that
n transactions can be
involved in a deadlock is
wrong
- So deadlock is not possible
- Suppose that: