When multiple users access the same application at the same time, they may also try to read or update the same database record.

For example:

• User A opens a product and changes its price.
• User B opens the same product at the same time and also changes its price.
• Both users click Save.

What happens?

Without a strategy to handle concurrent updates, one user's changes may silently overwrite the other's. This is where concurrency control and database locking strategies become important.

Two common approaches are:

1. Optimistic locking
2. Pessimistic locking

In this article, we'll learn both approaches using .NET and SQL Server, with beginner-friendly examples and commented code.

What Problem Are We Trying to Solve?

Imagine we have a simple Products table.

CREATE TABLE Products
(
    Id INT PRIMARY KEY,
    Name NVARCHAR(100),
    Price DECIMAL(10, 2)
);

The table contains this record:

Now imagine two users open this product at exactly the same time.

User A

Reads:

Laptop - ₹50,000

User A changes the price to:

₹55,000

User B

At the same time, User B also reads:

Laptop - ₹50,000

User B changes the price to:

₹48,000

If User A saves first and User B saves afterward, User B's update may overwrite User A's change.

This is often called a lost update problem.

We need a way to detect or prevent this conflict.

1. What Is Optimistic Locking?

Optimistic locking assumes that conflicts are rare.

The basic idea is:

"Let everyone read and work with the data. When someone tries to save, check whether the data has changed since they originally read it."

If the data has not changed, the update succeeds.

If the data has changed, the application detects a concurrency conflict.

How Does It Work?

A common approach is to add a rowversion column to the SQL Server table.

CREATE TABLE Products
(
    Id INT PRIMARY KEY,
    Name NVARCHAR(100),
    Price DECIMAL(10, 2),

    -- SQL Server automatically changes this value
    -- whenever the row is updated.
    RowVersion ROWVERSION NOT NULL
);

The RowVersion column acts like a version identifier for the row.

For example:

Product before update:

Id: 1
Price: 50000
RowVersion: 0x00000000000007D1

When the product is updated, SQL Server changes the RowVersion.

Product after update:

Id: 1
Price: 55000
RowVersion: 0x00000000000007D2

Now, when a user attempts to update the record, we can check whether the version they originally read is still the current version.

Optimistic Locking with ADO.NET

First, let's create a simple C# model.

public class Product
{
    public int Id { get; set; }

    public string Name { get; set; } = string.Empty;

    public decimal Price { get; set; }

    // This stores the row version returned by SQL Server.
    // It helps us detect whether another user changed the row.
    public byte[] RowVersion { get; set; } = Array.Empty<byte>();
}

Step 1: Read the Product

using Microsoft.Data.SqlClient;

public async Task<Product?> GetProductAsync(int id)
{
    // Replace this with your actual connection string.
    var connectionString =
        "Server=.;Database=ShopDb;Trusted_Connection=True;TrustServerCertificate=True;";

    await using var connection =
        new SqlConnection(connectionString);

    await connection.OpenAsync();

    var sql = @"
        SELECT Id, Name, Price, RowVersion
        FROM Products
        WHERE Id = @Id";

    await using var command =
        new SqlCommand(sql, connection);

    // Use parameters instead of string concatenation.
    command.Parameters.AddWithValue("@Id", id);

    await using var reader =
        await command.ExecuteReaderAsync();

    if (!await reader.ReadAsync())
    {
        return null;
    }

    return new Product
    {
        Id = reader.GetInt32(0),
        Name = reader.GetString(1),
        Price = reader.GetDecimal(2),

        // Save the row version that existed when we read the record.
        RowVersion = (byte[])reader["RowVersion"]
    };
}

When the user loads the product, we also store its current RowVersion.

Later, when the user clicks Save, we use that version in the UPDATE statement.

Step 2: Update the Product Safely

public async Task<bool> UpdateProductAsync(Product product)
{
    var connectionString =
        "Server=.;Database=ShopDb;Trusted_Connection=True;TrustServerCertificate=True;";

    await using var connection =
        new SqlConnection(connectionString);

    await connection.OpenAsync();

    var sql = @"
        UPDATE Products
        SET
            Name = @Name,
            Price = @Price
        WHERE
            Id = @Id

            -- Only update if the row version is still the same
            -- as when the user originally read the record.
            AND RowVersion = @RowVersion";

    await using var command =
        new SqlCommand(sql, connection);

    command.Parameters.AddWithValue("@Id", product.Id);
    command.Parameters.AddWithValue("@Name", product.Name);
    command.Parameters.AddWithValue("@Price", product.Price);
    command.Parameters.Add("@RowVersion", System.Data.SqlDbType.Timestamp)
        .Value = product.RowVersion;

    // ExecuteNonQueryAsync returns the number of affected rows.
    var affectedRows = await command.ExecuteNonQueryAsync();

    // If 1 row was updated, the operation succeeded.
    // If 0 rows were updated, someone may have changed
    // the record before us.
    return affectedRows == 1;
}

Now imagine this sequence:

User A reads the product

Price: 50000
RowVersion: Version 1

User B reads the product

Price: 50000
RowVersion: Version 1

User A updates the product

The product becomes:

Price: 55000
RowVersion: Version 2

User B tries to update

But User B still has:

RowVersion: Version 1

The SQL query checks:

WHERE Id = @Id
AND RowVersion = @RowVersion

Since the database now contains Version 2, no row matches the condition.

Therefore:

affectedRows = 0

The application can now tell User B:

"This product was modified by another user. Please reload the latest data and try again."

That is optimistic locking.

Optimistic Locking with Entity Framework Core

Entity Framework Core makes optimistic concurrency easier.

First, create the model:

using System.ComponentModel.DataAnnotations;

public class Product
{
    public int Id { get; set; }

    public string Name { get; set; } = string.Empty;

    public decimal Price { get; set; }

    // [Timestamp] tells EF Core that this property
    // should be used for optimistic concurrency checking.
    [Timestamp]
    public byte[] RowVersion { get; set; } = Array.Empty<byte>();
}

You can also configure it using Fluent API:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>()
        .Property(p => p.RowVersion)
        .IsRowVersion();
}

When EF Core detects that another user changed the row before SaveChangesAsync(), it can throw a concurrency exception.

try
{
    // Get the product.
    var product = await _context.Products.FindAsync(1);

    if (product == null)
    {
        return;
    }

    // Change the price.
    product.Price = 55000;

    // EF Core generates an UPDATE statement that checks
    // the original concurrency value.
    await _context.SaveChangesAsync();

    Console.WriteLine("Product updated successfully.");
}
catch (DbUpdateConcurrencyException)
{
    // This happens when another user changed or deleted
    // the record before our update was completed.
    Console.WriteLine(
        "The product was modified by another user. " +
        "Reload the latest data and try again.");
}

This is one reason rowversion is commonly used with EF Core applications.

Advantages of Optimistic Locking

Optimistic locking works well when conflicts are relatively uncommon.

Benefits

• Users are not blocked while reading data.
• Long-running locks are avoided.
• It generally scales well for many read operations.
• It is useful for web applications where users may keep a page open for several minutes.

Disadvantages

• The application must handle concurrency conflicts.
• A user may spend time editing data and then discover that another user changed it.
• Users may need to reload and merge changes.

2. What Is Pessimistic Locking?

Pessimistic locking takes the opposite approach.

It assumes:

"A conflict might happen, so lock the data while it is being used."

In simple terms:

• User A accesses a row.
• The application places a lock on that row.
• User B tries to access or modify the same row.
• User B may have to wait until User A finishes and releases the lock.

The lock usually exists within a database transaction.

Pessimistic Locking Example with SQL Server

Suppose we want to reserve a product row for update.

We can use locking hints such as:

UPDLOCK

and:

ROWLOCK

For example:

SELECT *
FROM Products WITH (UPDLOCK, ROWLOCK)
WHERE Id = 1;

Let's understand these hints.

UPDLOCK

This requests an update lock while reading the row.

The intention is:

"I am reading this row because I plan to update it."

ROWLOCK

This asks SQL Server to prefer row-level locking rather than locking a larger resource.

However, it is important to understand that SQL Server manages locking internally, and locking hints are requests rather than a guarantee of a particular physical locking behavior in every situation.

Pessimistic Locking with ADO.NET

Here is a simple example.

using Microsoft.Data.SqlClient;

public async Task UpdateProductWithLockAsync(
    int productId,
    decimal newPrice)
{
    var connectionString =
        "Server=.;Database=ShopDb;Trusted_Connection=True;TrustServerCertificate=True;";

    await using var connection =
        new SqlConnection(connectionString);

    await connection.OpenAsync();

    // Start a database transaction.
    await using var transaction =
        (SqlTransaction)await connection.BeginTransactionAsync();

    try
    {
        var selectSql = @"
            SELECT Id, Name, Price
            FROM Products WITH (UPDLOCK, ROWLOCK)
            WHERE Id = @Id";

        await using var selectCommand =
            new SqlCommand(selectSql, connection, transaction);

        selectCommand.Parameters.AddWithValue("@Id", productId);

        await using var reader =
            await selectCommand.ExecuteReaderAsync();

        if (!await reader.ReadAsync())
        {
            throw new Exception("Product not found.");
        }

        // Read the current values while the transaction
        // is still holding the requested lock.
        var productName = reader.GetString(1);
        var currentPrice = reader.GetDecimal(2);

        Console.WriteLine(
            $"Current price of {productName}: {currentPrice}");

        // Close the reader before executing another command.
        await reader.CloseAsync();

        var updateSql = @"
            UPDATE Products
            SET Price = @Price
            WHERE Id = @Id";

        await using var updateCommand =
            new SqlCommand(updateSql, connection, transaction);

        updateCommand.Parameters.AddWithValue("@Id", productId);
        updateCommand.Parameters.AddWithValue("@Price", newPrice);

        await updateCommand.ExecuteNonQueryAsync();

        // Commit releases the transaction's locks.
        await transaction.CommitAsync();

        Console.WriteLine("Product updated successfully.");
    }
    catch
    {
        // If something goes wrong, undo the transaction.
        await transaction.RollbackAsync();

        throw;
    }
}

The important part is:

SELECT *
FROM Products WITH (UPDLOCK, ROWLOCK)
WHERE Id = @Id;

combined with a transaction:

await using var transaction =
    (SqlTransaction)await connection.BeginTransactionAsync();

The lock remains relevant for the transaction according to SQL Server's locking and transaction behavior, helping protect the read-then-update operation from competing modifications.

A Simple Pessimistic Locking Scenario

Imagine User A starts this transaction:

BEGIN TRANSACTION

Read Product 1 with an update lock

Update Product 1

COMMIT

While User A's transaction is still active, User B tries to perform a conflicting operation on the same data.

Depending on the operation, isolation level, and locks involved, User B may wait until User A commits or rolls back.

After User A executes:

COMMIT

the transaction completes and its locks are released.

Then User B can continue.

The Danger of Long Transactions

Pessimistic locking can cause problems if transactions stay open for too long.

For example, this is usually a bad idea:

1. Start transaction
2. Lock product
3. Wait for user to fill out a form
4. User goes for lunch
5. Commit transaction

That could cause other users to wait unnecessarily.

Instead, transactions should generally be as short as possible.

A better pattern is:

1. User fills out the form
2. Start transaction
3. Read and validate the current data
4. Update the data
5. Commit immediately

Pessimistic Locking and Deadlocks

Pessimistic locking can increase the possibility of blocking and, in some situations, deadlocks.

A deadlock can look like this:

Transaction A

Locks Product 1
Waits for Product 2

Transaction B

Locks Product 2
Waits for Product 1

Now both transactions are waiting for each other.

SQL Server detects the deadlock and chooses one transaction as the deadlock victim, rolling it back.

One useful strategy for reducing deadlocks is to access resources in a consistent order.

For example, if you always update products in ID order:

Product 1
Product 2
Product 3

rather than sometimes doing:

Product 3
Product 1

you can reduce certain deadlock scenarios.

Optimistic vs Pessimistic Locking

Which One Should You Use?

There is no universal answer.

Use Optimistic Locking When

Choose optimistic locking when:

• Many users mostly read data.
• Simultaneous updates are relatively rare.
• Your application can ask users to reload or resolve conflicts.
• You are building a typical web application or business application.

For many CRUD applications, optimistic concurrency using SQL Server rowversion is a practical starting point.

Use Pessimistic Locking When

Consider pessimistic locking when:

• Multiple processes frequently modify the same data.
• Conflicting updates would be especially problematic.
• You need to protect a short, critical read-modify-write operation.
• Waiting is preferable to allowing concurrent conflicting work.

Examples might include certain inventory, reservation, or financial workflows—but the exact design should depend on the business rules and transaction requirements.

A Real-World Example: Booking the Last Seat

Suppose there is only one seat left.

Two users try to book it at the same time.

Optimistic Approach

Both users may initially see:

Available seats: 1

The first user successfully updates the record.

The second user's update detects that the data changed.

The second user receives a message:

"Sorry, this seat is no longer available."

Pessimistic Approach

The first transaction locks the relevant data, checks availability, and creates the booking.

A competing transaction attempting a conflicting operation may wait.

Once the first transaction completes, the next transaction checks the latest availability.

If no seat remains, it does not create a booking.

The important point is that the entire operation should be designed as a short atomic transaction.

A Safer Inventory Pattern

For inventory-like scenarios, you can often avoid a separate read followed by an update.

Instead, perform the business rule directly in the UPDATE statement.

UPDATE Products
SET Stock = Stock - 1
WHERE Id = @Id
  AND Stock > 0;

Then check how many rows were affected.

In C#:

public async Task<bool> TryBuyProductAsync(int productId)
{
    var connectionString =
        "Server=.;Database=ShopDb;Trusted_Connection=True;TrustServerCertificate=True;";

    await using var connection =
        new SqlConnection(connectionString);

    await connection.OpenAsync();

    var sql = @"
        UPDATE Products
        SET Stock = Stock - 1
        WHERE Id = @Id
          AND Stock > 0";

    await using var command =
        new SqlCommand(sql, connection);

    command.Parameters.AddWithValue("@Id", productId);

    // If affectedRows is 1, stock was available.
    // If affectedRows is 0, either the product does not exist
    // or there was no stock remaining.
    var affectedRows =
        await command.ExecuteNonQueryAsync();

    return affectedRows == 1;
}

This pattern is often simpler and safer than manually locking a row and then performing application-side logic.

A Simple Way to Remember the Difference

Think of a shared document.

Optimistic Locking

Two people can open and edit the document.

When someone saves, the system checks:

"Has somebody else changed this since you opened it?"

If yes, there is a conflict.

Pessimistic Locking

The first person effectively reserves the document for the critical operation.

Other conflicting operations may need to wait until the first person is finished.

Final Thoughts

Concurrency can initially sound complicated, but the core idea is simple:

What should happen when two users try to change the same data at the same time?

Optimistic locking says:

"Let them work independently. Detect the conflict when saving."

Pessimistic locking says:

"Protect the critical section so conflicting operations are coordinated."

For many .NET applications using SQL Server, a good starting point is optimistic concurrency with a rowversion column. It avoids holding long-running locks and works naturally with Entity Framework Core.

For operations where contention is high or correctness requires carefully coordinated access, a short transaction with appropriate locking or an atomic SQL operation may be more suitable.

The key is not simply choosing "optimistic" or "pessimistic." The best solution depends on your application's business rules, how frequently conflicts occur, and what should happen when two users attempt the same operation at the same time.

Key Takeaways

• Concurrency means multiple users or processes accessing data at the same time.
• Optimistic locking detects conflicts when data is updated.
• SQL Server's rowversion is commonly used for optimistic concurrency.
• Pessimistic locking coordinates conflicting access during a transaction.
• Keep database transactions as short as possible.
• Long-running locks can cause blocking and increase the risk of deadlocks.
• For some scenarios, an atomic SQL statement is better than manually implementing a lock.
• Always design concurrency around the actual business rule you need to protect.