Manage concurrency in SQL Server

Question

I have a table in my SQL Server database which contains some data about books like the given screenshot below:

+----+-------+-------+--------+
| Id | Title | Price | Status |
+----+-------+-------+--------+
|  1 | C#6   | 40.00 |      0 |
+----+-------+-------+--------+

The Status column indicates to the availability of the book for borrowing (0 = Available, 1 = Borrowed).

Let's assume that 2 or more users saw the book in my web application and those users are trying to borrow the book at the same moment, without managing the concurrency we will get an exception for the other users except the user #1.

I know that we can use SQL transactions and also isolation level like Serializable to make sure of no more users are using the same piece of data at the same time, but here, I have couple of questions:

1. Is this the best practice of such situation? If not, kindly share your recommendation.
2. If so, what about the other users and what should they get after user #1 got the book for borrowing? And also, is it good to handle the concurrency at the final level after the final confirmation or before?

Answer 1

The best practice in this case would be optimistic concurrency. Add a rowversion column to the table and check to see if the value has changed from the original value when updating:

CREATE TABLE dbo.Book(
      Id int NOT NULL CONSTRAINT PK_book PRIMARY KEY
    , Title varchar(30) NOT NULL
    , Price decimal(9,2) NOT NULL
    , Status bit NOT NULL
    , row_version rowversion NOT NULL
    );

INSERT INTO dbo.Book (Id, Title, Price, [Status])
    VALUES(1, 'C# 6', 40.0, 0);
GO


CREATE PROC dbo.UpdateBookAvailability
      @Id int
    , @Rowversion rowversion
    , @Status bit
AS
UPDATE dbo.Book
SET Status = @Status
WHERE
    Id = @Id
    AND row_version = @RowVersion;
IF @@ROWCOUNT < 1
BEGIN
    RAISERROR('book not avaiable', 16, 1);
END;
GO