Database transactions-How to generate mutual wait-deadlock, how to solve

Database transactions-How to generate mutual wait-deadlock, how to resolve

1. The simplest deadlock

The most common deadlock is because one transaction executes too long, causing another transaction to fail to access the resource locked by the transaction, resulting in a deadlock exception that results in a time-out . There are only two solutions to this situation, one is performance tuning. The second is to continue making the transaction re-try when the transaction encounters a deadlock exception that is caused by a wait timeout.

2. Waiting for each other--deadlock

The exception to this deadlock is exceptional, with two very short transactions that can wait in a high-concurrency environment -a deadlock situation.

A) How is it caused?

In general, it is because two transactions are caused by a different order of cud execution of shared resources. As follows

Transaction 1: A--> B

Transaction 2:b--> A

In high concurrency, if transaction 1 finishes processing the cud of resource A at the same time, transaction 2 finishes processing the cud of resource B. This time the transaction 1 to the B resource crud at the same time transaction 2 also to a resource crud, and at this time, transaction 1 locks a resource, transaction 2 locks the B resource, so transaction 1 needs to wait for transaction 2 release B resources to continue to the B resource operation, opposite transaction 2 needs to wait for Transaction 1 release a resource to continue to a Resource operations. so two transactions are waiting for the lock to release each other, causing a deadlock

b) How to resolve?

The only way to do this is to make the two transactions the same cud execution order for shared resources.

Transaction 1: A--> B

Transaction 2: A--> B

Interesting question if transaction 2 is a-and B-and-a while transaction 1 remains the same, when transaction 2 executes the B-resource operation again on the A-resource operation will not have a deadlock, it seems that if transaction 1 also executes to the B resource operation-it seems to be, but the answer is not.
The reason is simple, because a resource has been locked by transaction 2, transaction 1 will not have the opportunity to start, only wait for transaction 2 to release a resource lock, get the lock transaction 1 to start execution.

Similarly, if we set the order of execution to resolve such a problem, would it cause the threads to wait too long for each other to cause a significant decrease in performance? (If transaction 2 executes first, then transaction 1 must wait for transaction 2 to finish, after releasing the lock on the A resource, the transaction 1 of the lock will be able to continue execution)
We don't have to worry about this at all, for the simple reason that in a large system, even in a high-concurrency state, the probability that two or more different threads of a transaction can concurrently access (in this case, CUD operations) to the same data in thousands data is quite small .

the necessary conditions for the deadlock although the process may occur during the operation of the deadlock, but the occurrence of deadlocks must have a certain condition, the occurrence of deadlocks must have the following four requirements.

　　1 ) mutually exclusive condition: refers to the process of allocating resources to the exclusive use, that is, for a period of time a resource is occupied by only one process. If there are other processes requesting resources at this time, the requestor can wait until the resource-occupying process is freed.

　　2 ) request and hold condition: means that the process has maintained at least one resource, but a new resource request has been made, and the resource has been occupied by another process, at which time the request process is blocked, but the other resources that you have obtained remain.

　　3 ) non-deprivation conditions: refers to the process has been obtained resources, before the end of use, can not be deprived, can only be released by themselves when the use is complete. (In cases where no timeout or higher priority deprivation is in order)

　　4 ) loop wait condition: refers to a deadlock, there must be a process-a circular chain of resources, that is, the process set {p0,p1,p2,,pn} P0 is waiting for a P1 to occupy the resources; P1 is waiting for P2 to occupy the resources, ... PN is waiting for resources that have been consumed by P0.

Detailed description of the deadlock

Database transactions-How to generate mutual wait-deadlock, how to solve