Hey Everyone,

Post your solutions to the Aug. 29 In-Class Exercise to this thread.

Best,

Chris

User A executes a transaction that acquired account_balance lock and needs account_credentials User B executes a transaction that acquired account_credentials lock and needs account_balance

Because User A's transaction and User B's transaction are both requesting locks that are already held, neither will advance and release the lock they have already acquired. Thus, these two transactions have resulted in a deadlock.

Let’s assume we have two users, A and B. We also have two resources, X and Y. The following scenario will lead to a deadlock and the transaction manager will have to intervene: User A's transaction has acquired the lock to resource X. Next, User B's transaction acquires the lock to resource Y. Now, transaction A would like to get access to resource Y and transaction B wants access to resource X. A is waiting for B to release Y, while B is waiting for A to release X. They will wait forever if the transaction manager will not intervene.

When there are 2 transactions A and B. Transaction A has a lock on a data that it is currently updating. Transaction B has a lock on another data that it is updating. Now while A updating it needs the data that transaction B has a lock on. Simultaneously Transaction B needs to access data that A has a lock on. So now both of them cannot use the data and a deadlock occurs.

Two transactions A and B and two tables T1 and T2

A has a lock for T1 and needs to update T2 in order to finish.

B has a lock for T2 and needs to update T1 in order to finish.

This situation will cause a deadlock because A and B both need locks that the others have to move forward and cannot release their current locks until they finish.

Let's say there is a certain type of transaction that needs to perform 2 operations on resource A and B, atomically. If 2 instances of such a transaction is initiated, and their sequence is as follows: Transaction 1: Performs operation on resource A. Waits for resource B. Transaction 2: Performs operation on resource B. Waits for resource A. Since both transactions are holding one resource and waiting infinitely for another resource which is held, hence there is a deadlock.

The transaction 1 needs table A and table B to finish transaction. Transaction 2 also needs table A and B to finish transaction. But transaction 1 is holding table A and waiting table B. Transaction 2 is holding table B and waiting table A. It occurs deadlock.

Transactions A and B need each other’s resources, but are unable to do so because both are unable to release their own resources until they get the other’s.

An example would be when Table A needs data from Table B but table B is locked. Furthermore, table B needs data from table A but table A is locked. This will cause a deadlock.

An example of a situation where a deadlock would occur between two transactions would be if one transaction has a set of data but needs access to another set of data that is held by the second transaction. However, the second transaction needs access to the set of data that the first transaction is holding. This will cause a deadlock.