1

Tables:

Clients (20+ fields)
  Id int,
  Name varchar,
  [State] varchar,
  Address varhcar

ClientCheckout
  ClientId
  CheckedOutAt Datetime null

I'm using a separate table for managing the checkout process to avoid putting any locks on the Clients table.

I need to ensure that a client can't be checked out more than once. Once it is checked out I need to return the id of the client that was checked out.

This is what I came up with but I feel like there should be a better way

Procedure:

Create procedure (@state varchar(2))
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

DECLARE @clientid INT

SELECT TOP 1
    @clientid = CustomerId
FROM ClientCheckout
WHERE CheckedOutAt IS NULL AND [state] = @state

BEGIN TRANSACTION T


    UPDATE ClientCheckout
    SET CheckedOutAt = getdate()
    WHERE ClientId = @clientid;
commit transaction t
SELECT
@clientid

I left the initial selection of @clientid outside of the transaction so as not to lock the Clients table.

This seems to work but it doesn't feel quite right. Suggestions?

  • What does the [state] column indicate in the Clients table? – Max Vernon Oct 23 '15 at 15:14
3

You can combine the select and update into a single statement that runs atomically, by doing something like:

CREATE TABLE dbo.Clients
(
    ClientID INT NOT NULL
        CONSTRAINT PK_Clients
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ClientState VARCHAR(255)
);

CREATE TABLE dbo.CallCheckout
(
    ClientID INT NOT NULL
    , CheckedOutAt DATETIME
);

INSERT INTO dbo.Clients(ClientState)
VALUES ('AA')
    , ('AB')
    , ('AC');

INSERT INTO dbo.CallCheckout (ClientId, CheckedOutAt)
VALUES (1, NULL);

INSERT INTO dbo.CallCheckout (ClientId, CheckedOutAt)
VALUES (2, GETDATE());

INSERT INTO dbo.CallCheckout (ClientId, CheckedOutAt)
VALUES (3, NULL);
GO

CREATE PROCEDURE dbo.CheckOut
(
    @state VARCHAR(3)
)
AS
BEGIN
    UPDATE TOP(1) dbo.CallCheckout
    SET CheckedOutAt = getdate()
    OUTPUT inserted.clientid
    FROM dbo.CallCheckout cc
        INNER JOIN dbo.Clients c ON cc.ClientID = c.ClientID
    WHERE cc.CheckedOutAt IS NULL 
        AND c.ClientState = @state;
END
GO

EXEC dbo.CheckOut 'AA';
EXEC dbo.CheckOut 'AC';

The results are:

enter image description here

Notice, I've specified the schema in the CREATE PROCEDURE and UPDATE statements. You should always specify the schema, even if you only ever use dbo, since it prevents the server from having to make that decision, and potentially making the wrong decision.

Also, please do not use reserved keywords for column names. I'm talking about [state]. Notice, I've named that column CheckoutState which is not a reserved keyword, and makes the column name more meaningful.

  • Wouldn't this lock the clients table though? I really don't want to do that. – recursive_acronym Oct 23 '15 at 14:32
  • The [state] or [CheckedOutState] field isn't in the checkout table. I'd rather not duplicate it if I can avoid it. – recursive_acronym Oct 23 '15 at 14:41
  • 2
    @recursive_acronym Why is everyone so afraid of locking? You want locking. It's what prevents two processes from modifying the same row at the same time. – Aaron Bertrand Oct 23 '15 at 14:43
  • 1
    This won't lock the entire table assuming you have an index on CheckedOutAt and CheckoutState. – Max Vernon Oct 23 '15 at 14:44
  • 1
    I've updated my answer to move the [state] column into the Clients table. – Max Vernon Oct 23 '15 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.