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
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?