Defensive database programming: eliminating IF statements.

re: Defensive database programming: eliminating IF statements.

Bernd Eckenfels — Fri, 28 Nov 2008 06:43:11 GMT

I use this pattern oft for optimistic locking, and I always wonder what to do if rowcount is different from 0 or 1. Sure it should not happen, and shure I can log an alert if it happens. But what does my program logic do? Fail or Proceed. The first one might be a problem for availability, the second one for consitency... but I think I am worrying hypothetically only :)

re: Defensive database programming: eliminating IF statements.

Alexander Kuznetsov — Fri, 28 Nov 2008 14:21:21 GMT

Bernd,

In my example there is a UNIQUE INDEX UNQ_TwoInts_ID ON dbo.TwoInts(ID), so my rowcount can only be 0 o1 1. Can you enforce uniqueness in your situation?

re: Defensive database programming: eliminating IF statements.

Bernd Eckenfels — Sun, 30 Nov 2008 01:09:30 GMT

Yes it is enforced, thats why I wonder what to do if rowcount > 1 :)

re: Defensive database programming: eliminating IF statements.

Alexander Kuznetsov — Sun, 30 Nov 2008 02:36:42 GMT

Bernd,

This sounds interesting. Can you post a repro script?

re: Defensive database programming: eliminating IF statements.

Richard Howells — Sun, 30 Nov 2008 13:55:49 GMT

I’m not sure I understand this article properly.

At the point when it claims to stop losing updates, that’s not really true. It just keeps trying until an update succeeds. When I added another counter I discovered that to count to 10,000 required about 17,000 attempts. Updates are still being lost just not reported.

It seems to me that the whole idea is wrong. IMO this is what locking hints are for. I think the SELECT outside the stored procedure should add WITH (UPDLOCK) and that select should be in a transaction that includes the SP call. This allows the outer transaction to be confident that the selected values CAN’T change until the outer transaction commits. Now I can count to 10,000 in 10,000 attempts. This solution is also significantly faster. The article solution takes about 10 seconds on my system. Using the locking hint reduces it to 8 – about 20% faster.

re: Defensive database programming: eliminating IF statements.

The Noble Savage — Sun, 30 Nov 2008 15:45:57 GMT

Richard - care to share your code with the crew? Your idea sounds interesting.

re: Defensive database programming: eliminating IF statements.

Richard Howells — Sun, 30 Nov 2008 17:05:20 GMT

@The Noble Savage - Sure. Here's my outer loop, makes no difference which version of the SP is used.

DECLARE @i1 INT, @i2 INT, @version ROWVERSION, @count INT, @ret INT;

SET @count = 0;

WHILE @count<10000 BEGIN

begin transaction

SELECT @i1=i1+1, @i2=i2, @version=version FROM dbo.TwoInts with (updlock) WHERE ID=5;

EXEC @ret=dbo.UpdateTwoINTs 5, @i1, @i2, @version;

SET @count = @count + 1 - @ret;

commit transaction

END;

re: Defensive database programming: eliminating IF statements.

Alexander Kuznetsov — Sun, 30 Nov 2008 18:31:06 GMT

Richard,

To understand my article, you need to know the difference between optimistic and pessimistic locking. Suppose you are making a hotel reservation, you read a row, opened up a screen form, and you are thinking if you want to reserve this room. If the application read the row with (updlock), then other users cannot read this row until you are done with it. This is called pessimistic locking. this is what you are suggesting. It is rarely used in high concurrency OLTP because it usually results in very poor throughput - in real life there could be several minutes between a SELECT and a subsequent EXEC. I am considering a different approach in this article.

On the other hand, if the application just reads a row without holding locks, then other users cannot read and modify this row while you are making your decision. Then at the time you are saving your changes, you may overwrite other people's changes. This is called a lost update. This is why at the time your changes are being saved, the system needs to detect if the row was modified by somebody else and report an error if it was. This approach is called optimistic locking, that's what I'm saying at the very beginning: "implement optimistic locking using rowversion", with a strong emphasis on "optimistic". Each individual update is incrementing either i1 or i2 by one, so when scripts are completed, both i1 and i2 should be 100000 unless some updates were lost.

HIH

re: Defensive database programming: eliminating IF statements.

Chris — Sun, 30 Nov 2008 20:43:01 GMT

Or you could just check @@ROWCOUNT. If someone updated to a new version between the if exists and update, the version would be changed an dthe update would fail. Even better, you probably don't need the EXISTS check at all.....

re: Defensive database programming: eliminating IF statements.

Alexander Kuznetsov — Sun, 30 Nov 2008 20:51:26 GMT

Chris,

The whole point of this article is precisely this: in most cases you do not need the IF EXISTS check at all.

re: Defensive database programming: eliminating IF statements.

Uri Dimant — Mon, 01 Dec 2008 05:07:55 GMT

Hi Alex.Great post

Tony also described the similar technique on his web site.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx

re: Defensive database programming: eliminating IF statements.

Alexander Kuznetsov — Mon, 01 Dec 2008 14:32:48 GMT

Hi Uri,

Yes I like Tony's posts too, I read them all. In this case mutexes are not needed - the single row UPDATE itself under READ COMMITTED works as a mutex.

re: Defensive database programming: eliminating IF statements.

Chris Falter — Tue, 02 Dec 2008 22:47:00 GMT

In the original SP, many updates got lost because 2 threads both satisfied the IF EXISTS condition and then attempted competing updates.

Given the new SP, if 2 threads are competing, one of them will fail the WHERE clause, and no update will occur.

The important thing to note, and I don't think you emphasized it enough, is that in the improved design you *must* check @@ROWCOUNT to make sure the update succeeded, and if not the system *must* take corrective action. IMHO the best way to make sure corrective action is taken is for the improved SP to call RAISERROR if @@ROWCOUNT = 0. I would be reluctant to trust all client code that calls my SP to check the value of @ret and take corrective action; expecting the client code to implement that correctly is an invitation to bug city. Using RAISERROR, though, means that the client code is forced to take corrective action. Sure, your script checks the value of @ret and essentially re-tries if it fails, but I don't know anyone who writes real systems with such simplistic error handling.

I agree that in the UPDATE case that you have addressed, mutexes are not needed. However, in the insert case, Tony's solution looks very appealing.

re: Defensive database programming: eliminating IF statements.

SJ — Wed, 03 Dec 2008 06:35:59 GMT

I am novice in database scripting.

I tried out your example and found that around 45% of the updates were lost.

But could not fgiure out the reason for the same; please offer insight!

Thanks!

re: Defensive database programming: eliminating IF statements.

Alexander Kuznetsov — Wed, 03 Dec 2008 20:14:49 GMT

Chris,

I am not with you on this "client code is forced to take corrective action" suggestion. Because any database call can raise an exception, it should be wrapped in a try block anyway. A trivial catch block, such as the following:

catch(Exception e){

Console.Write(e);

}

will catch an error without any corrective action whatsoever. I don't think that you can force the client to do the right thing by just throwing exceptions. More to the point, instead of Tester-Doer Pattern I used a perfectly legitimate TryParse Pattern, both described here:

http://blogs.msdn.com/kcwalina/archive/2005/03/16/396787.aspx

Also I disagree with your distrust of client code. I think most client languages such as C# and C++ are far superior to T-SQL, easier to handle errors properly.

re: Defensive database programming: eliminating IF statements.

Alexander Kuznetsov — Wed, 03 Dec 2008 20:15:44 GMT

SJ,

Can you be more specific? Which example did you run? I provided several ones.

re: Defensive database programming: eliminating IF statements.

JKG — Fri, 05 Dec 2008 00:35:05 GMT

Interesting. I've worked on more than a few high demand over worked databases where locking is an issue. They are usually doing a lot of reads and few writes so it's less of a concern but I have to admit i use the IF EXISTS a lot.

I mostly use it because i like to collapse "Create" and "Update" sprocs so it looks something like...

IF EXISTS(SELECT 1 FROM dbo.SomeTable WHERE <Some Conditions>)

BEGIN

UPDATE dbo.SomeTable SET .. WHERE SomeTableID = @SomeTableId;

END ELSE BEGIN

INSERT INTO dbo.SomeTable ...

END;

Any better way to handle this situation short forcing the client code to do it?

I suppose doing the UPDATE first then checking the row count would work but the code seems ugly.

re: Defensive database programming: eliminating IF statements.

Alexander Kuznetsov — Fri, 05 Dec 2008 01:42:45 GMT

JKG,

On 2008 you can use the new MERGE command. Prior to that, it is more involved than I could fit in a short answer. Let me write it up over the weekend as a separate post. One thing is clear - moving the logic to the client will not handle race conditions, you will encounter the same problems.

Defensive database programming: fun with UPDATE.

Alexander Kuznetsov — Mon, 08 Dec 2008 16:04:34 GMT

It is well known that UPDATE ... FROM command does not detect ambiguities. Also it well known that ANSI

Stress testing UPSERTs

Alexander Kuznetsov — Mon, 15 Dec 2008 01:37:40 GMT

Suppose that you need to implement the following logic: IF(row exists) Update the row ELSE Insert a new

Defensive database programming: SET vs. SELECT.

Alexander Kuznetsov — Sun, 25 Jan 2009 22:57:02 GMT

Comparing SET vs. SELECT is a very popular topic, and much of what I have to say has been said before.

re: Defensive database programming: eliminating IF statements.

Chris Falter — Tue, 10 Feb 2009 14:02:44 GMT

Wow, where do I start? First of all, I didn't say I did not trust clients to catch exceptions. I in fact advocated the throwing of an exception from the stored proc precisely because exception handling is far better design for clients than expecting client-side code to pass around (and eventually evaluate/handle) failure codes.

Secondly, you did not implement the TryParse pattern correctly. For starters, you would need to name your stored proc something like "TRY_UpdateTwoInts" to make it clear to the client that the client must not expect that standard exception handlers will handle the possible error condition. In addition, when you implement the pattern you must also, according to Cwalina, implement a paired exception-throwing member for clients that do not want to make use of the TryParse pattern.

Third, I seriously doubt the wisdom of using the TryParse pattern in the vast majority of circumstances. In typical production scenarios, it is extraordinarily rare that a race condition would yield an error on more than a few percent of attempted updates, so the performance advantages of TryParse do not outweigh the associated disadvantage of design complexity. I am addressing the general type of situation that the reader of your blog might actually face in his/her server-side code, not an artificial circumstance of a blogger's sample code which is written with the expressed intention of manifesting a race condition.

re: Defensive database programming: eliminating IF statements.

Alexander Kuznetsov — Tue, 10 Feb 2009 16:28:52 GMT

Chris,

I agree that TryUpdateTwoInts is a better name.

Summarizing previous posts about defensive database programming

Alexander Kuznetsov — Mon, 09 Mar 2009 01:49:19 GMT

I have been posting examples of defensive database programming for some time now. I am by no means done

Defensive database programming: fun with ROWCOUNT

Alexander Kuznetsov — Sun, 22 Mar 2009 03:05:51 GMT

I have written up two examples when a SET ROWCOUNT command breaks a seemingly working stored procedure

Defensive database programming: fun with triggers.

Alexander Kuznetsov — Tue, 12 May 2009 01:19:33 GMT

There are three kinds of triggers: those which blow up and those which fail silently ;). Seriously, there

Defensive database programming: conditions in WHERE clause can evaluate in any order DRAFT ONE

Alexander Kuznetsov — Sun, 12 Jul 2009 03:14:15 GMT

My query used to work, but it blows up after I have added an index? The following query is not safe:

When you add an index and your query blows up...

Alexander Kuznetsov — Thu, 16 Jul 2009 21:41:16 GMT

You cannot assume that the conditions in your WHERE clause will evaluate in the left-to-write order -

Alexander Kuznetsov : Defensive database programming: fun with changing column widths.

Fri, 09 Oct 2009 20:44:05 GMT

PingBack from http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/11/19/defensive-database-programming-fun-with-changing-column-widths.aspx