October 25, 2016 at 9:14 am
Hi all
I have a question about concurrency.
Let me provide some set-up code first.
DROP TABLE IF EXISTS dbo.Test1
CREATE TABLE dbo.Test1
(
PK INT IDENTITY(1, 1)
PRIMARY KEY CLUSTERED
,Col1 INT
,Col2 INT
,StartedAt DATETIME
);
INSERT dbo.Test1
(Col1, Col2)
VALUES (1, 1),
(1, 2),
(2, 1);
SELECT *
FROM dbo.Test1 t;
I want to write a proc which will do the following:
1) For a given Col1, find the row with the minimum Col2 in the table where StartedAt is null.
2) Update StartedAt to GetDate()
3) Return some columns from the updated row
The important thing to note is that this proc may be called from multiple threads at the same time. It needs to handle this as gracefully as possible, servicing each thread by updating and returning the 'next available' row (or an empty resultset if no suitable row is found).
Here is my first attempt. Please can I invite comments as to whether this seems like a sensible design? Thanks.
CREATE PROCEDURE dbo.GetNextId @Col1 INT
AS
SET NOCOUNT, XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DECLARE @pk INT;
BEGIN TRY
BEGIN TRAN;
SELECT TOP 1
@pk = t.PK
FROM dbo.Test1 t
WHERE t.Col1 = @Col1
AND t.StartedAt IS NULL
ORDER BY t.Col2 ASC;
UPDATE dbo.Test1
SET StartedAt = GETDATE()
WHERE PK = @pk;
SELECT t.PK
, t.Col1
, t.Col2
FROM dbo.Test1 t
WHERE t.PK = @pk;
COMMIT TRAN;
END TRY
BEGIN CATCH
THROW;
END CATCH;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 25, 2016 at 10:23 am
Why not do it at once instead of using a variable?
CREATE PROCEDURE dbo.GetNextId @Col1 INT
AS
SET NOCOUNT, XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRY
DECLARE @Output TABLE(
PK INT
,Col1 INT
,Col2 INT
);
WITH cteTest1 AS(
SELECT TOP 1 *
FROM dbo.Test1 t
WHERE t.Col1 = @Col1
AND t.StartedAt IS NULL
ORDER BY t.Col2 ASC
)
UPDATE cteTest1
SET StartedAt = GETDATE()
OUTPUT inserted.PK, inserted.Col1, inserted.Col2
INTO @Output( PK, Col1, Col2);
SELECT PK
, Col1
, Col2
FROM @Output;
END TRY
BEGIN CATCH
THROW;
END CATCH;
October 25, 2016 at 10:31 am
Nice, Luis.
Is SERIALIZABLE required in this case? Or would READ COMMITTED be enough?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 25, 2016 at 10:38 am
READ COMMITTED should be enough. I would test, though.
October 25, 2016 at 10:45 am
Luis Cazares (10/25/2016)
READ COMMITTED should be enough. I would test, though.
Thanks again.
I agree: time to attempt to crash it with some testing.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 25, 2016 at 11:58 am
Multi-threaded testing suggests that READ COMMITTED works just fine, using your atomic code.
Thanks again.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 25, 2016 at 12:10 pm
That's great. Thank you for the feedback.
October 25, 2016 at 12:23 pm
It can also be done without the CTE or the need for a @results table.
And since it's a single statement, there is no need for the explicit transaction.
CREATE PROCEDURE dbo.GetNextId
@Col1 INT
AS
SET NOCOUNT, XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRY
UPDATE TOP(1) dbo.Test1
SET StartedAt = GETDATE()
OUTPUT inserted.PK, inserted.col1, inserted.col2
WHERE Col1 = @Col1
AND StartedAt IS NULL;
END TRY
BEGIN CATCH
THROW;
END CATCH;
October 25, 2016 at 12:34 pm
DesNorton (10/25/2016)
It can also be done without the CTE or the need for a @results table.And since it's a single statement, there is no need for the explicit transaction.
CREATE PROCEDURE dbo.GetNextId
@Col1 INT
AS
SET NOCOUNT, XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRY
UPDATE TOP(1) dbo.Test1
SET StartedAt = GETDATE()
OUTPUT inserted.PK, inserted.col1, inserted.col2
WHERE Col1 = @Col1
AND StartedAt IS NULL;
END TRY
BEGIN CATCH
THROW;
END CATCH;
This is a good idea, though it fails to output anything in the case where no row is selected for update. I need an empty resultset in this case.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 25, 2016 at 12:42 pm
The reason for the CTE was to include the ORDER BY which isn't available in the simple UPDATE.
October 25, 2016 at 12:44 pm
Luis Cazares (10/25/2016)
The reason for the CTE was to include the ORDER BY which isn't available in the simple UPDATE.
And I missed that. Definitely need the ORDER BY.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 25, 2016 at 12:46 pm
Phil Parkin (10/25/2016)
Luis Cazares (10/25/2016)
The reason for the CTE was to include the ORDER BY which isn't available in the simple UPDATE.And I missed that. Definitely need the ORDER BY.
I also missed it :blush:
October 25, 2016 at 12:51 pm
However, the OUTPUT clause will generate an empty result set when no rows are updated, so that should work.
And with a single statement and the error handling just throwing the original error, I see no reason for the TRY...CATCH...
October 25, 2016 at 1:06 pm
Luis Cazares (10/25/2016)
However, the OUTPUT clause will generate an empty result set when no rows are updated, so that should work.And with a single statement and the error handling just throwing the original error, I see no reason for the TRY...CATCH...
I should test stuff more before I make assumptions :blush:
But that does lead me to a question. Is there a reason that you chose to use a table variable rather than simply doing this?
UPDATE cteTest1
SET StartedAt = GETDATE()
OUTPUT inserted.PK, inserted.Col1, inserted.Col2;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 25, 2016 at 1:14 pm
Luis Cazares (10/25/2016)
The reason for the CTE was to include the ORDER BY which isn't available in the simple UPDATE.
The correct covering index also takes care of the order by
CREATE TABLE dbo.Test1 (
PK INT IDENTITY(1, 1)
PRIMARY KEY CLUSTERED
,Col1 INT
,Col2 INT
,StartedAt DATETIME
);
GO
CREATE NONCLUSTERED INDEX ix_Test1
ON dbo.Test1(Col1, Col2)
INCLUDE (StartedAt, PK);
GO
INSERT dbo.Test1 (Col1, Col2)
VALUES (1, 2),(1, 4),(2, 1)
,(1, 1),(1, 3),(2, 2)
,(3, 1),(3, 3),(3, 2)
,(4, 1),(4, 3),(4, 2);
GO
IF OBJECT_ID('dbo.GetNextId', 'P') IS NULL
EXEC ('CREATE PROCEDURE dbo.GetNextId AS DUMMY:;');
GO
ALTER PROCEDURE dbo.GetNextId
@Col1 INT
AS
SET NOCOUNT, XACT_ABORT ON;
UPDATE TOP(1) dbo.Test1
SET StartedAt = GETDATE()
OUTPUT inserted.PK, inserted.col1, inserted.col2
WHERE Col1 = @Col1
AND StartedAt IS NULL;
GO
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply