Defensive Db Programming Chapter 10
After dragging this review on for months now, it is finally time to bring this bad boy home and wrap it up. I have been working through a chapter by chapter review of the book by Alex Kuznetsov (Blog | Twitter). Alex has been taking us through the process and various techniques to better program defensively. This is the last installment in that series (yes shed a little tear). This chapter is devoted to teaching us how to better deal with Modifications that Survive Concurrency. Some of the problems associated with this are:
- lost modifications, a.k.a. lost updates – such problems occur when modifications performed by one connection are overwritten by another; these typically occur silently; no errors are raised
- resource contention errors – such as deadlocks and lock timeouts
- primary key and unique constraint violations – such problems occur when different modifications attempt to insert one and the same row. (p. 337)
Another way of stating these types of problems is that there is a “Race Condition.” You may have heard this term thrown around by the developers from time to time. It isn’t made up and is a very real potential problem that must be dealt with accordingly.
I can’t give this chapter enough props nor enough due through a short blog post on it. It is a lengthy chapter and covers a very important topic (my opinion). Alex covers each of the race conditions and continues to show how to resolve that race condition. He uses real world scenarios as you should have grown to see up to this point. I will point out that I had not considered using RowVerssion to help resolve some race conditions. That is at least one thing I have learned from reading this chapter.
Another thing that struck me and made me think is related to the TSQL patterns that Fail High concurrency. Here two methods are scrutinized and a third method is proposed. The third method is MERGE. That is a subject about which I could learn a lot.
For the final time (since this series has come to a close), go get the book and read it. You can find the rest of the articles by linking back through this link.