I recently wrote a high-volume, fast paced application which supports many threads reaching into a work queue (SQL table) and retrieving a row representing a piece of work.\u00a0 The table uses an identity column as its primary key.<\/p>\n
In other work experiences, I have seen SQL locking hints used to prevent two threads from grabbing the same piece of work.\u00a0 Mostly, the locking hints are READPAST, and some form of ROWLOCK, PAGELOCK or TABLELOCK.\u00a0 In high volume environments, the locking hints have periodically failed, and occasionally the same piece of work goes to different threads.<\/p>\n
I decided to try a different approach which isolates the allocation away from SQL internal locking\u00a0 mechanisms, although the locking mechanisms can be used.\u00a0 In my table, the work is marked as assigned, and later is marked as either failed\/reassign or successful, or can timeout and become available for reassignment.\u00a0 In other words, the row survives the work completion: it is not deleted upon retrieval.<\/p>\n
The technique uses a column in the work queue table called Process_Marker, which is a nullable uniqueidentifier.\u00a0 When a piece of work is injected, this column is left null.\u00a0 When the worker calls the stored procedure to retrieve a piece of available work, the stored procedure creates a GUID using NEWID() in a static variable, and attempts to update the Process_Marker column in an available row (i.e. Process Marker is null) with that GUID.\u00a0 As its final action, the stored procedure selects all the rows with that particular GUID, and updates any necessary admin columns in those rows.\u00a0 In the UPDATE statement, the READPAST locking hint is used.<\/p>\n
The identity column is used by the workers to report\/update results, in case the workers are capable of requesting multiple work pieces in a single call to the stored procedure. The worker just needs to set the Last_Attempt_End_Date to the current system time, to prevent the work from being reissued\/retried.<\/p>\n
I haven’t done full load testing on it yet, but the results look promising.<\/p>\n
Here is the code to create the work table (Job_Queue):<\/p>\n
SET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nSET ANSI_PADDING ON\r\nGO\r\n\r\nCREATE TABLE [dbo].[Job_Queue] (\r\n[ID] [bigint] IDENTITY(1,1) NOT NULL,\r\n[Priority] [tinyint] NOT NULL CONSTRAINT [DF_Job_Queue_Priority] DEFAULT ((255)),\r\n[Available_Date] [datetime] NOT NULL CONSTRAINT [DF_Job_Queue_Available_Date] DEFAULT (getdate()),\r\n[Timeout_Date] [datetime] NULL,\r\n[Attempts] [int] NOT NULL CONSTRAINT [DF_Job_Queue_Attempts] DEFAULT ((0)),\r\n[Processing_Mark] [uniqueidentifier] NULL,\r\n[Processing_Server] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,\r\n[Last_Attempt_Start_Date] [datetime] NULL,\r\n[Last_Attempt_End_Date] [datetime] NULL,\r\n[Payload] [varchar](100) NOT NULL CONSTRAINT [DF_Job_Queue_Payload] DEFAULT ('a piece of work in the queue')\r\nCONSTRAINT [PK_Job_Queue] PRIMARY KEY CLUSTERED\r\n(\r\n[ID] ASC\r\n) ON [PRIMARY]\r\n) ON [PRIMARY]\r\n\r\nGO\r\n\r\nSET ANSI_PADDING OFF\r\nGO<\/pre>\nAnd here is the stored procedure for retrieving one or more jobs, with anti-collision logic (usp_API_Get_Job):<\/p>\n
SET ANSI_NULLS ON\r\nGO\r\n\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n \r\nALTER PROCEDURE [dbo].[usp_API_Get_Job]\r\n \r\n\t@sProcessingServer VARCHAR(50),\r\n\t@nWorkCount INT = 1\r\n \r\nAS\r\n \r\nBEGIN\r\n\t-- SET NOCOUNT ON added to prevent extra result sets from\r\n\t-- interfering with SELECT statements.\r\n\tSET NOCOUNT ON;\r\n\t \r\n\tDECLARE @xGUID UNIQUEIDENTIFIER\r\n\t \r\n\tSET @xGUID = NEWID()\r\n\t \r\n\tUPDATE jq SET\r\n\t\tProcessing_Mark = @xGUID,\r\n\t\tProcessing_Server = @sProcessingServer,\r\n\t\tAttempts = Attempts + 1,\r\n\t\tLast_Attempt_Start_Date = GETDATE(),\r\n\t\tTimeout_Date = DATEADD (mi, 15, GETDATE())\r\n\tFROM Job_Queue AS jq WITH (UPDLOCK)\r\n\tINNER JOIN (\r\n\t\tSELECT TOP (@nWorkCount) ID\r\n\t\tFROM Job_Queue WITH (READPAST)\r\n\t\tWHERE\r\n\t\t(Processing_Mark IS NULL AND Attempts = 0 AND Available_Date <= GETDATE())\r\n\t\tOR (\r\n\t\t\tISNULL(Last_Attempt_End_Date, '1\/1\/2000') < Last_Attempt_Start_Date\r\n\t\t\tAND ISNULL (Timeout_Date, Available_Date) <= GETDATE()\r\n\t\t\tAND Attempts < 3\r\n\t\t)\r\n\t\tORDER BY Priority ASC, Available_Date ASC\r\n\t) AS jqs\r\n\tON jq.ID = jqs.ID\r\n\tWHERE\r\n\t(jq.Processing_Mark IS NULL AND jq.Attempts = 0 AND jq.Available_Date <= GETDATE())\r\n\tOR (\r\n\t\tISNULL(jq.Last_Attempt_End_Date, '1\/1\/2000') < jq.Last_Attempt_Start_Date\r\n\t\tAND ISNULL (jq.Timeout_Date, jq.Available_Date) <= GETDATE()\r\n\t\tAND jq.Attempts < 3\r\n\t)\r\n\t \r\n\tSELECT ID, Payload\r\n\tFROM Job_Queue\r\n\tWHERE Processing_Mark = @xGUID\r\nEND<\/pre>\nSo I'll see if this behaves well under some heavy loads..<\/p>\n","protected":false},"excerpt":{"rendered":"
I recently wrote a high-volume, fast paced application which supports many threads reaching into a work queue (SQL table) and retrieving a row representing a piece of work.\u00a0 The table uses an identity column as its primary key. In other work experiences, I have seen SQL locking hints used to prevent two threads from grabbing […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18],"tags":[],"class_list":["post-12","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"_links":{"self":[{"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=\/wp\/v2\/posts\/12","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=12"}],"version-history":[{"count":1,"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=\/wp\/v2\/posts\/12\/revisions"}],"predecessor-version":[{"id":415,"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=\/wp\/v2\/posts\/12\/revisions\/415"}],"wp:attachment":[{"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=12"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=12"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bitsofgenius.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=12"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}