September 14, 2010 at 11:57 am
Given the following schema:
CREATE TABLE [dbo].[Events]
(
[Id] [uniqueidentifier] NOT NULL,
[Version] [bigint] NOT NULL CHECK ([Version] > 0),
[CommitSequence] [bigint] IDENTITY(1,1) NOT NULL,
[Created] [datetime] NOT NULL DEFAULT (GETUTCDATE()),
[CommandId] [uniqueidentifier],
[Payload] [varbinary](MAX) NOT NULL,
CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED ([Id], [Version])
)
GO
CREATE TABLE dbo.EventQueue (
AggregateUid UNIQUEIDENTIFIER NOT NULL,
[Version] BIGINT NOT NULL,
Attempts TINYINT NOT NULL DEFAULT(0),
DequeuedAt DATETIME NULL,
CONSTRAINT [PK_EventQueue] PRIMARY KEY CLUSTERED (AggregateUid, [Version])
)
GO
CREATE TRIGGER dbo.PublishEventToQueue
ON dbo.[Events]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
INSERT INTO EventQueue (AggregateUid, [Version])
SELECT Id, [Version] FROM INSERTED
END
GO
I want get the "next" record from the EventQueue table in a multi-threaded envrionment and I don't want two queries to return the same result (dequeue the same record resulting in a race condition). Here's the query I'm using to "Dequeue":
DECLARE @agid UNIQUEIDENTIFIER, @version INT
UPDATE EQ SET DequeuedAt = GETUTCDATE(),
@agid = AggregateUid, @version = [Version]
FROM EventQueue EQ
WHERE EXISTS (
SELECT TOP 1 * FROM EventQueue Q
INNER JOIN Events E ON E.Id = Q.AggregateUid
WHERE Q.DequeuedAt IS NULL AND Q.AggregateUid = EQ.AggregateUid AND Q.[Version] = EQ.[Version]
ORDER BY Q.Attempts ASC, E.CommitSequence ASC
)
SELECT Payload, [Version] FROM [Events] WHERE Id = @agid AND [Version] = @version
What is the likelihood I'll run into race conditions and/or deadlocks and is there a better solution to avoid them?
September 14, 2010 at 1:22 pm
SQL Server's default transaction isolation level is READ COMMITTED, and I'd suggest leaving it as is. Also, run the update query through Database Tuning Advisor and see what it reccomends.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 14, 2010 at 2:32 pm
Thanks, after reading up on READ COMMITTED I ran into the table hints READPAST and UPDLOCK. Based on BOL it sounds like these hints were created for "work queue" scenarios which is exactly what I'm doing here. So I'll stick with things as I mentioned (with the addition of the proper indexes of course :-)) and add the table hints like this:
UPDATE EQ SET DequeuedAt = GETUTCDATE(),
@agid = AggregateUid, @version = [Version]
FROM EventQueue EQ
WHERE EXISTS (
SELECT TOP 1 * FROM EventQueue Q WITH(READPAST, UPDLOCK)
INNER JOIN Events E ON E.Id = Q.AggregateUid
WHERE Q.DequeuedAt IS NULL AND Q.AggregateUid = EQ.AggregateUid AND Q.[Version] = EQ.[Version]
ORDER BY Q.Attempts ASC, E.CommitSequence ASC
)
SELECT Payload, Version FROM [Events] WHERE Id = @agid AND [Version] = @version
Here's the link to BOL for those interested. Specifically under READPAST it says "READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table." : http://msdn.microsoft.com/en-us/library/ms187373.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply