Earlier this week we encountered a web-site outage caused by a database procedure. I’m going to blog about that problem (in detail) in a post scheduled for next week. But before I publish that post, I want to know how subtle or obvious the problem was. It seems obvious to me now, but I have the benefit of hindsight. I wonder whether we could we have avoided this during the code review stage of development.
So before I publish the details, I invite you to do a code review of this procedure in the comment section.
Here’s the procedure. It suffers from the same thing that burned us this week. Do you see any issues with it? Tell me in the comment section.
CREATE PROCEDURE dbo.s_EVENT_TICKETS_GetOrCreate ( @EventId BIGINT, @VenueSeatId BIGINT, @PurchaserId BIGINT, @PurchaseMethodId BIGINT ) AS SET NOCOUNT ON; DECLARE @pid BIGINT; DECLARE @pmid BIGINT; DECLARE @dt DATETIME2 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN -- If the row exists, grab details about the purchaser SELECT @pid = PurchaserId, @pmid = PurchaseMethodId, @dt = PurchaseDate FROM dbo.EVENT_TICKETS WITH (UPDLOCK) WHERE EventId = @EventId AND VenueSeatId = @VenueSeatId; IF ( @pid IS NULL ) BEGIN -- The row doesn't exist, insert the row SET @dt = SYSDATETIME() INSERT INTO dbo.EVENT_TICKETS ( EventId, VenueSeatId, PurchaserId, PurchaseMethodId, PurchaseDate ) VALUES ( @EventId, @VenueSeatId, @PurchaserId, @PurchaseMethodId, @dt ); SELECT @pid = @PurchaserId, @pmid = @PurchaseMethodId; END COMMIT TRAN -- return details about the purchaser SELECT @pid as PurchaserId, @pmid as PurchaseMethodId, @dt as PurchaseDate;
Here’s a subset of the table definitions that this procedure is meant to use.
CREATE TABLE dbo.[EVENTS] ( EventId BIGINT NOT NULL IDENTITY, CONSTRAINT PK_EVENTS PRIMARY KEY (EventId) -- etc... ); CREATE TABLE dbo.VENUE_SEATS ( VenueSeatId BIGINT NOT NULL IDENTITY, CONSTRAINT PK_VENUE_SEATS PRIMARY KEY (VenueSeatId) -- etc... ); CREATE TABLE dbo.PURCHASERS ( PurchaserId BIGINT NOT NULL IDENTITY, CONSTRAINT PK_PURCHASERS PRIMARY KEY (PurchaserId) -- etc... ); CREATE TABLE dbo.PURCHASE_METHODS ( PurchaseMethodId BIGINT NOT NULL IDENTITY, CONSTRAINT PK_PURCHASE_METHODS PRIMARY KEY (PurchaseMethodId) -- etc... ); CREATE TABLE dbo.EVENT_TICKETS ( EventId BIGINT NOT NULL, VenueSeatId BIGINT NOT NULL, PurchaserId BIGINT NOT NULL, PurchaseMethodId BIGINT NOT NULL, PurchaseDate DATETIME2 NOT NULL, CONSTRAINT PK_EventId PRIMARY KEY CLUSTERED (EventId, VenueSeatId), CONSTRAINT FK_EVENT_TICKETS_EVENTS FOREIGN KEY (EventId) REFERENCES dbo.[EVENTS] (EventId), CONSTRAINT FK_EVENT_TICKETS_VENUE_SEATS FOREIGN KEY (VenueSeatId) REFERENCES dbo.VENUE_SEATS (VenueSeatId), CONSTRAINT FK_EVENT_TICKETS_PURCHASERS FOREIGN KEY (PurchaserId) REFERENCES dbo.PURCHASERS (PurchaserId), CONSTRAINT FK_EVENT_TICKETS_PURCHASE_METHODS FOREIGN KEY (PurchaseMethodId) REFERENCES dbo.PURCHASE_METHODS (PurchaseMethodId), ); GO