Handling Database Concurrency with Entity Framework Core

This repository contains a small ASP.NET Core Minimal API sample built with .NET 10, Entity Framework Core, and SQL Server to demonstrate database concurrency issues during account balance updates.

The application exposes two endpoints for bank account operations:

• POST /api/accounts/{accountNumber}/deposits
• POST /api/accounts/{accountNumber}/withdrawals

The withdrawal flow intentionally includes a delay before saving changes so concurrent requests can reproduce race conditions more easily. The data model also includes a SQL Server rowversion column, and the source code contains commented examples for experimenting with pessimistic locking.

What this project demonstrates

• Minimal APIs in ASP.NET Core
• Entity Framework Core with SQL Server
• A simple account model with balance updates
• Concurrency issues caused by overlapping requests
• A starting point for comparing optimistic and pessimistic concurrency approaches
• OpenAPI/Swagger support for exploring the API

Prerequisites

• .NET 10 SDK
• SQL Server LocalDB or another reachable SQL Server instance

The default connection string in DatabaseConcurrencySample/appsettings.json points to LocalDB:

{
  "ConnectionStrings": {
    "SqlConnection": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=MinimalDB"
  }
}

If you want to use a different SQL Server instance, update that connection string before running the application.

How to run the project

From the repository root:

dotnet restore
dotnet ef database update --project DatabaseConcurrencySample
dotnet run --project DatabaseConcurrencySample

Once the application starts, open Swagger UI in the browser. The launch URL depends on the local runtime port, but Visual Studio or the application output will show it.

Sample data setup

The project does not seed accounts automatically, so create at least one record in the Accounts table before testing.

Example SQL:

INSERT INTO Accounts (Number, LastUpdate, Amount)
VALUES ('ACC-001', SYSDATETIMEOFFSET(), 1000);

How to test the concurrency behavior

This sample is most useful when two or more requests hit the same account at nearly the same time.

Scenario 1: Regular deposit

Send a request like:

POST /api/accounts/ACC-001/deposits
Content-Type: application/json

{
  "amount": 100
}

This updates the account balance immediately.

Scenario 2: Concurrent withdrawals

The withdrawal endpoint waits 10 seconds before saving changes. That delay makes it easier to start multiple requests against the same account and observe how concurrent updates behave.

Example request:

POST /api/accounts/ACC-001/withdrawals
Content-Type: application/json

{
  "amount": 700
}

To reproduce the concurrency problem:

1. Ensure account ACC-001 has a balance such as 1000.
2. Start one withdrawal request for 700.
3. Before the first request completes, start a second withdrawal request for 700 against the same account.
4. Observe the final database state and compare it with the expected business behavior.

Because each request reads the account before the delayed save, this sample helps show how concurrent operations can lead to inconsistent results if the application does not enforce proper concurrency control.

Testing with Swagger or HTTP clients

You can test the API with:

• Swagger UI
• Visual Studio HTTP files
• Postman
• curl
• any tool that can send concurrent HTTP requests

Example curl commands:

curl -X POST "https://localhost:5001/api/accounts/ACC-001/deposits" -H "Content-Type: application/json" -d "{\"amount\":100}"
curl -X POST "https://localhost:5001/api/accounts/ACC-001/withdrawals" -H "Content-Type: application/json" -d "{\"amount\":700}"

Adjust the host and port to match your local environment.

Notes

• The project is intentionally simple and focused on concurrency behavior.
• EnableSensitiveDataLogging is enabled in the EF Core configuration, which is useful for demos and local debugging.
• The commented code in Program.cs shows an alternative approach based on a database transaction and SQL Server locking hints for pessimistic concurrency experiments.