Skip to content
Tutorials Center
My learning

Topics

Lesson 152 of 182 ยท Data Access

Working with SQL and NoSQL from C#

Real database work in C#: optimistic and pessimistic concurrency, isolation levels, retries and connection resilience, pagination, plus Redis and MongoDB basics with runnable models.

Knowing how to open a connection and run a query is the start. Production data access is about what happens when things go wrong or collide: two users edit the same record, the network drops for a second, a page of results shifts while someone scrolls, or a cache holds yesterday's answer. This lesson covers the habits that make database code correct under those conditions, then takes a first look at two popular NoSQL stores, Redis and MongoDB.

As before, the runner has no database drivers and no NuGet packages, so driver code is shown read-only and labelled. Every idea is also modelled with runnable C# so you can see the behavior: a row-version check for optimistic concurrency, a retry policy with exponential backoff, keyset pagination, a miniature Redis with expiry, and a small document store. Background: Data Access in .NET, ADO.NET and Entity Framework Core.

Transactions and isolation, briefly

A transaction groups statements so they succeed or fail together. What other transactions can see while yours is running is controlled by the isolation level. Stronger isolation protects you from more anomalies and costs more in locking or versioning.

Level Prevents Still possible
Read Uncommitted nothing dirty reads (seeing uncommitted data)
Read Committed (common default) dirty reads non-repeatable reads, phantoms
Repeatable Read dirty and non-repeatable reads phantom rows (database dependent)
Snapshot / Serializable all of those more blocking or more retries

Defaults differ: SQL Server uses Read Committed (optionally with row versioning), PostgreSQL uses Read Committed, and SQLite is serializable by design. Pick a stronger level only for the operation that needs it, and keep the transaction short.

C#
// Read-only: choose an isolation level with ADO.NET (any provider).
await using var transaction = await connection.BeginTransactionAsync(
    System.Data.IsolationLevel.Serializable);

The same ideas apply in EF Core: SaveChanges() already wraps its statements in a transaction, and db.Database.BeginTransactionAsync() lets you span several SaveChanges calls.

Concurrency: two users, one row

Suppose two users open the same account page. Both see a balance of 100. One adds 50, the other subtracts 20. If both write back their computed value, whoever saves last wins and the other change vanishes: the lost update. There are two standard defenses.

Pessimistic concurrency assumes conflicts are likely and locks the row while one user works. The other waits. In SQL this looks like SELECT ... WITH (UPDLOCK) (SQL Server) or SELECT ... FOR UPDATE (PostgreSQL, MySQL) inside a transaction. It is simple and safe but holds locks, hurts throughput and risks deadlocks, so it fits short, hot operations such as reserving the last seat.

Optimistic concurrency assumes conflicts are rare. Nobody locks. Each row carries a version (a counter or a database rowversion/xmin). When saving, you say "update this row only if the version is still the one I read". If someone else saved first, zero rows match and you know about the conflict, then you re-read and retry, or tell the user.

C#
// Read-only: optimistic concurrency in raw SQL.
// UPDATE Accounts SET Balance = @balance, Version = Version + 1
// WHERE Id = @id AND Version = @versionIRead;
// rowsAffected == 0  ->  someone else changed it first.

In EF Core you mark a property as a concurrency token and EF adds that AND Version = ... check for you, throwing DbUpdateConcurrencyException when no row matched:

C#
// Read-only: EF Core.
public class Account
{
    public int Id { get; set; }
    public decimal Balance { get; set; }

    [Timestamp]                       // SQL Server rowversion; other providers use a configured token
    public byte[] RowVersion { get; set; } = [];
}

try
{
    await db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    var entry = ex.Entries.Single();
    var databaseValues = await entry.GetDatabaseValuesAsync();   // what the winner saved
    // decide: reload and retry, merge, or show the user both versions
}

Here is the mechanism in plain C#. The "table" only accepts an update if the caller's version matches, just like the WHERE clause above:

C# Optimistic concurrency with a row version

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

B's stale write is rejected, and after re-reading it succeeds: 100 + 50 โˆ’ 20 = 130. The concurrent loop then finishes at 130 + 40 ร— 10 = 530, no matter how the tasks interleave, because every conflicting write is detected and redone. How many conflicts happened varies from run to run, so it is not printed.

Choosing: use optimistic concurrency for most web applications (long "think time" between read and save, rare conflicts). Use pessimistic locking, or an atomic statement like UPDATE Stock SET Qty = Qty - 1 WHERE Id = @id AND Qty > 0, for small, hot, high-conflict operations. The atomic statement is often the best answer of all: the database does the read-modify-write in one step.

Resilience: transient failures and retries

Networks and cloud databases fail briefly: a failover, a dropped connection, a throttling response, a deadlock victim. These transient errors usually succeed if you simply try again. Permanent errors (a constraint violation, a syntax error) will not, and retrying them wastes time or repeats side effects.

A good retry policy has four parts:

  1. Retry only errors known to be transient.
  2. Limit the attempts.
  3. Wait between attempts, growing the delay (exponential backoff) and adding a little random jitter, so many clients do not all retry at the same instant.
  4. Make the operation idempotent (safe to repeat) or run it inside a transaction that is rolled back as a unit.

Libraries do this for you: EF Core has options.UseSqlServer(cs, o => o.EnableRetryOnFailure()), and Microsoft.Extensions.Http.Resilience covers HTTP calls (see Caching and HttpClientFactory). The core of such a policy is short enough to write yourself:

C# Retry with exponential backoff

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

The exception filter when (attempt < maxAttempts) is doing double duty: on the last attempt the filter is false, so the exception escapes to the caller (see exception filters). The permanent error is not caught at all, so it fails immediately after one call.

Retries and side effects do not mix naively. If a request timed out after the database committed, retrying an INSERT creates a duplicate. Use idempotency keys (a unique request id column with a unique index), MERGE/upsert statements, or retry the whole transaction rather than single statements. Add a timeout to every command and pass a CancellationToken (cancellation) so a stuck query cannot hold a request forever.

Pagination that stays correct

Returning thousands of rows to a page is slow and wasteful, so you page. The obvious way is offset pagination: OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY (LINQ: Skip(10).Take(5)). It has two weaknesses: the database still walks past all the skipped rows (slow on deep pages), and if rows are inserted or deleted between page requests, items are repeated or missed.

Keyset pagination (also called seek pagination) remembers the last key seen and asks for rows after it: WHERE Id < @lastId ORDER BY Id DESC (LINQ: Where(x => x.Id < lastId).OrderByDescending(x => x.Id).Take(5)). With an index on the key, every page costs the same, and the result is stable:

C# Offset versus keyset pagination

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

The offset version shows 16 twice, because the new post pushed everything down by one place. The keyset version continues exactly where the reader stopped. The trade-off: keyset pagination cannot jump to "page 37", and it needs a unique, indexed, stable sort key (add the id as a tiebreaker when sorting by something like a date).

Redis: fast key-value storage

Redis is an in-memory data store accessed by key. Its values can be strings, hashes, lists, sets and sorted sets, and keys can expire automatically. The usual uses are caching, counters, rate limiting, sessions, queues and pub/sub. From C# you use a client library, most commonly StackExchange.Redis:

C#
// Read-only: needs the NuGet package StackExchange.Redis and a Redis server.
using StackExchange.Redis;

// Create ONE multiplexer for the whole app and reuse it; it is thread-safe and expensive to build.
ConnectionMultiplexer redis = await ConnectionMultiplexer.ConnectAsync("localhost:6379");
IDatabase db = redis.GetDatabase();

await db.StringSetAsync("product:7", "{\"name\":\"Lamp\"}", expiry: TimeSpan.FromMinutes(5));
RedisValue json = await db.StringGetAsync("product:7");
long views = await db.StringIncrementAsync("views:product:7");   // atomic counter

In ASP.NET Core you normally depend on the abstraction IDistributedCache (in the shared framework, with AddDistributedMemoryCache() for development and AddStackExchangeRedisCache() in production), which gives simple get/set of bytes or strings with expiration.

The commands are simple enough to model, including the part that surprises newcomers: expiry. This miniature uses a controllable clock so the example is deterministic, and implements the cache-aside pattern: look in the cache, on a miss read the database and fill the cache:

C# A mini Redis and the cache-aside pattern

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Three product reads cost one database read; after the 60-second TTL passes, the next read goes back to the database. Cache-aside has well-known risks: stale data (invalidate on writes), and the stampede where many requests miss at once and all hit the database (see Caching and HttpClientFactory). Remember that Redis is usually a cache or helper, not your source of truth, unless you configure persistence deliberately.

MongoDB: documents

MongoDB stores documents: JSON-like objects (BSON) grouped into collections, with no fixed schema. Related data is often embedded in one document (an order with its lines) instead of joined across tables. This makes reads of a whole aggregate one lookup, at the cost of duplicated data and weaker cross-document consistency than relational joins.

C#
// Read-only: needs the NuGet package MongoDB.Driver and a MongoDB server.
using MongoDB.Driver;

var client = new MongoClient("mongodb://localhost:27017");
var orders = client.GetDatabase("shop").GetCollection<Order>("orders");

await orders.InsertOneAsync(new Order { Customer = "Ada", City = "London", Total = 59.90m });

var londonOrders = await orders
    .Find(o => o.City == "London" && o.Total > 20m)
    .SortByDescending(o => o.Total)
    .ToListAsync();

The driver translates your lambda into a query document much like EF Core builds SQL. The shape of the data, not the syntax, is the big difference. To feel it, we can model a tiny collection of JSON documents with System.Text.Json and query by a nested path:

C# A tiny document collection

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Kim's document has no address at all and nothing complains. That freedom is both the strength (fast iteration, naturally nested data) and the danger (typos create new fields, old documents have old shapes). Teams control it with validation rules, versioned document shapes and strongly typed C# classes as shown above. See JSON for working with System.Text.Json.

Choosing between SQL and NoSQL

  • Relational (SQL) when data has clear relationships, you need transactions across rows, ad-hoc queries and reports, strict constraints and a mature tooling ecosystem. It is the right default for most business applications.
  • Key-value (Redis) for caches, counters, sessions, rate limits and ephemeral data where speed matters and loss is tolerable or recoverable.
  • Document (MongoDB, Cosmos DB) for aggregate-shaped, evolving data that is read and written as a whole, such as catalogs, content or event payloads.
  • Polyglot persistence (several stores in one system) is common: SQL for the truth, Redis in front for speed, a search engine for text. Each store adds operations work, so add one only for a measured need.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Two users load the same row at version 3. User A saves successfully. What happens to user B's UPDATE ... WHERE Id = @id AND Version = 3?

    A's save incremented the version to 4, so B's WHERE clause no longer matches. Zero affected rows (or DbUpdateConcurrencyException in EF Core) is how optimistic concurrency reports the conflict.
  2. 2. Which errors should a retry policy retry?

    Transient errors often succeed on a second try. Permanent errors will fail the same way again, so retrying them only wastes time and may repeat side effects.
  3. 3. Why does exponential backoff add jitter?

    Without jitter, clients that failed at the same time retry in lockstep and can overload the recovering server again.
  4. 4. A feed shows newest posts first. New posts arrive while a reader pages through it. Which approach avoids duplicates between pages?

    Offsets count positions, which shift when new rows arrive. A key condition such as Id < lastId continues from a fixed point.
  5. 5. In the cache-aside pattern, what happens on a cache miss?

    The application owns the logic: check the cache first, fall back to the source of truth and fill the cache so the next read is fast.

Summary

  • Use transactions for atomic work and choose an isolation level deliberately; keep transactions short.
  • Optimistic concurrency checks a version in the WHERE clause and detects lost updates; pessimistic locking holds locks. An atomic single-statement update is often better than both.
  • Retry only transient errors, with limits, exponential backoff and jitter, and make retried operations idempotent. Set timeouts and honor cancellation.
  • Keyset pagination is faster and more stable than offset pagination for feeds and large tables.
  • Redis suits caches, counters and expiring data; reuse one connection multiplexer and use cache-aside with sensible expiry and invalidation.
  • MongoDB stores flexible, nested documents; strong typing and validation keep the flexibility under control.
  • Default to relational databases and add other stores only when a measured need justifies the cost.

Next: Testing Fundamentals