postgresql EFCore 悲观锁与乐观锁

1. 安装依赖包

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.1.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.1.1">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.1.1" />
  </ItemGroup>

2. 定义实体

using System;
using System.Collections.Generic;
using System.Text;

namespace pessimisticAndoptimistic
{
    public class Test
    {
        public int id { get; set; }

        public string name { get; set; }

        public string address { get; set; }
    }
}

3. 定义Config

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace pessimisticAndoptimistic
{
    public class TestConfig : IEntityTypeConfiguration<Test>
    {
        public void Configure(EntityTypeBuilder<Test> builder)
        {
            builder.ToTable("testlock");
            builder.Property(x => x.address);
            builder.Property(x => x.name).IsRequired();
        }
    }
}

4. 定义Context

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.EntityFrameworkCore;
namespace pessimisticAndoptimistic
{
    public class PgDbContext:DbContext
    {
        public DbSet<Test> Tests { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);

            optionsBuilder.UseNpgsql("User ID=postgres;Password=postgres;Host=127.0.0.1;Port=5432;Database=postgres;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.ApplyConfiguration(new TestConfig());
        }
    }
}

5. 数据库迁移
   运行 ADD-Migration init 然后运行Update-Databse init
6. 测试悲观锁
   需要说明的是锁需要与事务一起使用，执行select for update 会获取一个update锁，锁住这一行，等到执行完update后，会释放锁。
   当这一行被锁住后，其他需要更新这一行的任务将会等待锁释放。

using System;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using System.Threading;

namespace pessimisticAndoptimistic
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("请输入你的地址");

            string address = Console.ReadLine();

            using (PgDbContext cxt = new PgDbContext())
            using (var tran = cxt.Database.BeginTransaction())
            {
                Test test = cxt.Tests.FromSql("select * from testlock where id=1 for update").Single();//获取锁
                if (string.IsNullOrEmpty(test.address))
                {
                    test.address = address;
                    Thread.Sleep(8000);
                    cxt.SaveChanges();//释放锁
                    tran.Commit();
                }
                else
                {
                    Console.WriteLine("已经有了地址");
                }

                Console.ReadKey();
            }



            Console.WriteLine("Hello World!");
        }
    }
}

7. 测试乐观锁
   需要在判断并发冲突的字段上添加IsConcurrencyToken

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace pessimisticAndoptimistic
{
    public class TestConfig : IEntityTypeConfiguration<Test>
    {
        public void Configure(EntityTypeBuilder<Test> builder)
        {
            builder.ToTable("testlock");
            builder.Property(x => x.address).IsConcurrencyToken();
            builder.Property(x => x.name).IsRequired();
        }
    }
}

在执行SaveChanges的时候捕获异常:DbUpdateConcurrencyException

using System;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using System.Threading;

namespace pessimisticAndoptimistic
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("请输入你的地址");

            string address = Console.ReadLine();

            using (PgDbContext cxt = new PgDbContext())
            {
                Test test = cxt.Tests.Where(x=>x.id==1).Single();//获取锁
                if (string.IsNullOrEmpty(test.address))
                {
                    test.address = address;
                    Thread.Sleep(8000);
                    try
                    {
                        cxt.SaveChanges();//释放锁
                    }
                    catch (DbUpdateConcurrencyException ex)
                    {

                        Console.WriteLine("并发冲突");
                    }
                    
                }
                else
                {
                    Console.WriteLine("已经有了地址");
                }

                Console.ReadKey();
            }



            Console.WriteLine("Hello World!");
        }
    }
}