Net Core中数据库事务隔离详解——以Dapper和Mysq

• Net Core中数据库事务隔离详解——以Dapper和Mysql为例
  • 事务隔离级别
    • 准备工作
    • Read uncommitted 读未提交
    • Read committed 读取提交内容
    • Repeatable read (可重读)
    • Serializable 序列化
  • 总结

事务隔离级别

.NET Core中的IDbConnection接口提供了BeginTransaction方法作为执行事务，BeginTransaction方法提供了两个重载，一个不需要参数BeginTransaction()默认事务隔离级别为RepeatableRead;另一个BeginTransaction(IsolationLevel il)可以根据业务需求来修改事务隔离级别。由于Dapper是对IDbConnection的扩展，所以Dapper在执行增删除改查时所有用到的事务需要由外部来定义。事务执行时与数据库之间的交互如下：

2017-12-19-22-43-23

从WireShark抓取的数据包来看程序和数据交互步骤依次是：建立连接-->设置数据库隔离级别-->告诉数据库一个事务开始-->执行数据增删查改-->提交事务-->断开连接

准备工作

准备数据库：Mysql （笔者这里是：MySql 5.7.20 社区版）

创建数据库并创建数据表，创建数据表的脚本如下：

CREATE TABLE `posts` (
  `Id` varchar(255) NOT NULL ,
  `Text` longtext NOT NULL,
  `CreationDate` datetime NOT NULL,
  `LastChangeDate` datetime NOT NULL,
  `Counter1` int(11) DEFAULT NULL,
  `Counter2` int(11) DEFAULT NULL,
  `Counter3` int(11) DEFAULT NULL,
  `Counter4` int(11) DEFAULT NULL,
  `Counter5` int(11) DEFAULT NULL,
  `Counter6` int(11) DEFAULT NULL,
  `Counter7` int(11) DEFAULT NULL,
  `Counter8` int(11) DEFAULT NULL,
  `Counter9` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

创建.NET Core Domain类：

[Table("Posts")]
public class Post
{
    [Key]
    public string Id { get; set; }
    public string Text { get; set; }
    public DateTime CreationDate { get; set; }
    public DateTime LastChangeDate { get; set; }
    public int? Counter1 { get; set; }
    public int? Counter2 { get; set; }
    public int? Counter3 { get; set; }
    public int? Counter4 { get; set; }
    public int? Counter5 { get; set; }
    public int? Counter6 { get; set; }
    public int? Counter7 { get; set; }
    public int? Counter8 { get; set; }
    public int? Counter9 { get; set; }

}

具体怎样使用Dapper，请看上篇。

Read uncommitted 读未提交

允许脏读，即不发布共享锁，也不接受独占锁。意思是：事务A可以读取事务B未提交的数据。

优点：查询速度快

缺点：容易造成脏读，如果事务A在中途回滚

以下为执行脏读的测试代码片断：

public static void RunDirtyRead(IsolationLevel transaction1Level,IsolationLevel transaction2Level)
{
    var id = Guid.NewGuid().ToString();
    using (var connection1 = new MySqlConnection(connStr))
    {
        connection1.Open();
        Console.WriteLine("transaction1 {0} Start",transaction1Level);
        var transaction1 = connection1.BeginTransaction(transaction1Level);
        Console.WriteLine("transaction1 插入数据 Start");
        var sql = "insert into posts (id,text,CreationDate,LastChangeDate) values(@Id,@Text,@CreationDate,@LastChangeDate)";
        var detail1 = connection1.Execute(sql,
        new Post
        {
            Id = id,
            Text = Guid.NewGuid().ToString(),
            CreationDate = DateTime.Now,
            LastChangeDate = DateTime.Now
        },
            transaction1);
        Console.WriteLine("transaction1 插入End 返回受影响的行：{0}", detail1);
        using (var connection2 = new MySqlConnection(connStr))
        {
            connection2.Open();
            Console.WriteLine("transaction2 {0} Start",transaction2Level);
            var transaction2 = connection2.BeginTransaction(transaction2Level);
            Console.WriteLine("transaction2 查询数据 Start");
            var result = connection2.QueryFirstOrDefault<Post>("select * from posts where id=@Id", new { id = id }, transaction2);
            //如果result为Null 则程序会报异常
            Console.WriteLine("transaction2 查询结事 返回结果：Id={0},Text={1}", result.Id, result.Text);
            transaction2.Commit();
            Console.WriteLine("transaction2 {0} End",transaction2Level);
        }
        transaction1.Rollback();
        Console.WriteLine("transaction1 {0} Rollback ",transaction1Level);
    }

}

1、当执行RunDirtyRead(IsolationLevel.ReadUncommitted,IsolationLevel.ReadUncommitted),即事务1和事务2都设置为ReadUncommitted时结果如下：

2017-12-22-22-06-49

当事务1回滚以后，数据库并没有事务1添加的数据，所以事务2获取的数据是脏数据。

2、当执行RunDirtyRead(IsolationLevel.Serializable,IsolationLevel.ReadUncommitted)，即事务1隔离级别为Serializble，事务2的隔离级别设置为ReadUncommitted，结果如下：

2017-12-22-22-07-28

3、当执行RunDirtyRead(IsolationLevel.ReadUncommitted,IsolationLevel.ReadCommitted);,即事务1隔离级别为ReadUncommitted，事务2的隔离级别为Readcommitted，结果如下：

2017-12-22-22-08-13

结论：当事务2（即取数据事务）隔离级别设置为ReadUncommitted，那么不管事务1隔离级别为哪一种，事务2都能将事务1未提交的数据得到；但是测试结果可以看出当事务2为ReadCommitted则获取不到事务1未提交的数据从而导致程序异常。

Read committed 读取提交内容

这是大多数数据库默认的隔离级别，但是，不是MySQL的默认隔离级别。读取数据时保持共享锁，以避免脏读，但是在事务结束前可以更改数据。

优点：解决了脏读的问题

缺点：一个事务未结束被另一个事务把数据修改后导致两次请求的数据不一致

测试重复读代码片断：

public static void RunRepeatableRead(IsolationLevel transaction1Level, IsolationLevel transaction2Level)
{
    using (var connection1 = new MySqlConnection(connStr))
    {
        connection1.Open();
        var id = "c8de065a-3c71-4273-9a12-98c8955a558d";
        Console.WriteLine("transaction1 {0} Start", transaction1Level);
        var transaction1 = connection1.BeginTransaction(transaction1Level);
        Console.WriteLine("transaction1 第一次查询开始");
        var sql = "select * from posts where id=@Id";
        var detail1 = connection1.QueryFirstOrDefault<Post>(sql, new { Id = id }, transaction1);
        Console.WriteLine("transaction1 第一次查询结束，结果：Id={0}，Counter1={1}", detail1.Id, detail1.Counter1);
        using (var connection2 = new MySqlConnection(connStr))
        {
            connection2.Open();
            Console.WriteLine("transaction2  {0} Start", transaction2Level);
            var transaction2 = connection2.BeginTransaction(transaction2Level);
            var updateCounter1=(detail1.Counter1 ?? 0) + 1;
            Console.WriteLine("transaction2  开始修改Id={0}中Counter1的值修改为：{1}", id,updateCounter1);
            var result = connection2.Execute(
                "update posts set Counter1=@Counter1 where id=@Id",
                new { Id = id, Counter1 = updateCounter1 },
                transaction2);
            Console.WriteLine("transaction2 修改完成 返回受影响行：{0}", result);
            transaction2.Commit();
            Console.WriteLine("transaction2 {0} End", transaction2Level);
        }
        Console.WriteLine("transaction1 第二次查询 Start");
        var detail2 = connection1.QueryFirstOrDefault<Post>(sql, new { Id = id }, transaction1);
        Console.WriteLine("transaction1 第二次查询 End 结果：Id={0},Counter1={1}", detail2.Id, detail2.Counter1);
        transaction1.Commit();
        Console.WriteLine("transaction1 {0} End", transaction1Level);
    }
}

在事务1中detail1中得到的Counter1为1，事务2中将Counter1的值修改为2，事务1中detail2得到的Counter1的值也会变为2

下面分几种情况来测试：

1、当事务1和事务2都为ReadCommitted时，结果如下：

2017-12-21-21-29-41 2017-12-22-22-08-38

2、当事务1和事务2隔离级别都为RepeatableRead时，执行结果如下：

2017-12-22-22-08-53

3、当事务1隔离级别为RepeatableRead，事务2隔离级别为ReadCommitted时执行结果如下：

2017-12-22-22-09-09

4、当事务1隔离级别为ReadCommitted，事务2隔离级别为RepeatableRead时执行结果如下：

2017-12-22-22-09-30

结论：当事务1隔离级别为ReadCommitted时数据可重复读，当事务1隔离级别为RepeatableRead时可以不可重复读，不管事务2隔离级别为哪一种不受影响。

注：在RepeatableRead隔离级别下虽然事务1两次获取的数据一致，但是事务2已经是将数据库中的数据进行了修改，如果事务1对该条数据进行修改则会对事务2的数据进行覆盖。

<a id="markdown-repeatable-read-可重读" name="repeatable-read-可重读"></a>

Repeatable read (可重读)

这是MySQL默认的隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行（目标数据行不会被修改）。

优点：解决了不可重复读和脏读问题

缺点：幻读

测试幻读代码

 
public static void RunPhantomRead(IsolationLevel transaction1Level, IsolationLevel transaction2Level)
{
    using (var connection1 = new MySqlConnection(connStr))
    {
        connection1.Open();
        Console.WriteLine("transaction1 {0} Start", transaction1Level);
        var transaction1 = connection1.BeginTransaction(transaction1Level);
        Console.WriteLine("transaction1 第一次查询数据库 Start");
        var detail1 = connection1.Query<Post>("select * from posts").ToList();
        Console.WriteLine("transaction1 第一次查询数据库 End 查询条数：{0}", detail1.Count);
        using (var connection2 = new MySqlConnection(connStr))
        {
            connection2.Open();
            Console.WriteLine("transaction2 {0} Start", transaction2Level);
            var transaction2 = connection2.BeginTransaction(transaction2Level);
            Console.WriteLine("transaction2 执行插入数据 Start");
            var sql = "insert into posts (id,text,CreationDate,LastChangeDate) values(@Id,@Text,@CreationDate,@LastChangeDate)";
            var entity = new Post
            {
                Id = Guid.NewGuid().ToString(),
                Text = Guid.NewGuid().ToString(),
                CreationDate = DateTime.Now,
                LastChangeDate = DateTime.Now
            };
            var result = connection2.Execute(sql, entity, transaction2);
            Console.WriteLine("transaction2 执行插入数据 End 返回受影响行：{0}", result);
            transaction2.Commit();
            Console.WriteLine("transaction2 {0} End", transaction2Level);
        }
        Console.WriteLine("transaction1 第二次查询数据库 Start");
        var detail2 = connection1.Query<Post>("select * from posts").ToList();
        Console.WriteLine("transaction1 第二次查询数据库 End 查询条数：{0}", detail2.Count);
        transaction1.Commit();
        Console.WriteLine("transaction1 {0} End", transaction1Level);
    }
}

分别对几种情况进行测试：

1、事务1和事务2隔离级别都为RepeatableRead,结果如下：

2017-12-22-22-09-46

2、事务1和事务2隔离级别都为Serializable,结果如下：

2017-12-22-22-10-02

3、当事务1的隔离级别为Serializable,事务2的隔离级别为RepeatableRead时，执行结果如下：

2017-12-22-22-10-18

4、当事务1的隔离级别为RepeatableRead,事务2的隔离级别为Serializable时，执行结果如下：

2017-12-22-22-10-32

结论：当事务隔离级别为RepeatableRead时虽然两次获取数据条数相同，但是事务2是正常将数据插入到数据库当中的。当事务1隔离级别为Serializable程序异常，原因接下来将会讲到。

Serializable 序列化

这是最高的事务隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。

优点：解决幻读

缺点：在每个读的数据行上都加了共享锁，可能导致大量的超时和锁竞争

当执行RunPhantomRead(IsolationLevel.Serializable, IsolationLevel.Serializable)或执行RunPhantomRead(IsolationLevel.Serializable, IsolationLevel.RepeatableRead)时代码都会报异常，是因为Serializable隔离级别下强制事务以串行方式执行，由于这里是一个主线程上第一个事务未完时执行了第二个事务，但是第二个事务必须等到第一个事务执行完成后才参执行，所以就会导致程序报超时异常。这里将代码作如下修改：

using (var connection1 = new MySqlConnection(connStr))
{
    connection1.Open();
    Console.WriteLine("transaction1 {0} Start", transaction1Level);
    var transaction1 = connection1.BeginTransaction(transaction1Level);
    Console.WriteLine("transaction1 第一次查询数据库 Start");
    var detail1 = connection1.Query<Post>("select * from posts").ToList();
    Console.WriteLine("transaction1 第一次查询数据库 End 查询条数：{0}", detail1.Count);
    Thread thread = new Thread(new ThreadStart(() =>
    {
        using (var connection2 = new MySqlConnection(connStr))
        {
            connection2.Open();
            Console.WriteLine("transaction2 {0} Start", transaction2Level);
            var transaction2 = connection2.BeginTransaction(transaction2Level);
            Console.WriteLine("transaction2 执行插入数据 Start");
            var sql = "insert into posts (id,text,CreationDate,LastChangeDate) values(@Id,@Text,@CreationDate,@LastChangeDate)";
            var entity = new Post
            {
                Id = Guid.NewGuid().ToString(),
                Text = Guid.NewGuid().ToString(),
                CreationDate = DateTime.Now,
                LastChangeDate = DateTime.Now
            };
            var result = connection2.Execute(sql, entity, transaction2);
            Console.WriteLine("transaction2 执行插入数据 End 返回受影响行：{0}", result);
            transaction2.Commit();
            Console.WriteLine("transaction2 {0} End", transaction2Level);
        }
    }));
    thread.Start();
    //为了证明两个事务是串行执行的，这里让主线程睡5秒
    Thread.Sleep(5000);
    Console.WriteLine("transaction1 第二次查询数据库 Start");
    var detail2 = connection1.Query<Post>("select * from posts").ToList();
    Console.WriteLine("transaction1 第二次查询数据库 End 查询条数：{0}", detail2.Count);
    transaction1.Commit();
    Console.WriteLine("transaction1 {0} End", transaction1Level);
}

执行结果如下：

2017-12-22-22-11-02 2017-12-22-22-11-13

结论：当事务1隔离级别为Serializable时对后面的事务的增删改改操作进行强制排序。避免数据出错造成不必要的麻烦。

注：在.NET Core中IsolationLevel枚举值中还提供了另外三种隔离级别：Chaos、Snapshot、Unspecified由于这种事务隔离级别MySql不支持设置时会报异常：

2017-12-20-21-31-13

总结

本节通过Dapper对MySql中事务的四种隔离级别下进行测试，并且指出事务之间的相互关系和问题以供大家参考。

1、事务1隔离级别为ReadUncommitted时，可以读取其它任何事务隔离级别下未提交的数据

2、事务1隔离级别为ReadCommitted时，不可以读取其它事务未提交的数据，但是允许其它事务对数据表进行查询、添加、修改和删除;并且可以将其它事务增删改重新获取出来。

3、事务1隔离级别为RepeatableRead时，不可以读取其它事务未提交的数据，但是允许其它事务对数据表进行查询、添加、修改和删除;但是其它事务的增删改不影响事务1的查询结果

4、事务1隔离级别为Serializable时，对其它事务对数据库的修改（增删改）强制串行处理。

	脏读	重复读	幻读
Read uncommitted	会	会	会
Read committed	不会	会	会
Repeatable read	不会	不会	会
Serializable	不会	不会	不会

作者：xdpie 原文：http://www.xdpie.com/2017/12/28/net-core-dapper-transaction/