Optimistic Concurrency with PostgreSQL source
We have gone through the concept of optimistic concurrency, and how it is used in environments with low contention for data and why it improves performance. Many libraries for accessing data sources include implementation of this technique. Let’s see how it works against a PostgreSQL data source.
DevOps MPP Course Source
- This lab is used in course DevOps200.9x: Architecting Distributed Cloud Applications - Module 6.
Pre-requisites:
This lab has a dependency on the following technologies. These will need to be installed on a development machine to complete the lab.
Lab Tasks:
- Set up the environment
- Handling optimistic concurrency
Estimated Lab Time:
- approx. 40 minutes
Task 1:Set up the environment
-
Scaffold a new .NET Core console project with:
dotnet new console --name db_writer
-
Install the required nuget packages for connecting to PostgreSQL:
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL && \ dotnet add package Microsoft.EntityFrameworkCore.Design
-
The EF Core .NET Command Line Tools are installed by manually editing the *.csproj file. Add Microsoft.EntityFrameworkCore.Tools.DotNet as a DotNetCliToolReference. See sample project below.
<Project Sdk="Microsoft.NET.Sdk"> <PropertyGroup> <OutputType>Exe</OutputType> <TargetFramework>netcoreapp1.1</TargetFramework> </PropertyGroup> <ItemGroup> <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="1.1.2" /> <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="1.1.0" /> </ItemGroup> <ItemGroup> <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.0.1" /> </ItemGroup> </Project>
-
And add the design package with:
dotnet add package Microsoft.EntityFrameworkCore.Design
-
Create a new file to contain the class representing the entity,
InventoryRecord
using System; namespace DbWriter { public class InventoryRecord { public Guid ItemId { get; set; } public string ItemName { get; set; } public int AvailableItems { get; set; } } }
-
Create a new file to contain the DBContext class
InventoryDbContext
. Entity Framework supports the concept of optimistic concurrency - a property on your entity is designated as a concurrency token, and EF detects concurrent modifications by checking whether that token has changed since the entity was read. You can read more about this in the EF docs.Although applications can update concurrency tokens themselves, we frequently rely on the database automatically updating a column on update - a “last modified” timestamp, an SQL Server rowversion, etc. Unfortunately PostgreSQL doesn’t have such auto-updating columns - but there is one feature that can be used for concurrency token. All PostgreSQL have a set of implicit and hidden system columns, among which xmin holds the ID of the latest updating transaction. Since this value automatically gets updated every time the row is changed, it is ideal for use as a concurrency token.
using System; using Microsoft.EntityFrameworkCore; namespace DbWriter { public class InventoryDbContext: DbContext { protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseNpgsql("Host=localhost;Database=inventory;Username=postgres;Password=postgresspasswrod"); } public DbSet<InventoryRecord> Inventory { get; set; } protected override void OnModelCreating(ModelBuilder builder) { builder.Entity<InventoryRecord>() .ForNpgsqlUseXminAsConcurrencyToken() .HasKey(m => m.ItemId); base.OnModelCreating(builder); } } }
-
We will instantiate a PostgreSQL Docker container. First pull the postgres image
docker pull library/postgres
-
And instantiate the container and attach the stdout to see the logs on the database:
docker run -it --name postgres -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgrespassword -e POSTGRES_DB=inventory -p 5432:5432 library/postgres
-
Add the EF migration to create the table and run the migration
dotnet ef migrations add InitialCreate && \ dotnet ef database update
-
Connect to the container, and run psql utility to verify the table is created
docker exec -it -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgddpassword -e POSTGRES_DB=inventory postgres /bin/bashInventoryRecord
Task 2: Handling optimistic concurrency
-
Update the program.cs with the following code:
using System; using System.Collections.Generic; using System.Linq; using System.Threading; using System.Threading.Tasks; using Microsoft.EntityFrameworkCore; namespace DbWriter { class Program { static void Main(string[] args) { var itemIds = new List<Guid>(); using (var dbContext = new InventoryDbContext()) { for (var i = 0; i < 10; i++) { var id = Guid.NewGuid(); var random = new Random(); itemIds.Add(id); dbContext.Inventory.Add(new InventoryRecord { ItemId = id, ItemName = $"Item{(char)('A' + (char)i)}", AvailableItems = 10 }); } var count = dbContext.SaveChanges(); Console.WriteLine($"Initialized the database with {count} items. Their names are:"); foreach (var item in dbContext.Inventory) { Console.WriteLine(item.ItemName); } } var updateOnTheSecond = new AutoResetEvent(false); var task1 = Task.Factory.StartNew(() => { using (var dbContext = new InventoryDbContext()) { var i = 0; foreach (var item in dbContext.Inventory) { item.AvailableItems = 100; if (i++ == 4) { updateOnTheSecond.Set(); } } SaveAndCatch(dbContext); } }); var task2 = Task.Factory.StartNew(() => { using (var dbContext = new InventoryDbContext()) { foreach (var item in dbContext.Inventory) { item.AvailableItems = 200; } updateOnTheSecond.WaitOne(); SaveAndCatch(dbContext); } }); Task.WaitAll(task1, task2); using (var dbContext = new InventoryDbContext()) { Console.WriteLine($"The data in the table is now:"); foreach (var item in dbContext.Inventory) { Console.WriteLine($"Item: {item.ItemName}, available: {item.AvailableItems}"); } } } private static void SaveAndCatch(InventoryDbContext dbContext) { try { dbContext.SaveChanges(); } catch (DbUpdateConcurrencyException exception) { foreach (var entry in exception.Entries) { if (entry.Entity is InventoryRecord) { // Using a NoTracking query means we get the entity but it is not tracked by the context // and will not be merged with existing entities in the context. var databaseEntity = dbContext.Inventory.AsNoTracking() .Single(p => p.ItemId == ((InventoryRecord)entry.Entity).ItemId); var databaseEntry = dbContext.Entry(databaseEntity); foreach (var property in entry.Metadata.GetProperties()) { var proposedValue = entry.Property(property.Name).CurrentValue; var originalValue = entry.Property(property.Name).OriginalValue; var databaseValue = databaseEntry.Property(property.Name).CurrentValue; // Decide how to handle the changes, currently just display for demo purposes. Console.WriteLine($"Proposed: {proposedValue}, original: {originalValue}, database: {databaseValue}"); // Update original values to // entry.Property(property.Name).OriginalValue = databaseEntry.Property(property.Name).CurrentValue; } } else { throw new NotSupportedException("Don't know how to handle concurrency conflicts for " + entry.Metadata.Name); } } // Retry the save operation as desired. // dbContext.SaveChanges(); } } } }
-
Run the project and see the results:
dotnet run
Summary
In this lab you completed the following tasks:
- Set up the environment
- Handled optimistic concurrency