How does db2 handle deadlock

For more details see the references at the end of the article. Now let's go back and consider the following simple use case, which by the way I think is pretty common. Each order has a unique order number, which is generated by the application and the next available value is kept in a separate table COUNTER.

The initial value will be 1. You might object that in such situation the counter table could eventually be replaced by SQL sequences or an identity column. At this moment, let us keep this over-simplified scenario. Looks pretty easy, right? Let's say there are two users that are placing different orders concurrently - user A and user B. The sequence of operations could be like this:. This example shows that a concurrent activity with the previous setup may cause unsuccessful inserts or, which is worse, can break the consistency of data.

If you need to ensure that only one row is returned i. OK, the problem with the previous scenario was that we were releasing the lock too early. What could we do about that? Maybe the idea could be to make the lock duration a bit longer - more restrictive isolation level could help in this case. If we keep the code and switch the isolation level from CS to Read Stability RS this is what will happen - RS guarantees that any concurrent updates or deletes to the qualified rows are prevented.

So, it seems it could help us right? Well, it appears that it will not work that nicely in case of parallel operations. Again, let's assume we have concurrent users A and B, placing the orders in almost the same time. Remind - S-locks and X-locks are not compatible. So, we have seen two possible problems with our simple scenario - either corrupting a database, or causing deadlocks. It seems that isolation levels will not help us here.

If so, what could be good candidates how to correctly deal with this situation? This was actually my initial idea and it relates to my initial question - if there could be a situation where a fetch from a cursor may be a better solution than using a singleton SELECT. It shows that a cursor even for fetching one row may have sense.

Though there is a slight performance penalization caused by the cursor management. How the pseudo code would look like in this case? With this code, we can also switch safely back to the CS isolation level. You might ask, what makes the real difference here? Effectively, we have created a mutual exclusive section, and so the data corruption nor a deadlock cannot occur. What can happen though is that B gets a timeout if B is waiting for the lock too long because A is doing very long business in its step.

However, that would be another story more related to proper commit strategy. This helps DB2 to avoid ambiguous cursors and to acquire proper locks, which can reduce the lock contention of read only applications. The simple scenario we use in this article can be simply solved using a simple update at the beginning, which takes X lock directly from the start.

That would suspend B until A commits. Here we update the counter at the first place. If a concurrent activity just updated the value, we must wait until it commits - the X-lock acquired by an UPDATE will prevent surprises. Otherwise, if there is no X-lock, we can continue, update the counter, acquiring an X-lock, inserting a new row and committing.

So, in fact we are back to the original question and it seems that the singleton SELECT is a perfect and probably most elegant solution here.

Optimistic locking is another solution that came to my mind. Although it would require some changes - both in DDL and in the code. The idea behind the optimistic locking is having a timestamp or a token in the COUNTER table that will be updated automatically after each update. It means that in our code we could check if there was some update since we read the data in that table.

The DDL would require the following change:. The code however, needs to be changed a bit as well:. If the value has not changed - no one else updated the value - we update the value and acquire an X-lock on that row or page. The optimistic locking is usually recommended in the situations where you are optimistic that no one will update the value. If there is a great chance that the concurrent updates happen frequently, this is not a best solution and is usually better to rely on the DB2 locking.

Back to top. Pradyutonnet, Quote:. Actually the above DB2 table was defined long year back and then release by release new programs used the same table for there processing and this is becoming further exacerbated with new releases putting even greater pressure on this table.

Do we have possible solution to prevent it from deadlock and also in order to improve access time of table. Do Frequent Commits. As sushanth mentioned do a brainstorming over your application design too Hello, Have you verified that you are dealing with a deadlock rather than a timeout.

Sometimes, they are mistakenly viewed as the same problem. Either email addresses are anonymous for this group or you need the view member email addresses permission to view the original message. Miro Flasza. If you're using read stability or repeatable read isolation level, then the select statement won't release the locks on the previously read rows until commit see the admin guide for the explanation of different isolation level semantics. If you have another transaction performing concurrent updates on the same table, then this can lead to a deadlock.

Hi Miro: Thanks for your reply. Fan Ruo Xin. HI, I have the same error message when rename and create table, "QLN The current transaction has been rolled back because of a deadlock or timeout.