Search results matching tag 'Defensive programming'

The client code that handles timeouts

Alexander Kuznetsov — Thu, 10 Jan 2013 21:05:00 GMT

After timeouts we need to make sure that active transactions, if any, are rolled back. All timeout handling must be done on the client. This post provides the implementation and unit tests.

Implementation

The following class extends SqlCommand and rolls back active transactions after timeouts:

public static class SqlCommandExtentions { public static void ExecuteNonQueryWithErrorHandling(this SqlCommand command) { try { command.ExecuteNonQuery(); } catch (SqlException e) { HandleSqlException(command, e); throw; } } public static SqlDataReader ExecuteReaderWithErrorHandling(this SqlCommand command) { try { return command.ExecuteReader(); } catch (SqlException e) { HandleSqlException(command, e); throw; } } private static void HandleSqlException(SqlCommand command, SqlException e) { const int timeoutCode = -2; if (e.Number != timeoutCode) return; var rollbackCommand = command.Connection.CreateCommand(); rollbackCommand.CommandText = "IF @@TRANCOUNT > 0 BEGIN ; ROLLBACK ; END ;"; rollbackCommand.CommandType = CommandType.Text; rollbackCommand.CommandTimeout = 0; rollbackCommand.ExecuteNonQueryWithErrorHandling(); } }

Testing

We need to unit test the two methods. For each one, we need to test the following four cases:

Command succeeds
Command blows up with some other error, which must not be caught by our handling
Command times out, active transaction
Command times out, no active transaction

The following tests cover these four cases for ExecuteReader. The other four tests are very similar, so there is no need to post them.

[Test] public void ExecuteReaderWithErrorHandling_WorksOnSuccess() { using (var dr = ExecuteReaderWithErrorHandling(sqlConn, "SELECT 1 AS n")) { Assert.IsTrue(dr.Read()); Assert.AreEqual(1, dr.GetInt32(0)); } } [Test] public void ExecuteReaderWithErrorHandling_HandlesTimeout_ActiveTransaction() { try { ExecuteReaderWithErrorHandling(sqlConn, "EXEC dbo.IWillTimeOut", timeoutInSeconds: 1); Assert.Fail("Must throw exception"); } catch(SqlException e) { Assert.AreEqual(-2, e.Number); VerifyNoOpenTransaction(sqlConn); } } [Test] public void ExecuteReaderWithErrorHandling_HandlesTimeout_NoActiveTransaction() { try { ExecuteReaderWithErrorHandling(sqlConn, "EXEC dbo.IWillTimeOutWithoutTransaction", timeoutInSeconds: 1); Assert.Fail("Must throw exception"); } catch (SqlException e) { Assert.AreEqual(-2, e.Number); VerifyNoOpenTransaction(sqlConn); } } [Test] public void ExecuteReaderWithErrorHandling_ThrowsOtherExceptions() { try { ExecuteReaderWithErrorHandling(sqlConn, "EXEC dbo.IThrowError8134"); Assert.Fail("Must throw exception"); } catch (SqlException e) { Assert.AreEqual(8134, e.Number); VerifyNoOpenTransaction(sqlConn); } }

These unit tests utilize a few database objects:

CREATE FUNCTION dbo.IWillCompleteInNSeconds ( @NumSeconds INT ) RETURNS INT AS BEGIN ; DECLARE @StartTime DATETIME , @Toggle INT ; SELECT @StartTime = GETDATE() , @Toggle = 1 ; WHILE DATEDIFF(SECOND, @StartTime, GETDATE()) < @NumSeconds BEGIN ; SET @Toggle = 1 - @Toggle ; END ; RETURN 0 ; END ; GO CREATE PROCEDURE dbo.IWillTimeOut AS BEGIN ; BEGIN TRANSACTION ; SELECT dbo.IWillCompleteInNSeconds( 5 ) AS SomeNumber ; COMMIT ; END ; GO CREATE PROCEDURE dbo.IWillTimeOutWithoutTransaction AS BEGIN ; WAITFOR DELAY '00:00:05' ; END ; GO CREATE PROCEDURE dbo.IThrowError8134 AS BEGIN ; SET XACT_ABORT ON ; DECLARE @i INT ; SELECT @i = 1/0 ; SELECT 1 AS n; END ;

Yet another use of OUTER APPLY in defensive programming

Alexander Kuznetsov — Wed, 06 Jun 2012 21:58:00 GMT

When a SELECT is used to populate variables from a subquery, it fails to change them if the subquery returns nothing - and that can lead to subtle bugs. We shall use OUTER APPLY to eliminate this problem.

Prerequisites

All we need is the following mock function that imitates a subquery:

CREATE FUNCTION dbo.BoxById ( @BoxId INT ) RETURNS TABLE AS RETURN ( SELECT CAST(1 AS INT) AS [Length] , CAST(2 AS INT) AS [Width] , CAST(3 AS INT) AS [Height] WHERE @BoxId = 1 ) ;

Let us assume that this subquery is slow and we do not want to run it more than absolutely necessary.

Short recap on SET vs. SELECT

The following approach uses SET to populate variables:

DECLARE @Length INT , @Width INT , @BoxId INT ; SELECT @Length = 0 , @Width = 0 ; SET @BoxId = 1 ; SET @Length = ( SELECT [Length] FROM dbo.BoxById(@BoxId) ) ; SET @Width = ( SELECT [Width] FROM dbo.BoxById(@BoxId) ) ;

This script will always change the values of variables, even when the subquery returns nothing, which is good. However, it runs a possibly slow/expensive subquery twice. Also it duplicates code. We want to avoid both these disadvantages.

The following script uses SELECT, it has no duplication, and it runs the subquery only once:

DECLARE @Length INT , @Width INT , @BoxId INT ; SELECT @Length = 0 , @Width = 0 ; SET @BoxId = 1 ; SELECT @Length = [Length] , @Width = [Width] FROM dbo.BoxById(@BoxId) ; SELECT @Length AS [Length] , @Width AS [Width] ;

This code looks cleaner and performs better, but it has the following problem: if the subquery returns nothing, the variables are not changed. You can change @BoxId from 1 to any other number, rerun the script, and see for yourself.

When the variables are not changed by an assignment, that can lead to subtle bugs,

like this one.

To avoid this vulnerability, we can use OUTER APPLY, as follows:

DECLARE @Length INT , @Width INT , @BoxId INT ; SELECT @Length = 0 , @Width = 0 ; SET @BoxId = 2 ; SELECT @Length = [Length] , @Width = [Width] FROM (SELECT 1 AS PlaceHolder) AS AlwaysOneRow OUTER APPLY dbo.BoxById(@BoxId) ; SELECT @Length AS [Length] , @Width AS [Width] ;

As a result, we still have no duplication, run an expensive subquery only once, and also always change the variables, making the code less vulnerable to bugs.

"Trusted" Foreign Keys Allow Orphans, Reject Valid Child Rows

Alexander Kuznetsov — Mon, 17 Oct 2011 17:15:00 GMT

In SQL 2008 R2, MERGE does not implement foreign keys properly. I will show both false negatives (valid rows are rejected) and false positives - orphan rows that are allowed to save.

False Negatives

The following tables implement a very common type/subtype pattern:

CREATE TABLE dbo.Vehicles( ID INT NOT NULL, [Type] VARCHAR(5) NOT NULL, CONSTRAINT Vehicles_PK PRIMARY KEY(ID), CONSTRAINT Vehicles_UNQ_ID_Type UNIQUE(ID, [Type]), CONSTRAINT Vehicles_CHK_ValidTypes CHECK([Type] IN ('Car', 'Truck')) ); GO CREATE TABLE dbo.Cars(ID INT NOT NULL, [Type] AS CAST('Car' AS VARCHAR(5)) PERSISTED, OtherData VARCHAR(10) NULL, CONSTRAINT Cars_PK PRIMARY KEY(ID), CONSTRAINT Cars_FK_Vehicles FOREIGN KEY(ID, [Type]) REFERENCES dbo.Vehicles(ID, [Type]) ); GO -- adding parent rows INSERT INTO dbo.Vehicles(ID, [Type]) VALUES(1, 'Car'), (2, 'Truck'); The following INSERT succeeds:

INSERT INTO dbo.Cars(ID, OtherData) VALUES(1, 'Some Data'); DELETE FROM dbo.Cars;

MERGE should be able to add the same row, but it fails:

MERGE dbo.Cars AS TargetTable USING ( SELECT 1 AS ID , 'Some Data' AS OtherData ) AS SourceData ON SourceData.ID = TargetTable.ID WHEN NOT MATCHED THEN INSERT (ID, OtherData) VALUES(SourceData.ID, SourceData.OtherData); Msg 547, LEVEL 16, State 0, Line 1 The MERGE statement conflicted WITH the FOREIGN KEY CONSTRAINT "Cars_FK_Vehicles" The conflict occurred IN DATABASE "Test" TABLE "dbo.Vehicles" The statement has been terminated.

False Positives

Martin Smith has posted this repro on stackoverflow, in response to my scenario described above. This script successfully adds an orphan row into dbo.Cars table:

ALTER TABLE dbo.Vehicles DROP CONSTRAINT Vehicles_CHK_ValidTypes ; GO INSERT INTO dbo.Vehicles(ID, [Type]) VALUES (3, ''); GO MERGE dbo.Cars AS TargetTable USING ( SELECT 3 AS ID , 'Some Data' AS OtherData ) AS SourceData ON SourceData.ID = TargetTable.ID WHEN NOT MATCHED THEN INSERT (ID, OtherData) VALUES(SourceData.ID, SourceData.OtherData); GO -- the FK constraint is enabled and trusted SELECT is_disabled,is_not_trusted FROM sys.foreign_keys WHERE name = 'Cars_FK_Vehicles'; -- yet we have an orphan row SELECT * FROM dbo.Cars SELECT * FROM dbo.Vehicles GO is_disabled is_not_trusted ----------- -------------- 0 0 (1 row(s) affected) ID TYPE OtherData ----------- ----- ---------- 3 Car SOME Data (1 row(s) affected) ID TYPE ----------- ----- 1 Car 2 Truck 3 Conclusion

In Ch 7 of my book "Defensive Database Programming", I wrote the following statement.

"As long as a constraint is trusted, then we know for sure that all our data complies with the rules that it dictates."

At the time I was writing that, I believed that was true. Unfortunately, as we have just seen, I was wrong - we can bypass trusted foreign key constraints in SQL 2008 R2.

P.S.

I have just learned that there is another scenario to bypass a foreign key. Google up this: "Parameterized DELETE and MERGE Allow Foreign Key Constraint Violations".

Another very important thing that I have just learned that this Connect item did not get urgent priority. Quoting directly from Connect: "Since this is not a common scenario or use case i.e., doing DDL changes and DML in the same transaction we don't see this as a big issue".

Reads involving UDFs under READ_COMMITTED_SNAPSHOT may seem inconsistent.

Alexander Kuznetsov — Wed, 03 Aug 2011 01:33:00 GMT

When a select uses scalar or multi-statement UDFs under READ_COMMITTED_SNAPSHOT, we might not get consistent results as of the time our select began - I will provide simple repro scripts.

At the time of this writing MSDN clearly states the following: "Read committed isolation using row versioning provides statement-level read consistency". However, if our select invokes scalar or multi-statement UDFs, this consistency is ensured per a statement in the body of the UDF, not per the whole statement that invokes the UDF(s).

Prerequisites

Before we run the repro scripts, we need the following setup:

ALTER DATABASE MyTest SET READ_COMMITTED_SNAPSHOT ON ; GO USE MyTest ; GO CREATE TABLE dbo.Messages ( MessageID INT NOT NULL CONSTRAINT PK_Messages PRIMARY KEY , MessageBody VARCHAR(50) NOT NULL ) ; GO INSERT INTO dbo.Messages ( MessageID, MessageBody ) VALUES ( 1, 'Hello world' ) ; GO

Getting inconsistent results from a multi-statement UDF

First, we need to create this UDF:

CREATE FUNCTION dbo.AllMessagesTwice ( ) RETURNS @ret TABLE ( MessageBody VARCHAR(50) NOT NULL , SelectedAt DATETIME2(5) NOT NULL ) AS BEGIN ; INSERT @ret ( MessageBody , SelectedAt ) SELECT MessageBody , GETDATE() FROM dbo.Messages ; DECLARE @d DATETIME2(5), @i INT ; SET @d = DATEADD(SECOND, 30, GETDATE()) ; WHILE GETDATE() < @d BEGIN ; SET @i = 1; END ; INSERT @ret ( MessageBody , SelectedAt ) SELECT MessageBody , GETDATE() FROM dbo.Messages ; RETURN ; END ; GO

Next, let us open a tab and cut and paste the following script, but not run it yet:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED ; SELECT * FROM dbo.AllMessagesTwice ( ) DELETE FROM dbo.Messages WHERE MessageId = 2 ;

In another tab, cut and paste the following script, but not run it either:

INSERT INTO dbo.Messages ( MessageID, MessageBody ) VALUES ( 2, 'Hi there' ) ;

Run the select script in the first tab - it takes 30 seconds to complete. Immediately return to the second tab and insert the 'Hi there' message.

When the select in the first tab completes, we shall clearly see the 'Hi there' message, which definitle was inserted after the select began, as well as the timestamp that shows when the row was selected, 30 seconds after the beginning of the statement:

Hello world 2011-08-02 20:56:44.70000 Hello world 2011-08-02 20:57:14.74000 Hi there 2011-08-02 20:57:14.74000

As we have seen, a multi-statement UDF can return data that was committed after the time when the select using the UDF began.

Getting inconsistent results from a scalar UDF

Here is the scalar UDF we shall be using:

CREATE FUNCTION dbo.MessageWithLastId ( ) RETURNS VARCHAR(50) AS BEGIN ; DECLARE @d DATETIME2(5) , @i INT ; SET @d = DATEADD(SECOND, 30, GETDATE()) ; WHILE GETDATE() < @d BEGIN ; SET @i = 1 ; END ; RETURN (SELECT TOP(1) MessageBody FROM dbo.Messages ORDER BY MessageID DESC) ; END ; GO

When we have created the scalar UDF, let us cut, paste, and run the following script:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED ; DELETE FROM dbo.Messages WHERE MessageId = 2 ; PRINT 'Data as of the beginning of the dbo.MessageWithLastId ( ) below' SELECT MessageBody FROM dbo.Messages ORDER BY MessageID ; PRINT 'Data returned by dbo.MessageWithLastId ( )' SELECT dbo.MessageWithLastId ( ) ; DELETE FROM dbo.Messages WHERE MessageId = 2 ;

In the second tab we need to immediately run the following, we have 30 seconds to do so:

INSERT INTO dbo.Messages ( MessageID, MessageBody ) VALUES ( 2, 'Hi there' ) ;

When the select in the first tab completes, we shall see that the scalar UDF returned the value that was not in the table when the select has started:

Data AS OF the beginning OF the dbo.MessageWithLastId ( ) below MessageBody -------------------------------------------------- Hello world (1 row(s) affected) Data returned BY dbo.MessageWithLastId ( ) -------------------------------------------------- Hi there

As we have seen, a scalar UDF can read data that was committed after the time when the select using the UDF began.

Snapshot isolation guarantees consistency

We can rerun both previous examples under snapshot isolation. All we need to do is add the following line immediately before the select that invokes our UDF:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT ;

In both cases we shall see that under snapshot isolation our selects return consistent data as of the time the select started.

Conclusion

As we have seen, if we are using scalar or multi-statement UDFs, and we need statement level consistency, we need to use snapshot isolation - READ_COMMITTED_SNAPSHOT may fail to provide the consistency we need.

Naturally, inline UDFs are esentially macroes - they are flattened into the select they are invoked from. As such, READ_COMMITTED_SNAPSHOT does provide statement level consistency as long as we are only using inline UDFs, and do not use scalar and multi-statement ones.

I would like to thank Erland Sommarskog, whose feedback inspired this post - I am posting this content with his permission.

Math with Months Is Not Commutative

Alexander Kuznetsov — Mon, 29 Nov 2010 22:58:00 GMT

In other words, if we add a month, then subtract a month, we might not get back to the date we started from. For example:

SELECT DATEADD(MONTH, 1, DATEADD(MONTH, -1, '20100330')) , DATEADD(MONTH, -1, DATEADD(MONTH, 1, '20100330')) 2010-03-28 00:00:00.000 2010-03-30 00:00:00.000

If our code assumes otherwise, it has a bug.

Additional links for my presentation on defensive database programming.

Alexander Kuznetsov — Sun, 19 Sep 2010 18:29:00 GMT

Yesterday I delivered a session named "Develop T-SQL defensively" at East Iowa SQL Saturday. Thanks to those who attended, and many thanks to Michelle Ufford, Chris Leonard, Ed Leighton-Dick, and other volunteers, who organized the event.

Here are the links with more information and more repro scripts, more or less in the order following the order of the presentation.

Basic Defensive Database Programming Techniques Summarizing previous posts about defensive database programming Happy programming!

Repro scripts and more info for my presentation on concurrency

Alexander Kuznetsov — Sun, 19 Sep 2010 18:10:00 GMT

Yesterday I delivered a session named "Developing T-SQL to survive concurrency" at East Iowa SQL Saturday. Thanks to everyone who attended, and thanks to Michelle Ufford, Chris Leonard, Ed Leighton-Dick, and other volunteers, who organized the event.

As promised, here are the links with more information and more repro scripts, more or less in the order following the order of the presentation.

Selects under READ COMMITTED and REPEATABLE READ may return incorrect results.

Reproducing deadlocks involving only one table

Retrying after deadlocks leads to lost updates

Patterns that do not work as expected.

Developing Modifications that Survive Concurrency

And, finally, Erland Sommarskog's article Arrays and lists in SQL

Speaking at SQL Saturday #50 in Iowa City, IA, and the recent disturbance in the Force.

Alexander Kuznetsov — Sun, 29 Aug 2010 15:29:00 GMT

In a few weeks I will be meeting a lot of old friends and new folks at SQL Saturday #50 in Iowa City. I'll do two presentations: on defensive database programming and on coding for high concurrency.

I really like this whole SQL Saturday thing. For me it is much better to allocate a Saturday for training and networking close to where I live, than to travel and not work for several business days. I would like to thank Steve Jones, Andy Warren, and Brian Knight who got this whole thing up and running.

Recently there was a disturbance in the Force. Quite a few people got really disappointed that Steve Jones was rejected for PASS board. Here is my take at it.

I really appreciate Steve's contributions to the community. I like, attend, and speak at SQL Saturdays. Also I regularly visit sqlserevercentral.com. To show how much I appreciate this great site, I will begin contributing to it - I will be regularly submitting articles to it. I hope they will be accepted and published. I don't have anything else to say on this issue.

My book is complete, many thanks to Hugo, Tony, and many others!

Alexander Kuznetsov — Fri, 18 Jun 2010 19:22:00 GMT

My book is finished.

A couple years ago I googled up "Defensive Database Programming", and came up with nothing. Naturally, I started filling up the void. Although the book is complete, I am planning to continue my research; I would appreciate any other defensive scenarios to discuss and describe.

Many thanks to my longtime friend Hugo Kornelis. I was very lucky that he agreed to review my book; he went very much beyond just reviewing it - his multiple excellent suggestions were invaluable.

Thanks to the brilliant editor Tony Davis, who patiently guided me all the way through.

Maybe we should do it again some time.

Also thanks to everyone who commented on my blog posts, helping me to crystallize and clarify the ideas, around which I eventually wrote my book.

Yet another example of defensive query optimization

Alexander Kuznetsov — Thu, 18 Feb 2010 20:11:00 GMT

My second take at the same problem I blogged about yesterday:

To optimize a query, we frequently have to explicitly tell optimizer some information which it does not realize by itself. Short term, this works, but long term we run the risk that what we are telling to the optimizer may be no longer relevant. In the previous post, Optimizing a query, then documenting assumptions in a unit test , we defined the problem and used a unit test to document an assumption. Read the previous post before proceeding.

In this post we shall eliminate the assumption altogether, and our code will be safer and easier to maintain. Instead of hardcoding the maximum duration of an event, we shall read it from the same table. To make sure that we read it efficiently, we shall use an index on a computed column:

ALTER TABLE dbo.Events ADD EventDurationInMinutes AS DATEDIFF(MINUTE,StartedAt, EndedAt) ; GO CREATE INDEX Events_EventDurationInMinutes ON dbo.Events(EventDurationInMinutes) ;

This index allows to retrieve maximum duration efficiently, as shown in the following procedure:

ALTER PROCEDURE dbo.SelectRunningEventsForTime @AsOfTime DATETIME AS BEGIN ; DECLARE @MaxEventDurationInMinutes INT , @EarliestPossibleStart DATETIME ; SET @MaxEventDurationInMinutes = ( SELECT MAX(EventDurationInMinutes) + 1 FROM dbo.Events ) ; SET @EarliestPossibleStart = DATEADD(MINUTE, -@MaxEventDurationInMinutes, @AsOfTime) ; SELECT EventID , StartedAt , EndedAt FROM dbo.Events WHERE @AsOfTime BETWEEN StartedAt AND EndedAt AND StartedAt >= @EarliestPossibleStart ; END ;

Let us run this procedure:

SET STATISTICS IO ON ; EXEC dbo.SelectRunningEventsForTime @AsOfTime = '20100223 01:38:08' ;

Selecting the maximum duration uses 3 reads, it is a simple index seek

Table 'Events'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(snip)

Reading the rows takes just a few more reads.

Table 'Events'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Conclusion

We have added an index on a computed column, and changed the stored procedure, so that it does not use any hardcoded constants. As such, the procedure is safer. Quite likely this improved stored procedure will require less maintenance.