C# - ACID properties in Database Management

In this post, we will see the ACID properties and how we can implement it using C#.
But first of all: what is ACID?
ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. It’s a set of properties that databases strive to achieve to guarantee that database transactions are reliable, even in the face of errors and failures.
In a nutshell, ACID properties are the guiding pillars that ensure trustworthiness in a database system.
Let’s see every single property:

ATOMICITY:
The principle of Atomicity guarantees that each transaction is treated as a single “unit”, which either succeeds completely, or fails completely. If any part of the transaction fails, the entire transaction fails, and the database state is left unchanged.
An example in C# could be a simple bank transaction process.
If a debit is successful but the corresponding credit fails, the entire transaction should fail and roll back to its original state.

using (var transaction = new TransactionScope())
{
    try
    {
        accountService.Debit(sourceAccount, amount);
        accountService.Credit(destinationAccount, amount);
        transaction.Complete();
    }
    catch (Exception ex)
    {
        // Transaction will be rolled back
        throw ex;
    }
}

CONSISTENCY:
Consistency ensures that a transaction brings the database from one valid state to another.
Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.
If a transaction is executed that violates these rules, it is rolled back and an error is raised.

using (var transaction = new TransactionScope())
{
    try
    {
        accountService.Debit(sourceAccount, amount);
        if (destinationAccount.IsClosed)
            throw new InvalidOperationException("Cannot credit a closed account.");
        accountService.Credit(destinationAccount, amount);
        transaction.Complete();
    }
    catch (Exception ex)
    {
        // Transaction will be rolled back
        throw ex;
    }
}

ISOLATION:
Isolation is the property that ensures other operations cannot access or see the data in an intermediate state during a transaction.
This is critical to avoid concurrent transactions from corrupting the database.

using (var transaction = new TransactionScope())
{
    try
    {
        // Retrieve accounts in the context of the current transaction
        var sourceAccount = accountService.GetAccount(sourceAccountId);
        var destinationAccount = accountService.GetAccount(destinationAccountId);
        accountService.Debit(sourceAccount, amount);
        accountService.Credit(destinationAccount, amount);
        transaction.Complete();
    }
    catch (Exception ex)
    {
        // Transaction will be rolled back
        throw ex;
    }
}

DURABILITY:
Durability ensures that once a transaction has been committed, it will remain so, even in the event of a system failure. This is usually achieved by storing the transaction into a transaction log that can be played back in a recovery scenario.
In C#, System.Transactions namespace provides us TransactionScope class which, by default, provides a durable transaction (using the MS-DTC service).

using (var transaction = new TransactionScope())
{
    try
    {
        accountService.Debit(sourceAccount, amount);
        accountService.Credit(destinationAccount, amount);
        transaction.Complete(); // The transaction is committed here. It's durable now.
    }
    catch (Exception ex)
    {
        // Transaction will be rolled back
        throw ex;
    }
}

The following are the entities and the AccountService class used in the previous examples:

public class Account
{
    public int AccountId { get; set; }
    public string AccountHolder { get; set; }
    public decimal Balance { get; set; }
    public bool IsClosed { get; set; }

    // Navigation properties
    public ICollection<Transaction> Transactions { get; set; }
}

public class Transaction
{
    public int TransactionId { get; set; }
    public int SourceAccountId { get; set; }
    public int DestinationAccountId { get; set; }
    public decimal Amount { get; set; }
    public DateTime TransactionDate { get; set; }

    // Navigation properties
    public Account SourceAccount { get; set; }
    public Account DestinationAccount { get; set; }
}

public class MyDbContext : DbContext
{
    public DbSet<Account> Accounts { get; set; }
    public DbSet<Transaction> Transactions { get; set; }

    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options) {}
}


public class AccountService
{
    private readonly MyDbContext _context;

    public AccountService(MyDbContext context)
    {
        _context = context;
    }

    public void Debit(Account account, decimal amount)
    {
        if (account.Balance < amount)
        {
            throw new InvalidOperationException("Insufficient funds.");
        }

        account.Balance -= amount;
        _context.SaveChanges();
    }

    public void Credit(Account account, decimal amount)
    {
        if (account.IsClosed)
        {
            throw new InvalidOperationException("Cannot credit a closed account.");
        }

        account.Balance += amount;
        _context.SaveChanges();
    }

    public Account GetAccount(int accountId)
    {
        return _context.Accounts.SingleOrDefault(a => a.AccountId == accountId);
    }
}

C# – ACID properties in Database Management

Leave a Reply Cancel reply