Search results matching tags 'sql server' and 'Defensive programming'

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

Your TRY block may fail, and your CATCH block may be bypassed.

Alexander Kuznetsov — Thu, 14 May 2009 01:11:00 GMT

Some T-SQL code is written under the assumption that either a TRY block successfully completes or a CATCH block is invoked. Most likely, this is the case. However, there is a third, although rare, possibility – the TRY block may fail, and the CATCH one is bypassed. Let me provide some examples. I do not intend to provide a comprehensive list of all such cases, I only want to demonstrate that sometimes CATCH blocks are bypassed. Also I would like to emphasize that in almost all the cases CATCH blocks do catch errors, and the exceptions, if any, are quite rare.

KILL command and timeout (aka attention) both stop execution without invoking CATCH blocks.

As a result of a KILL or an attention, the execution stops immediately. You can run the following script, cancel the query or kill it from another tab, and see for yourself:

SELECT @@SPID;

BEGIN TRY

PRINT 'Before WAITFOR';

WAITFOR DELAY '00:35:00';

PRINT 'After WAITFOR';

END TRY

BEGIN CATCH

SELECT 'Beginning CATCH block';

SELECT ERROR_NUMBER(), ERROR_MESSAGE();

END CATCH

PRINT 'At the end of the same batch';

PRINT 'Next batch';

You can also invoke it from ADO.Net, and you can also decrease CommandTimeout, so that the timeout occurs faster.

As you have seen, you can only catch such errors on the client.

Note: timeout and lock timeout are different. Lock timeout errors are caught by CATCH blocks.

Some compile errors cancel batch execution.

See for yourself:

BEGIN TRY

PRINT 'Beginning TRY';

BEGIN TRAN

INSERT data.SomeData(ID, AnotherID) VALUES(-1, -2);

-- make sure #t does not exists for your connection

DELETE FROM #t;

COMMIT;

PRINT 'Finishing TRY';

END TRY

BEGIN CATCH

SELECT 'Beginning CATCH block';

SELECT ERROR_NUMBER(), ERROR_MESSAGE();

ROLLBACK;

END CATCH

PRINT 'At the end of the same batch';

PRINT 'Next batch';

Beginning TRY

(1 row(s) affected)

Msg 208, Level 16, State 0, Line 6

Invalid object name '#t'.

Next batch

This problem is more likely to happen if you work with temporary tables. However, because at the time of this writing we have deferred name resolution, you can get this problem with a permanent object too. Surprisingly enough, if you wrap this batch in a stored procedure, and invoke your procedure from another TRY block, the second TRY block does catch the error. You can create the procedure and see for yourself:

CREATE PROCEDURE dbo.TestProc

BEGIN TRY

PRINT 'Beginning TRY';

DELETE FROM #t;

--INSERT data.SomeData(j) SELECT 1;

PRINT 'Finishing TRY';

END TRY

BEGIN CATCH

SELECT 'Beginning CATCH block';

SELECT ERROR_NUMBER(), ERROR_MESSAGE();

END CATCH

PRINT 'At the end of the same batch';

BEGIN TRY

PRINT 'Beginning TRY';

EXEC dbo.TestProc;

PRINT 'Finishing TRY';

END TRY

BEGIN CATCH

SELECT 'Beginning CATCH block';

SELECT ERROR_NUMBER(), ERROR_MESSAGE();

END CATCH

PRINT 'At the end of the same batch';

In my opinion it would be preferable not to start executing a batch if there are compilation errors, and in most cases exactly this is happening:

BEGIN TRY

PRINT 'Beginning TRY';

DECLARE @i INT;

INSERT data.SomeData(j) SELECT 1;

PRINT 'Finishing TRY';

END TRY

BEGIN CATCH

SELECT 'Beginning CATCH block';

SELECT ERROR_NUMBER(), ERROR_MESSAGE();

END CATCH

PRINT 'At the end of the same batch';

PRINT 'Next batch';

Msg 207, Level 16, State 1, Line 4

Invalid column name 'j'.

Next batch

If you are still reading this, also make sure you have read Erland Sommarskog’s articles on error handling:

http://www.sommarskog.se/error-handling-I.html

http://www.sommarskog.se/error-handling-II.html

and STRICT_CHECKS:

http://www.sommarskog.se/strict_checks.html

This post continues my series on defensive database programming. My next post:

Without ORDER BY, there is no default sort order.

Avoiding infinite loops. Part One.

Alexander Kuznetsov — Tue, 21 Apr 2009 03:11:00 GMT

Although there are many discussions about which kind of cursor or loop performs the best, there is no doubt which loops perform the worst – the infinite ones of course. Whenever you write a loop, you need to make sure that it never runs infinitely. I will provide two examples, two rather common scenarios that may potentially cause loops to run infinitely. In this post I will describe a common yet error-prone method of storing and traversing hierarchies. In the next one I will demonstrate how processing rows one by one can sometimes run infinitely.

Prerequisites

The following table allows to store hierarchies but does not prevent cycles.

CREATE TABLE data.Employee(

EmployeeID int NOT NULL,

ManagerID int NULL,

FirstName varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

LastName varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT PK_Employee_EmployeeID PRIMARY KEY CLUSTERED

(

EmployeeID ASC

)WITH (IGNORE_DUP_KEY = OFF)

)

SET ANSI_PADDING OFF

USE Test

ALTER TABLE data.Employee WITH CHECK ADD CONSTRAINT FK_Employee_Employee_ManagerID FOREIGN KEY(ManagerID)

REFERENCES data.Employee (EmployeeID)

The following short employee chart is perfectly correct:

INSERT INTO data.Employee

(EmployeeID

,ManagerID

,FirstName

,LastName)

SELECT 1, NULL, 'Dan', 'Chang' UNION ALL

SELECT 2, 1, 'June', 'Yang'

The following employee chart makes no sense at all - it contains a cycle. Yet is can be stored all right:

INSERT INTO data.Employee

(EmployeeID

,ManagerID

,FirstName

,LastName)

SELECT 3, 4, 'Sydney', 'Hobart' UNION ALL

SELECT 4, 3, 'Hobart', 'Sydney'

Retrieving such hierarchies may end up in an endless loop

Consider the following function:

CREATE FUNCTION Readers.GetManagerAndTeam (@EmployeeID INT)

RETURNS @ret TABLE

(

EmployeeID INT NOT NULL,

ManagerID INT NULL,

FirstName varchar(50),

LastName varchar(50),

Level INT NOT NULL

)

BEGIN

DECLARE @level INT, @rc INT;

INSERT INTO @ret(EmployeeID,

ManagerID,

FirstName,

LastName,

Level)

SELECT EmployeeID,

ManagerID,

FirstName,

LastName,

FROM data.Employee

WHERE EmployeeID=@EmployeeID;

SELECT @level=1, @rc=@@ROWCOUNT;

WHILE @rc>0 BEGIN

INSERT INTO @ret(EmployeeID,

ManagerID,

FirstName,

LastName,

Level)

SELECT e.EmployeeID,

e.ManagerID,

e.FirstName,

e.LastName,

@level+1

FROM data.Employee e JOIN @ret r ON e.ManagerID=r.EmployeeID

WHERE r.Level=@level;

SELECT @level=@level+1, @rc=@@ROWCOUNT;

END

RETURN;

END

This function correctly returns a valid employee chart:

SELECT EmployeeID, ManagerID, FirstName, LastName, Level

FROM Readers.GetManagerAndTeam(1)

But sometimes it runs infinitely. See for yourself:

SELECT EmployeeID, ManagerID, FirstName, LastName, Level

FROM Readers.GetManagerAndTeam(3)

Recursive function as a workaround

You can implement the same functionality with recursion, as follows

CREATE FUNCTION Readers.GetManagerAndTeam(@ManagerID INT)

RETURNS TABLE

RETURN(

WITH ManagerAndTeam AS(

SELECT EmployeeID, ManagerID, FirstName, LastName, CAST(1 AS INT) AS Level

FROM data.Employee

WHERE EmployeeID = @ManagerID

UNION ALL

SELECT e.EmployeeID, e.ManagerID, e.FirstName, e.LastName, m.Level + 1

FROM data.Employee e JOIN ManagerAndTeam m

ON m.EmployeeID = e.ManagerID)

SELECT EmployeeID, ManagerID, FirstName, LastName, Level

FROM ManagerAndTeam);

Under default settings this function will not run forever, it will stop after 100 iterations:

SELECT EmployeeID, ManagerID, FirstName, LastName, Level

FROM Readers.GetManagerAndTeam(3)

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

However, it still can run forever. See for yourself.

SELECT EmployeeID, ManagerID, FirstName, LastName, Level

FROM Readers.GetManagerAndTeam(3) OPTION (MAXRECURSION 0)

Fix the data!

You definitely can write a better UDF that will robustly handle dirty data. However, it is preferable to fix the data instead. The original UDF worked correctly under the assumption that there are no cycles in the hierarchy. Let us have the database enforce that. The following table does just that – you cannot store invalid employee charts in it:

CREATE TABLE data.Employee(

EmployeeID int NOT NULL,

ManagerID int NULL,

EmployeeLevel INT NOT NULL,

ManagerLevel INT NULL,

FirstName varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

LastName varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT PK_Employee_EmployeeID PRIMARY KEY CLUSTERED

(

EmployeeID ASC

)WITH (IGNORE_DUP_KEY = OFF),

CONSTRAINT UNQ_Employee_EmployeeID_EmployeeLevel UNIQUE

(

EmployeeID, EmployeeLevel

CONSTRAINT CHK_Employee_EmployeeLevel_ManagerLevel

CHECK(EmployeeLevel=ManagerLevel+1),

CONSTRAINT CHK_Employee_ManagerID_ManagerLevel

CHECK((ManagerID IS NULL AND ManagerLevel IS NULL)

OR (ManagerID IS NOT NULL AND ManagerLevel IS NOT NULL))

)

ALTER TABLE data.Employee WITH CHECK ADD CONSTRAINT FK_Employee_Employee_ManagerID FOREIGN KEY(ManagerID,ManagerLevel)

REFERENCES data.Employee (EmployeeID,EmployeeLevel)

Only valid data can be inserted into this table

INSERT INTO data.Employee

(EmployeeID

,ManagerID

,EmployeeLevel

,ManagerLevel

,FirstName

,LastName)

SELECT 1, NULL, 1, NULL, 'Dan', 'Chang' UNION ALL

SELECT 2, 1, 2, 1, 'June', 'Yang'

There are several other ways to store hierarchies. Discussing them is beyond the scope of this post.

Here is the second part: When you process your rows one by one, avoid infinite loops

Defensive database programming: fun with ROWCOUNT

Alexander Kuznetsov — Sun, 22 Mar 2009 02:59:00 GMT

I have written up two examples when a SET ROWCOUNT command breaks a seemingly working stored procedure or trigger. Note that currently the best practice is to use TOP clause instead of SET ROWCOUNT, which is

deprecated in SQL Server 2008.

However, even if you never use SET ROWCOUNT yourself, some legacy code can still use it – and it can change the behavior of your stored procedure or trigger, unless you are careful.

Prerequisites

Here are the table and sample data:

CREATE TABLE Data.Objects(ObjectID INT NOT NULL,

SizeInInches FLOAT,

WeightInPounds FLOAT

);

INSERT INTO Data.Objects(ObjectID,

SizeInInches,

WeightInPounds)

SELECT 1, 10, 10 UNION ALL

SELECT 2, 12, 12 UNION ALL

SELECT 3, 20, 22;

When SET ROWCOUNT breaks a stored procedure

Consider the following stored procedure:

CREATE PROCEDURE Readers.SelectObjectsBySizeRange

@SizeFrom FLOAT,

@SizeTo FLOAT

BEGIN

SELECT ObjectID,

SizeInInches,

WeightInPounds

FROM Data.Objects

WHERE SizeInInches BETWEEN @SizeFrom AND @SizeTo;

END;

Apparently the stored procedure works:

EXEC Readers.SelectObjectsBySizeRange

@SizeFrom = 10,

@SizeTo =14

ObjectID SizeInInches WeightInPounds

----------- ---------------------- ----------------------

1 10 10

2 12 12

(2 row(s) affected)

but a SET ROWCOUNT command issued before it is invoked breaks it. See for yourself:

SET ROWCOUNT 1;

EXEC Readers.SelectObjectsBySizeRange

@SizeFrom = 10,

@SizeTo =14

ObjectID SizeInInches WeightInPounds

----------- ---------------------- ----------------------

1 10 10

(1 row(s) affected)

The robust way is not to make unnecessary assumptions and to make sure that SET ROWCOUNT setting is cleared at the beginning of the stored procedure, as follows:

ALTER PROCEDURE Readers.SelectObjectsBySizeRange

@SizeFrom FLOAT,

@SizeTo FLOAT

BEGIN

SET ROWCOUNT 0;

SELECT ObjectID,

SizeInInches,

WeightInPounds

FROM Data.Objects

WHERE SizeInInches BETWEEN @SizeFrom AND @SizeTo;

END;

Rerun the query and see for yourself that now it works:

SET ROWCOUNT 1;

EXEC Readers.SelectObjectsBySizeRange

@SizeFrom = 10,

@SizeTo =14

ObjectID SizeInInches WeightInPounds

----------- ---------------------- ----------------------

1 10 10

2 12 12

(2 row(s) affected)

When SET ROWCOUNT breaks a trigger

Consider the following trigger that logs changes into another table:

CREATE TABLE Data.ObjectsChangeLog(ObjectID INT NOT NULL,

ChangedColumnName VARCHAR(20) NOT NULL,

ChangedAt DATETIME NOT NULL,

OldValue FLOAT NOT NULL

);

CREATE TRIGGER Objects_UpdTrigger

ON Data.Objects

FOR UPDATE

BEGIN

INSERT INTO Data.ObjectsChangeLog(ObjectID,

ChangedColumnName,

ChangedAt,

OldValue)

SELECT i.ObjectID, 'SizeInInches', CURRENT_TIMESTAMP, d.SizeInInches

FROM inserted i JOIN deleted d ON i.ObjectID = d.ObjectID

WHERE i.SizeInInches <> d.SizeInInches

UNION ALL

SELECT i.ObjectID, 'WeightInPounds', CURRENT_TIMESTAMP, d.WeightInPounds

FROM inserted i JOIN deleted d ON i.ObjectID = d.ObjectID

WHERE i.WeightInPounds <> d.WeightInPounds;

END

Apparently it works:

SET ROWCOUNT 0;

BEGIN TRAN

UPDATE Data.Objects

SET SizeInInches = 12,

WeightInPounds = 14

WHERE ObjectID = 1;

SELECT * FROM Data.ObjectsChangeLog;

ROLLBACK;

ObjectID ChangedColumnName ChangedAt OldValue

----------- -------------------- ----------------------- ----------------------

1 SizeInInches 2009-03-21 21:47:44.200 10

1 WeightInPounds 2009-03-21 21:47:44.200 10

However, SET ROWCOUNT breaks it. See for yourself:

DELETE FROM Data.ObjectsChangeLog;

SET ROWCOUNT 1;

BEGIN TRAN

UPDATE Data.Objects

SET SizeInInches = SizeInInches + 1,

WeightInPounds = WeightInPounds + 1

WHERE SizeInInches = 12;

ROLLBACK;

SET ROWCOUNT 0;

SELECT * FROM Data.ObjectsChangeLog;

ObjectID ChangedColumnName ChangedAt OldValue

----------- -------------------- ----------------------- ----------------------

2 SizeInInches 2009-03-21 21:49:02.200 12

(1 row(s) affected)

Although two columns were changed, only one change is logged, which is a bug. Again, the fix is to explicitly set ROWCOUNT to zero in your trigger, as follows:

ALTER TRIGGER Data.Objects_UpdTrigger

ON Data.Objects

FOR UPDATE

BEGIN

SET ROWCOUNT 0;

INSERT INTO Data.ObjectsChangeLog(ObjectID,

ChangedColumnName,

ChangedAt,

OldValue)

SELECT i.ObjectID, 'SizeInInches', CURRENT_TIMESTAMP, d.SizeInInches

FROM inserted i JOIN deleted d ON i.ObjectID = d.ObjectID

WHERE i.SizeInInches <> d.SizeInInches

UNION ALL

SELECT i.ObjectID, 'WeightInPounds', CURRENT_TIMESTAMP, d.WeightInPounds

FROM inserted i JOIN deleted d ON i.ObjectID = d.ObjectID

WHERE i.WeightInPounds <> d.WeightInPounds;

END

Rerun the update again and see for yourself that both changes were captured. As you have seen, explicitly adding SET ROWCOUNT 0 at the beginning of your Transact-SQL modules increases their robustness.

This post continues my series on defensive database programming. Here is my next post:

Defensive database programming: fun with triggers.

Here are my previous posts from the series:

Summarizing previous posts about defensive database programming

Defensive database programming: SET vs. SELECT.

Stress testing UPSERTs

Defensive database programming: fun with UPDATE.

Defensive database programming: eliminating IF statements.

Defensive database programming: fun with changing column widths.

Avoid mixing old and new styles of error handling.

Defensive database programming: adding ESCAPE clauses.

Defensive database programming: qualifying column names.

Defensive database programming: rewriting queries with NOT IN().

Summarizing previous posts about defensive database programming

Alexander Kuznetsov — Mon, 09 Mar 2009 01:33:00 GMT

I have been posting examples of defensive database programming for some time now. I am by no means done with this topic, there is much more to it. Yet this time I would like to skip concrete examples and write up a long overdue introduction.

Defensive programming is an approach that is designed to ensure high quality of software by eliminating existing bugs and avoiding potential ones. The goal of defensive programming to make sure that software robustly and gracefully handles cases of unintended use. Programming defensively includes the following techniques:

· Explicitly list the assumptions that you made during development
· Whenever feasible, remove assumptions that are not essential
· Ensure that the essential assumptions always hold
· Use all your imagination to come up with cases of unintended use, trying to break your module. Incorporate these cases into your testing suite.
· Lay out your code in short, fully testable, and fully tested modules
· Reuse your code whenever feasible

Although at the time of this writing apparently the most popular example of defensive database programming is the prevention of SQL injection attacks, there is much, much more to it. Also unit testing is another very powerful approach in ensuring high quality of software. Defensive programming and unit testing are very closely intertwined and are frequently used together; this is why there are several references to unit testing in this post.

If you drive defensively, it frequently means driving slowly. You may need more time to get to your destination, but you are less likely to get into an accident. Similarly, if you program defensively, it frequently means delivering slower becasue of all the precautions you are taking. Whether you need to program defensively depends on your situation. I am not saying that the techniques which I demonstrate need to be used in all the cases.

Identifying your hidden or implicit assumptions.

This is extremely important. This is never easy. I have already posted several examples, such as:

Rewriting queries with NOT IN():

Defensive database programming: rewriting queries with NOT IN().

A correlated subquery with an unqualified column with an implicit assumption that a table does not have a column:

Defensive database programming: qualifying column names.

One very commonly used but rarely recognized assumption is the absence of concurrency. There are quite a lot of stored procedures that work perfectly well when invoked only from one connection, yet intermittently fail in high concurrency environments. The post on IF ELSE logic demonstrates that.

Removing assumptions that are not essential.

Examples of this technique are the same as in the previous section.

Ensuring that essential assumptions always hold.

Whenever possible, separate such checks from the code that actually runs on the server, so that they do not slow down the execution. For instance, you can use unit tests to make sure that some condition is true. I provided an example in my previous post on defensive database programming: http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/25/defensive-database-programming-set-vs-select.aspx

However, in some cases you do need to make sure that your assumption holds at execution time. For example, if your stored procedure only works correctly if there is no concurrent execution of the same procedure from other connections, you can only guarantee that at execution time, as Tony Rogerson demonstrated in his post “Assisting Concurrency by creating your own Locks (Mutexs in SQL)”:

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

Coming up with examples of unintended use.

Try to come up with cases that could break your module right before you have started developing it. This is a very good time to do so, because you might come up with better. For example, if you are submitting a comma-separated list of zip codes (on 2005 or earlier), and expect to get back a list of customers for those zip codes, make sure to submit imperfect lists such as “,12345,12346”, “12345,,12346”, “12345,12346,,”, and so on. Try to break your module again right when you have just developed it, because the implementation details are still fresh in your mind. For instance, if you used a query with LIKE clause in your implementation, you may later realize that you have not expected patterns such as “[OT]%”. This is demonstrated in the following post:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/11/08/defensive-database-programming-adding-escape-clauses.aspx

Also ask somebody else to break your module – that might yield more results.

Conclusion

As you have seen, defensive database programming includes several different techniques applied on case-by-case basis. I still have a lot of examples to share, but I felt that it is necessary to summarize what I have written so far. That done, I can get back to writing up examples, one at a time.

This post continues my series on defensive database programming.Here is my next post:

Defensive database programming: fun with ROWCOUNT

Here are the previous posts from the series:

Defensive database programming: SET vs. SELECT.

Stress testing UPSERTs

Defensive database programming: fun with UPDATE.

Defensive database programming: eliminating IF statements.

Defensive database programming: fun with changing column widths.

Avoid mixing old and new styles of error handling.

Defensive database programming: adding ESCAPE clauses.

Defensive database programming: qualifying column names.

Defensive database programming: rewriting queries with NOT IN().

Defensive database programming: SET vs. SELECT.

Alexander Kuznetsov — Sun, 25 Jan 2009 18:40:00 GMT

Comparing SET vs. SELECT is a very popular topic, and much of what I have to say has been said before. Assigning multiple values via SELECT performs better, and you don’t have to repeat your code several times, as described by Tony Rogerson here and here. However, if you have ambiguities, SET will raise an error, but SELECT will not detect them, unless you use a smart trick described by Adam Machanic. Suppose, however, that you have a subquery that cannot have ambiguities at the time when you are developing it. For example, consider the following table and script:

USE Test

DROP TABLE data.Customers;

/****** Object: Table data.Customers Script Date: 01/25/2009 11:00:31 ******/

CREATE TABLE Data.Customers(

CustomerId int NOT NULL,

FirstName varchar(50) NOT NULL,

LastName varchar(50) NOT NULL,

PhoneNumber varchar(50) NOT NULL,

CONSTRAINT PK_Customers PRIMARY KEY(CustomerId),

CONSTRAINT UNQ_Customers UNIQUE(PhoneNumber)

)

INSERT INTO data.Customers(

CustomerId,

FirstName,

LastName,

PhoneNumber)

SELECT 1, 'Darrel', 'Ling', '(123)456-7890' UNION ALL

SELECT 2, 'Peter', 'Hansen', '(234)123-4567';

DECLARE @FirstName varchar(50),

@LastName varchar(50),

@PhoneNumber varchar(50);

SET @PhoneNumber = '(123)456-7890';

-- You can assign one variable at a time using SET:

SET @LastName = (SELECT LastName FROM Data.Customers WHERE PhoneNumber = @PhoneNumber);

SET @FirstName = (SELECT FirstName FROM Data.Customers WHERE PhoneNumber = @PhoneNumber);

-- But assigning both variables in one SELECT is faster

-- and you don't duplicate your code:

SELECT @LastName = LastName,

@FirstName = FirstName

FROM Data.Customers

WHERE PhoneNumber = @PhoneNumber;

At the time this code is being developed, ambiguities are impossible – the uniqueness of condition WHERE PhoneNumber = @PhoneNumber is guaranteed by the UNIQUE constraint UNQ_Customers. In this case assigning both variables via a single SELECT is simpler and performs better; there is no reason to use SETs. If later on the uniqueness is no longer guaranteed, the code will no longer be safe, but right now there is no need to complicate your query just in case. However, if you decide to use a single SELECT, you actually made an assumption – you assumed that the condition WHERE PhoneNumber = @PhoneNumber guarantees uniqueness. As long as this assumption is true, your code is perfectly safe to use. Yet you need to document your assumption – a unit test is a very good way to document it. You can run the following two queries and make sure that they both return 1:

-- there is a UNIQUE or PK constraint on PhoneNumber

SELECT COUNT(*) FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u

WHERE u.TABLE_NAME='Customers'

AND u.TABLE_SCHEMA='Data'

AND u.CONSTRAINT_NAME='UNQ_Customers'

AND u.COLUMN_NAME='PhoneNumber'

AND (SELECT CONSTRAINT_TYPE

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE TABLE_NAME='Customers'

AND TABLE_SCHEMA='Data'

AND CONSTRAINT_NAME='UNQ_Customers') IN ('PRIMARY KEY', 'UNIQUE');

-- This constraint does not include other columns

SELECT COUNT(*) FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

WHERE TABLE_NAME='Customers'

AND TABLE_SCHEMA='Data'

AND CONSTRAINT_NAME='UNQ_Customers';

Suppose that at some time later on the database schema has changed, as follows:

ALTER TABLE Data.Customers ADD CountryCode VARCHAR(5) NOT NULL

CONSTRAINT DF_Customers_CountryCode DEFAULT('US');

ALTER TABLE Data.Customers DROP CONSTRAINT UNQ_Customers;

ALTER TABLE Data.Customers ADD CONSTRAINT UNQ_Customers UNIQUE(PhoneNumber, CountryCode);

After this change phone number alone no longer uniquely identifies a customer, and your unit test will fail. The failed unit test will alert you to adjust your code to fit the changed database schema. For instance, you can add a condition on CountryCode to ensure uniqueness, as follows:

-- add some ambiguity: a person from another country

-- with the same phone number

INSERT INTO data.Customers(

CustomerId,

FirstName,

LastName,

PhoneNumber,

CountryCode)

SELECT 3, 'Drew', 'Magnusson', '(123)456-7890', 'UK';

DECLARE @FirstName varchar(50),

@LastName varchar(50),

@PhoneNumber varchar(50),

@CountryCode VARCHAR(5);

SELECT @PhoneNumber = '(123)456-7890', @CountryCode = 'US';

SELECT @LastName = LastName,

@FirstName = FirstName

FROM Data.Customers

WHERE PhoneNumber = @PhoneNumber

AND CountryCode = @CountryCode;

Alternatively, you can use a trick described by Adam Machanic, so that you get an exception whenever there is an ambiguity.

This post continues my series on defensive database programming. My next post:

Summarizing previous posts about defensive database programming

Here are the previous posts from the series:

Stress testing UPSERTs

Defensive database programming: fun with UPDATE.

Defensive database programming: eliminating IF statements.

Defensive database programming: fun with changing column widths.

Avoid mixing old and new styles of error handling.

Defensive database programming: adding ESCAPE clauses.

Defensive database programming: qualifying column names.

Defensive database programming: rewriting queries with NOT IN().

Stress testing UPSERTs

Alexander Kuznetsov — Mon, 15 Dec 2008 01:30:00 GMT

Suppose that you need to implement the following logic:

IF(row exists)

Update the row

ELSE

Insert a new row.

If you already are on 2008, you should use MERGE command, and you don’t need to read this post. Prior to 2008 this logic has to be implemented using UPDATE and INSERT commands. I will stress test several UPSERT implementations in a high concurrency environment. I will demonstrate that under most circumstances the best approach is to serialize your modifications.

Prerequisites

The following tables are used in this post:

CREATE TABLE [dbo].[TwoINTs](

[ID] [int] NOT NULL,

[i1] [int] NOT NULL,

[i2] [int] NOT NULL,

[i3] [int] NOT NULL

);

CREATE TABLE [dbo].[TwoINTsErrorLog](

[ID] [int] NOT NULL,

[i1] [int] NOT NULL,

[i2] [int] NOT NULL,

[error] [int] NOT NULL

);

Implementing optimistic approach

Optimistic approach means that the procedure does not prevent any race conditions up front – it handles errors caused by concurrency as they occur. The following procedure implements optimistic approach and logs all the errors:

CREATE PROCEDURE dbo.SaveTwoINTs(@ID INT, @i1 INT, @i2 INT)

BEGIN

SET NOCOUNT ON;

SET XACT_ABORT OFF;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @ret INT;

SET @ret=0;

BEGIN TRAN;

INSERT INTO dbo.TwoINTs(ID, i1, i2, i3)VALUES(@ID, @i1, @i2, @i1);

SET @ret=@@ERROR;

IF @ret=2601 BEGIN

INSERT INTO [dbo].[TwoINTsErrorLog](ID, i1, i2, error)VALUES(@ID, @i1, @i2, @ret);

UPDATE dbo.TwoINTs SET i1=i1+@i1, i2=i2+@i2 WHERE ID=@ID;

SET @ret = @@ERROR;

IF @ret<>0 BEGIN

INSERT INTO [dbo].[TwoINTsErrorLog](ID, i1, i2, error)VALUES(@ID, @i1, @i2, @ret);

END

END ELSE BEGIN

IF @ret<>0 BEGIN

INSERT INTO [dbo].[TwoINTsErrorLog](ID, i1, i2, error)VALUES(@ID, @i1, @i2, @ret);

END

END;

COMMIT;

RETURN @ret;

END

Stress testing optimistic approach

The following two procedures upsert 50K values each, the first one into column i1, and the second one into column i2:

CREATE PROCEDURE Testers.UpsertLoop1

BEGIN

DECLARE @ID INT, @i1 INT, @i2 INT, @count INT, @ret INT;

SET @count = 0;

WHILE @count<50000 BEGIN

SELECT @ID = COALESCE(MAX(ID),0) + 1 FROM dbo.TwoInts;

EXEC @ret=dbo.SaveTwoINTs @ID, 1, 0;

SET @count = @count + 1;

END;

CREATE PROCEDURE Testers.UpsertLoop2

BEGIN

DECLARE @ID INT, @i1 INT, @i2 INT, @count INT, @ret INT;

SET @count = 0;

WHILE @count<50000 BEGIN

SELECT @ID = COALESCE(MAX(ID),0) + 1 FROM dbo.TwoInts;

EXEC @ret=dbo.SaveTwoINTs @ID, 0, 1;

SET @count = @count + 1;

END;

When I simultaneously ran these two test procedures from two connections, all values were saved, and in more than 90% cases a row was actually updated from both connections:

SELECT SUM(i1), SUM(i2) FROM [dbo].[TwoINTs];

SELECT SUM(i1), SUM(i2) FROM [dbo].[TwoINTs] WHERE i1+i2=2;

----------- -----------

50000 50000

----------- -----------

46030 46030

As you have seen, all values were saved. Was it because the procedure actually works in high concurrency environments, or was it just because the stress test harness was too weak to expose problems?

Does this test harness actually work?

Whenever you do stress testing, you need to demonstrate that your harness actually works. You need to come up with a procedure which work without concurrency, and have your harness expose problems in it. Here is a faulty implementation:

ALTER PROCEDURE dbo.SaveTwoINTs(@ID INT, @i1 INT, @i2 INT)

BEGIN

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @ret INT;

SET @ret=0;

BEGIN TRAN;

UPDATE dbo.TwoINTs SET i1=i1+@i1, i2=i2+@i2 WHERE ID=@ID;

IF @@ROWCOUNT=0 BEGIN

INSERT INTO dbo.TwoINTs(ID, i1, i2, i3)VALUES(@ID, @i1, @i2, @i1);

SET @ret = @@ERROR;

IF @ret<>0 BEGIN

INSERT INTO [dbo].[TwoINTsErrorLog](ID, i1, i2, error)VALUES(@ID, @i1, @i2, @ret)

END

END;

COMMIT;

RETURN @ret;

END

Set up a fresh start before you rerun the loop tests:

TRUNCATE TABLE [dbo].[TwoINTsErrorLog];

TRUNCATE TABLE [dbo].[TwoINTs];

Rerun the loop tests. Clearly the faulty procedure does not work under high concurrency:

SELECT SUM(i1), SUM(i2) FROM [dbo].[TwoINTs];

----------- -----------

46433 39017

If it worked, I would get 50000 in both cases.

Implementing and stress testing pessimistic approach.

Pessimistic approach means that you acquire some kind of exclusive lock before you start modifications and hold it until you commit – once the lock is acquired, this eliminates all concurrency. Let us reuse the technique published by SQL Server MVP Tony Rogerson in his blog:

here

ALTER PROCEDURE dbo.SaveTwoINTs(@ID INT, @i1 INT, @i2 INT)

BEGIN

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @ret INT;

SET @ret=0;

BEGIN TRAN;

DECLARE @result INT, @iteration INT, @resourceName VARCHAR(50);

SELECT @result = -1, @iteration = 0, @resourceName = 'dbo.TwoINTs '+CAST(@ID AS VARCHAR(10));

WHILE @result NOT IN ( 0, 1 ) BEGIN -- Only successful return codes

EXEC @result = sp_getapplock @Resource = @resourceName, @LockMode = 'Exclusive';

SET @iteration = @iteration +1;

IF @iteration>1000 BEGIN

RAISERROR ( 'Lock failed to acquire.', 16, 1 );

ROLLBACK;

RETURN -1;

END;

UPDATE dbo.TwoINTs SET i1=i1+@i1, i2=i2+@i2 WHERE ID=@ID;

IF @@ROWCOUNT=0 BEGIN

INSERT INTO dbo.TwoINTs(ID, i1, i2, i3)VALUES(@ID, @i1, @i2, @i1);

SET @ret = @@ERROR;

IF @ret<>0 BEGIN

INSERT INTO [dbo].[TwoINTsErrorLog](ID, i1, i2, error)VALUES(@ID, @i1, @i2, @ret)

END

END;

COMMIT;

RETURN @ret;

END

If you stress test this procedure in the same way as before, you will see that no saves are lost and no errors are raised.

Performance considerations.

Under high concurrency, the pessimistic approach repeatedly runs noticeably faster. Typical costs on my laptop are as follows:

Pessimistic: CPU – 12600, duration - 35500

Optimistic: CPU – 16400, duration – 50800

Without any concurrency, the optimistic approach repeatedly runs slightly faster. Typical costs on my laptop are as follows:

Pessimistic: CPU – 12200, duration - 29500

Optimistic: CPU – 10900, duration – 26800

Note that for this benchmarking I commented out logging errors into a table, which made very little difference.

Isolation level considerations

As usual in such cases, if you up isolation level to REPEATABLE READ or to SERILIZABLE, you will get a lot of deadlocks.

This post continues my series on defensive database programming.The next post is:

Defensive database programming: SET vs. SELECT.

Here are the previous posts from the series:

Defensive database programming: fun with UPDATE. Defensive database programming: eliminating IF statements. Defensive database programming: fun with changing column widths. Avoid mixing old and new styles of error handling. Defensive database programming: adding ESCAPE clauses. Defensive database programming: qualifying column names. Defensive database programming: rewriting queries with NOT IN().

Defensive database programming: fun with UPDATE.

Alexander Kuznetsov — Mon, 08 Dec 2008 15:56:00 GMT

It is well known that UPDATE ... FROM command does not detect ambiguities. Also it well known that ANSI standard UPDATE may perform very poorly and may be difficult to maintain, because it does not adhere to the fundamental DRY (Don’t Repeat Yourself) principle. I will tweak UPDATE ... FROM in three different ways so that you can detect or handle ambiguities without having to repeat one and the same subquery over and over again.

Prerequisites

The following tables and sample data are used in this post:

CREATE Schema Data AUTHORIZATION dbo;

CREATE TABLE Data.Prices(ID INT NOT NULL, Price FLOAT NOT NULL);

CREATE TABLE Data.PricesStaging(ID INT NOT NULL, Price FLOAT NOT NULL);

INSERT INTO Data.Prices(ID, Price)

SELECT 1, 10 UNION ALL SELECT 2, 10 UNION ALL SELECT 3, 10;

INSERT INTO Data.PricesStaging(ID, Price)

SELECT 1, 14 UNION ALL SELECT 1, 15 UNION ALL SELECT 2, 16;

SELECT 1 AS Number INTO Data.Numbers

UNION ALL SELECT 2;

Problems with ANSI standard updates

Consider the following ANSI standard update:

UPDATE Data.Prices

SET Price = (SELECT s.Price FROM Data.PricesStaging s WHERE Data.Prices.ID=s.ID)

WHERE EXISTS(SELECT * FROM Data.PricesStaging s WHERE Data.Prices.ID=s.ID);

In order to comply with ANSI standard, I had to repeat my subquery. Clearly this is poor programming practice. On top of that, this subquery is also executes twice, which drags performance. However, this update detected an ambiguity and errored out, which is usually very good:

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

Updating a subquery does not detect ambiguities

Because ANSI standard UPDATE is very inconvenient to work with, RDBMS vendors provide proprietary extensions which are more convenient. Transact-SQL provides several ways to write an UPDATE without having to repeat yourself. However, these extensions do not detect ambiguities the way ANSI standard UPDATEs do. For instance, the following update silently succeeds:

-- erase previous changes

UPDATE Data.Prices SET Price=10;

WITH p AS(SELECT p.ID, p.Price, s.Price AS NewPrice

FROM Data.Prices p JOIN Data.PricesStaging s ON p.ID=s.ID)

UPDATE p SET Price=NewPrice;

It is very tempting to use such UPDATE commands, because they are more maintainable. However, in many cases you have to detect ambiguities. There are several ways to accomplish that, I will demonstrate some in the next three sections.

However, I need to mention that SQL Server MVP Hugo Kornelis suggests that UPDATE .. FROM syntax is hopeless and as such

should be deprecated altogether

Hugo recommends using MERGE statement instead, and that is definitely a good option if you are already on 2008. Yet many systems out there are still on 2005, and there are a few approaches to get you going before you can upgrade to 2008.

Mimicking ANSI standard UPDATE’s behavior.

The following UPDATE detects an ambiguity and fails:

DECLARE @ambiguityDetector INT;

-- erase previous changes

UPDATE Data.Prices SET Price=10;

WITH p AS(SELECT p.ID, p.Price, s.Price AS NewPrice,

COUNT(*)OVER(PARTITION BY p.ID) AS NumVersions

FROM Data.Prices p JOIN Data.PricesStaging s ON p.ID=s.ID)

UPDATE p SET Price=NewPrice,

@ambiguityDetector = (SELECT Number FROM Data.Numbers WHERE Number<=NumVersions);

SELECT ID, Price FROM Data.Prices;

But this UPDATE succeeds if there are no ambiguities.

Accepting unambiguous changes, silently ignoring ambiguities.

The following UPDATE modifies the row with ID=2 because there is no ambiguity, and it does not modify the row with ID=1:

UPDATE Data.Prices SET Price=10;

WITH p AS(SELECT p.ID, p.Price, s.Price AS NewPrice,

COUNT(*)OVER(PARTITION BY p.ID) AS NumVersions

FROM Data.Prices p JOIN Data.PricesStaging s ON p.ID=s.ID)

UPDATE p SET Price=NewPrice

WHERE NumVersions = 1;

SELECT ID, Price FROM Data.Prices;

ID Price

----------- ----------------------

1 10

2 15

3 10

Detecting an ambiguity so that you can handle it yourself

The following code detects an ambiguity without raising an error - that allows you to provide your own error handling:

DECLARE @expectedRowcount INT, @actualRowcount INT;

UPDATE Data.Prices SET Price=10;

WITH p AS(SELECT p.ID, p.Price, s.Price AS NewPrice,

COUNT(*)OVER() AS NumUpdates

FROM Data.Prices p JOIN Data.PricesStaging s ON p.ID=s.ID)

UPDATE p SET Price=NewPrice,

@expectedRowcount = NumUpdates;

SELECT @actualRowcount = @@ROWCOUNT;

IF (@actualRowcount <> @expectedRowcount) BEGIN

-- do your error handling here

PRINT 'Ambiguity detected';

END;

This post continues my series on defensive database programming.

The next post is:

Stress testing UPSERTs

Here are the previous posts from the series:

Defensive database programming: eliminating IF statements. Defensive database programming: fun with changing column widths. Avoid mixing old and new styles of error handling. Defensive database programming: adding ESCAPE clauses. Defensive database programming: qualifying column names. Defensive database programming: rewriting queries with NOT IN().

Defensive database programming: eliminating IF statements.

Alexander Kuznetsov — Thu, 27 Nov 2008 23:27:00 GMT

The following pattern is quite common in database programming:

IF EXISTS(some query) BEGIN

DO SOMETHING;

END

When such code runs in high concurrency situations, it may not work as expected. I will provide a repro when such logic fails 40% of the time. The following script provides a test table and attempts to implement optimistic locking using rowversion columns:

CREATE TABLE dbo.TwoINTs(ID INT NOT NULL, i1 INT NOT NULL, i2 INT NOT NULL, version ROWVERSION)

SET NOCOUNT ON;

DECLARE @i INT;

SET @i=0;

WHILE @i<1000000 BEGIN

INSERT INTO dbo.TwoINTs(ID, i1, i2) VALUES(@i,0,0);

SET @i = @i+1;

END

CREATE UNIQUE INDEX UNQ_TwoInts_ID ON dbo.TwoInts(ID);

CREATE PROCEDURE dbo.UpdateTwoINTs(@ID INT, @i1 INT, @i2 INT, @version ROWVERSION)

BEGIN

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @ret INT;

BEGIN TRANSACTION

IF EXISTS(SELECT 1 FROM dbo.TwoINTs WHERE ID=@ID AND version = @version) BEGIN

UPDATE dbo.TwoINTs SET i1=@i1, i2=@i2 WHERE ID=@ID AND version = @version;

SET @ret=0;

END ELSE BEGIN

SET @ret=1;

END;

COMMIT

RETURN @ret;

END

A naïve test not involving high concurrency succeeds all right. You can run a naïve test yourself. In one tab, run the following code, which mimics a user who read a row and modified column i1 in 10 seconds:

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

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

WAITFOR DELAY '00:00:10'

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

SELECT @ret AS ret, i1, i2 FROM dbo.TwoInts WHERE ID=5;

Immediately start another script, which mimics a user who modified another column, i2, also after 10 seconds of delay:

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

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

WAITFOR DELAY '00:00:10'

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

SELECT @ret AS ret, i1, i2 FROM dbo.TwoInts WHERE ID=5;

Note that only one of two updates succeeded, which is expected – the procedure detected a rowversion mismatch and did not overwrite the first update. However, a more realistic test on my laptop fails 40% of the time. First of all, some cleanup:

UPDATE dbo.TwoINTs SET i1=0, i2=0 WHERE ID=5;

Here is the script to run in one tab:

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

SET @count = 0;

WHILE @count<100000 BEGIN

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

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

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

END;

Here is the script to run in another tab:

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

SET @count = 0;

WHILE @count<100000 BEGIN

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

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

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

END;

If no updates were lost, I would expect the values in both i1 and i2 to be 100000. Unfortunately, this is not the case:

SELECT i1, i2 FROM dbo.TwoINTs WHERE ID=5

57871 54473

As you have seen, more than 40% updates were lost. There are several ways to fix the problem. Of course, you can just up the isolation level to REPEATABLE READ or even to SERIALIZABLE. Unfortunately, under higher isolation levels code like this is very prone to deadlocks. You can alter the stored procedure, rerun the loops, and see for yourself. There is a much simpler and more robust way to implement optimistic locking. Let me clean up the data first and improve the procedure:

UPDATE dbo.TwoINTs SET i1=0, i2=0 WHERE ID=5;

ALTER PROCEDURE dbo.UpdateTwoINTs(@ID INT, @i1 INT, @i2 INT, @version ROWVERSION)

BEGIN

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @ret INT;

UPDATE dbo.TwoINTs SET i1=@i1, i2=@i2 WHERE ID=@ID AND version = @version;

SET @ret= 1 - @@ROWCOUNT;

RETURN @ret;

END

When you rerun those two scripts with loops from 1 to 100000 again, you can see that this time not a single update was lost:

SELECT i1, i2 FROM dbo.TwoINTs WHERE ID=5

100000 100000

Note that you did not get any deadlocks at all.

As you have seen, the following pattern is very prone to errors in high concurrency OLTP environments:

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

DO Something;

END ELSE BEGIN

DO Something else;

END;

If you use this pattern in your procedures and expect to run them in high concurrency OLTP environments, you need to properly stress test your procedures. While it is OK to just run scripts from SSMS tabs to demonstrate the problem, as I did in this post, I think that in real life there are more convenient ways to stress test. For instance, I was describing a C# test harness

here.

This post continues my series on defensive database programming.

The next post is:

Defensive database programming: fun with UPDATE.

Here are the previous posts from the series:

Defensive database programming: fun with changing column widths. Avoid mixing old and new styles of error handling. Defensive database programming: adding ESCAPE clauses. Defensive database programming: qualifying column names. Defensive database programming: rewriting queries with NOT IN().