The following is a quick post in regards to deadlocking. In particular, we will be discussing Oracle and how to control deadlocking.
Oracle has a DBMS that can process database transactions. Deadlocking occurs when two or more transactions wait indefinitely for the other to release the lock on a previously locked data item. It is also called deadly embrace. For example, there are two transactions T1 and T2. If T1 has not unlocked data item Y, then T2 can not begin. If T2 has not unlocked data item X, then T1 cannot continue. Consequently T1 and T2 each wait for the other to unlock the required data item. Deadlocking can cause many complications and makes the data not readily accessible to the end user.
Three basic techniques can be used to control deadlocking. Prevention in which a transaction requesting a new lock is aborted when there is the possibility that a deadlock can occur. If the transaction is aborted, all changes made by the transaction are rolled back and all locks obtained by the transaction are released. The transaction will then be rescheduled. Detection where the DBMS can test the database for deadlocking can be helpful as well. If a deadlock is found then the transaction is aborted and restarted and the other transaction can continue. Lastly, avoidance is useful because the transaction must obtain all of the locks it needs before it can be executed. This technique avoids rolling back of conflicting transactions by requiring that locks be obtained in succession. The choice in deadlock control is determined by the database environment.