Running Concurrency Tests

Starting concurrency tests simultaneously could be tricky, especially they need to start at the same time from different workstations.

One way to do it:

CREATE TABLE WhistleSound(i INT)
go
CREATE PROCEDURE WaitForWhistle
AS
DECLARE @cnt INT
SET @cnt = 0
WHILE (@cnt = 0) BEGIN
SELECT @cnt = COUNT(*) FROM WhistleSound WITH(NOLOCK)
IF @cnt = 0
WAITFOR DELAY '00:00:01'
END
go
CREATE PROCEDURE BlowWhistle
AS
INSERT WhistleSound(i) VALUES(1)
go

From one connection run

DELETE FROM WhistleSound
SELECT Getdate()
EXEC WaitForWhistle
SELECT Getdate()

From another connection run

SELECT Getdate()
EXEC WaitForWhistle
SELECT Getdate()

From third connection run

EXEC dbo.BlowWhistle

When Snapshot Isolation Helps and When It Hurts

My next article on devx.com:

You know SQL Server 2005's new snapshot isolation feature can help you reduce lock contention and deadlocks, but did you know that it also can help you fix intermittent errors in reports and detect lost updates? You know that snapshot isolation requires some effort from your DBA, but did you know it may introduce some breaking changes into applications? Its benefits are well known, but SQL Server developers need to understand the downstream ramifications of snapshot isolation. This article discusses a couple of common real-world scenarios that illustrate the often-unanticipated consequences of using this feature.

http://www.devx.com/dbzone/Article/32957