IFreeSql 事务另类玩法 #322

2881099 · 2020-05-22T16:42:27Z

我们都知道 IFreeSql 以单例注入使用，所以直接使用 IFreeSql curd 做事务。

要么：

fsql.Transaction(() => { ... }); //同线程事务，不能异步

要么：

fsql.Insert(xxx).WithTransaction(tran).ExecuteAffrows(); //每次传递显麻烦

除了这两种事务，还可以选择使用 FreeSql.DbContext、FreeSql.Repository + UnitOfWork 来做事务，他们不受异步限制、并且能减少显示传递事务的麻烦。

条件允许还可以使用 FreeSql.Repository + UnitOfWorkManager 完成更高阶的事务管理操作。

本文内容

另类事务，直接在 IFreeSql 上开刀：

项目中注入 ITransactionFreeSql，项目内也是使用 ITransactionFreeSql

var orgFsql = new FreeSql.FreeSqlBuilder()
  .UseConnectionString(...)
  .Build();

services.AddScoped<ITransactionFreeSql>(() => new TransactionFreeSql(orgFsql));
//注意：Scoped 注入，而不是 Singleton

使用事务：

fsql.Transaction(null); //重写了 orgFsql 的同线程事务

try
{
  await fsql.Insert(xx).ExecuteAffrowsAsync();
  //...
  fsql.Commit();
}
catch
{
  fsql.Rollback();
}

ITransactionFreeSql 的代码定义如下：（相信大多数人都看得懂）

using FreeSql;
using FreeSql.Internal;
using FreeSql.Internal.ObjectPool;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace Your
{
    public interface ITransactionFreeSql : IFreeSql
    {
        void Commit();
        void Roolback();
    }
    class TransactionFreeSql : ITransactionFreeSql
    {
        IFreeSql _originalFsql;
        int _transactionCount;
        DbTransaction _transaction;
        Object<DbConnection> _connection;
        public TransactionFreeSql(IFreeSql fsql)
        {
            _originalFsql = fsql;
        }

        public IAdo Ado => _originalFsql.Ado;
        public IAop Aop => _originalFsql.Aop;
        public ICodeFirst CodeFirst => _originalFsql.CodeFirst;
        public IDbFirst DbFirst => _originalFsql.DbFirst;
        public GlobalFilter GlobalFilter => _originalFsql.GlobalFilter;

        public ISelect<T1> Select<T1>() where T1 : class =>
          _originalFsql.Select<T1>().WithTransaction(_transaction);
        public ISelect<T1> Select<T1>(object dywhere) where T1 : class => Select<T1>().WhereDynamic(dywhere);

        public IDelete<T1> Delete<T1>() where T1 : class =>
          _originalFsql.Delete<T1>().WithTransaction(_transaction);
        public IDelete<T1> Delete<T1>(object dywhere) where T1 : class => Delete<T1>().WhereDynamic(dywhere);

        public IUpdate<T1> Update<T1>() where T1 : class =>
          _originalFsql.Update<T1>().WithTransaction(_transaction);
        public IUpdate<T1> Update<T1>(object dywhere) where T1 : class => Update<T1>().WhereDynamic(dywhere);

        public IInsert<T1> Insert<T1>() where T1 : class =>
          _originalFsql.Insert<T1>().WithTransaction(_transaction);
        public IInsert<T1> Insert<T1>(T1 source) where T1 : class => Insert<T1>().AppendData(source);
        public IInsert<T1> Insert<T1>(T1[] source) where T1 : class => Insert<T1>().AppendData(source);
        public IInsert<T1> Insert<T1>(List<T1> source) where T1 : class => Insert<T1>().AppendData(source);
        public IInsert<T1> Insert<T1>(IEnumerable<T1> source) where T1 : class => Insert<T1>().AppendData(source);

        public IInsertOrUpdate<T1> InsertOrUpdate<T1>() where T1 : class =>
          _originalFsql.InsertOrUpdate<T1>().WithTransaction(_transaction);

        public void Dispose() => TransactionCommitPriv(true);
        public void Transaction(Action handler) => TransactionPriv(null, handler);
        public void Transaction(IsolationLevel isolationLevel, Action handler) => TransactionPriv(isolationLevel, handler);
        void TransactionPriv(IsolationLevel? isolationLevel, Action handler)
        {
            if (_transaction != null)
            {
                _transactionCount++;
                return; //事务已开启
            }
            try
            {
                if(_connection == null ) _connection = _originalFsql.Ado.MasterPool.Get();
                _transaction = isolationLevel == null ? _connection.Value.BeginTransaction() : _connection.Value.BeginTransaction(isolationLevel.Value);
                _transactionCount = 1; //
            }
            catch
            {
                TransactionCommitPriv(false);
                throw;
            }
        }
        public void Commit() => TransactionCommitPriv(true);
        public void Roolback() => TransactionCommitPriv(false);
        void TransactionCommitPriv(bool iscommit)
        {
            if (_transaction == null) return;
            _transactionCount--;
            if (_transactionCount > 0) return; //跳过嵌套的内层事务
            try
            {
                if (iscommit == false) _transaction.Rollback();
                else if (_transactionCount <= 0) _transaction.Commit();
            }
            finally
            {
                if (iscommit == false || _transactionCount <= 0)
                {
                    _originalFsql.Ado.MasterPool.Return(_connection);
                    _connection = null;
                    _transaction = null;
                }
            }
        }
    }
}

liuyl1992 · 2020-09-03T01:48:58Z

为什么不集成这种方式的事务到frees起来源码呢

zhouandke · 2020-11-08T14:55:55Z

在导入excel里的多行记录时, 每一行数据的写入都需要 begin 和 commit/rollback, 以保证每行的写入不会受其他行影响.
在 void TransactionPriv(IsolationLevel? isolationLevel, Action handler) 方法中
_connection = _originalFsql.Ado.MasterPool.Get();
需要改成
if(_connection == null ) _connection = _originalFsql.Ado.MasterPool.Get();

否则, 在默认情况下, 100行数据就会 耗尽数据库连接

luoyunchong · 2020-11-08T14:58:15Z

一行数据，用一个事务？你这是什么操作。

zhouandke · 2020-11-08T15:40:47Z

我这个excel 的一行有很多数据,, 实际上是要写入3张表
只起一个事务的话, 如果第1行成功, 第2行失败, 就会导致第1行数据也 rollback
所以我是每行都起一个事务, 第2行失败与否, 不会影响第1行

2881099 · 2020-11-08T15:48:58Z

@zhouandke 一楼代码已更新，多谢提醒

luoyunchong · 2020-11-10T04:50:30Z

fsql 外部事务demo

通过fsql得到transaction

  using Object<DbConnection> conn = _freeSql.Ado.MasterPool.Get();
  using DbTransaction transaction = await conn.Value.BeginTransactionAsync();

通过外部事务WithTransaction 指定事务

await fsql.Update<xxx>()
  .WithTransaction(指定事务)
  .Set(a => a.Clicks + 1)
  .ExecuteAffrowsAsync();

如下demo


        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="inputDto"></param>
        /// <returns></returns>
        public async Task CreateAsync(CreateGroupDto inputDto)
        {
            bool exist = await _groupRepository.Select.AnyAsync(r => r.Name == inputDto.Name);
            if (exist)
            {
                throw new LinCmsException("分组已存在，不可创建同名分组", ErrorCode.RepeatField);
            }

            LinGroup linGroup = Mapper.Map<LinGroup>(inputDto);

            using Object<DbConnection> conn = _freeSql.Ado.MasterPool.Get();
            using DbTransaction transaction = await conn.Value.BeginTransactionAsync();
            try
            {
                long groupId = await _freeSql.Insert(linGroup).WithTransaction(transaction).ExecuteIdentityAsync();
                List<LinPermission> allPermissions = await _freeSql.Select<LinPermission>().WithTransaction(transaction).ToListAsync();
                List<LinGroupPermission> linPermissions = new List<LinGroupPermission>();
                inputDto.PermissionIds.ForEach(r =>
                {
                    LinPermission pdDto = allPermissions.FirstOrDefault(u => u.Id == r);
                    if (pdDto == null)
                    {
                        throw new LinCmsException($"不存在此权限:{r}", ErrorCode.NotFound);
                    }
                    linPermissions.Add(new LinGroupPermission(groupId, pdDto.Id));
                });

                await _freeSql.Insert<LinGroupPermission>()
                       .WithTransaction(transaction)
                       .AppendData(linPermissions)
                       .ExecuteAffrowsAsync();
                await transaction.CommitAsync();
            }
            catch
            {
                await transaction.RollbackAsync();
                throw;
            }
        }

weilence · 2021-11-15T03:22:54Z

这里代码有问题，应该是1。
如果是0，将导致A事务包含B事务，在B事务commit后，会立即提交全局事务，从而导致错误的结果

cnkker · 2021-11-17T01:12:57Z

这里代码有问题，应该是1。如果是0，将导致A事务包含B事务，在B事务commit后，会立即提交全局事务，从而导致错误的结果

你这是说的哪个有问题？

hajiuxbz · 2021-12-21T04:51:16Z

fsql 外部事务demo

通过fsql得到transaction

  using Object<DbConnection> conn = _freeSql.Ado.MasterPool.Get();
  using DbTransaction transaction = await conn.Value.BeginTransactionAsync();

通过外部事务WithTransaction 指定事务

await fsql.Update<xxx>()
  .WithTransaction(指定事务)
  .Set(a => a.Clicks + 1)
  .ExecuteAffrowsAsync();

如下demo


        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="inputDto"></param>
        /// <returns></returns>
        public async Task CreateAsync(CreateGroupDto inputDto)
        {
            bool exist = await _groupRepository.Select.AnyAsync(r => r.Name == inputDto.Name);
            if (exist)
            {
                throw new LinCmsException("分组已存在，不可创建同名分组", ErrorCode.RepeatField);
            }

            LinGroup linGroup = Mapper.Map<LinGroup>(inputDto);

            using Object<DbConnection> conn = _freeSql.Ado.MasterPool.Get();
            using DbTransaction transaction = await conn.Value.BeginTransactionAsync();
            try
            {
                long groupId = await _freeSql.Insert(linGroup).WithTransaction(transaction).ExecuteIdentityAsync();
                List<LinPermission> allPermissions = await _freeSql.Select<LinPermission>().WithTransaction(transaction).ToListAsync();
                List<LinGroupPermission> linPermissions = new List<LinGroupPermission>();
                inputDto.PermissionIds.ForEach(r =>
                {
                    LinPermission pdDto = allPermissions.FirstOrDefault(u => u.Id == r);
                    if (pdDto == null)
                    {
                        throw new LinCmsException($"不存在此权限:{r}", ErrorCode.NotFound);
                    }
                    linPermissions.Add(new LinGroupPermission(groupId, pdDto.Id));
                });

                await _freeSql.Insert<LinGroupPermission>()
                       .WithTransaction(transaction)
                       .AppendData(linPermissions)
                       .ExecuteAffrowsAsync();
                await transaction.CommitAsync();
            }
            catch
            {
                await transaction.RollbackAsync();
                throw;
            }
        }

这个用完不需要归还连接吗之前自己写的都归还啦

luoyunchong · 2021-12-21T06:19:50Z

加了using

2881099 · 2022-04-07T04:06:34Z

这里代码有问题，应该是1。如果是0，将导致A事务包含B事务，在B事务commit后，会立即提交全局事务，从而导致错误的结果

你这是说的哪个有问题？

        void TransactionCommitPriv(bool iscommit)
        {
            _transactionCount--;
            if (_transactionCount > 0) return; //内层事务，跳过

TinyMaD · 2023-09-07T03:20:29Z

请问这种方式怎么与IBaseRepository 结合使用？创建BaseRepository后需要手工绑定工作单元，但这个TransactionFreeSql类没有UnitOfWork相关的数据，我尝试为TransactionFreeSql类写了一个获取UnitOfWork的属性，但貌似不能满足DbContext事务的使用方式，不知道该怎么写，或者有没有更好实现方式？

public class UowOfFsql : IUnitOfWork
{
    private readonly TransactionFreeSql _fsql;

    public UowOfFsql(TransactionFreeSql fsql)
    {
        _fsql = fsql;
    }

    public IFreeSql Orm => _fsql;
    public IsolationLevel? IsolationLevel { get; set; }

    public DbContext.EntityChangeReport EntityChangeReport { get; }

    public Dictionary<string, object> States { get; }

    public void Commit() => _fsql.Commit();

    public void Dispose() { }

    public DbTransaction GetOrBeginTransaction(bool isCreate = true)
    {
        return _fsql._transaction;
    }

    public void Rollback() => _fsql.Rollback();
}

public class TransactionFreeSql : ITransactionFreeSql
{
    internal DbTransaction _transaction;

    public IUnitOfWork UnitOfWork => new UowOfFsql(this);
}

2881099 · 2023-09-07T07:03:10Z

@TinyMaD

大概如下，fsql、trans、uow、repo 交给 IoC

错误点：public IUnitOfWork UnitOfWork => new UowOfFsql(this);

每次都是 new 新实例了。。

IFreeSql fsql = ...;

using (var trans = new TransactionFreeSql(fsql))
{
    var uow = new UowOfFsql(trans);
    var repo = fsql.GetRepository<T>();
    repo.UnitOfWork = uow;

    repo.Insert(...);

    trans.Commit();
}

如果你需要 uow，应该把 TransacitonFreeSql 定义到 uow 中，如下：

IFreeSql fsql = ...;

//使用者，只需要关心 IUnitOfWork
using (var uow = new UowOfFsql(fsql))
{
    var repo = fsql.GetRepository<T>();
    repo.UnitOfWork = uow;

    repo.Insert(...);

    uow.Commit();
}

public class UowOfFsql : IUnitOfWork
{
    readonly IFreeSql _fsql;
    readonly TransactionFreeSql _trans;

    public UowOfFsql(IFreeSql fsql)
    {
        _fsql = fsql;
        _trans = new TransactionFreeSql(_fsql);
    }

    public IFreeSql Orm => _trans;
    public IsolationLevel? IsolationLevel { get; set; }

    public DbContext.EntityChangeReport EntityChangeReport { get; }

    public Dictionary<string, object> States { get; }

    public void Commit() => _trans.Commit();

    public void Dispose() { }

    public DbTransaction GetOrBeginTransaction(bool isCreate = true)
    {
        return _trans._transaction;
    }

    public void Rollback() => _trans.Rollback();
}

TinyMaD · 2023-09-07T07:45:12Z

@2881099
非常感谢大佬的详细回复，确实TransacitonFreeSql写在UowOfFsql里面更好，是我先入为主了😁谢谢

weilence · 2023-10-01T03:03:20Z

@2881099 有个typo，是Rollback不是Roolback

2881099 mentioned this issue Jun 25, 2020

【建议】新增事务的异步使用方式 #139

Closed

2881099 closed this as completed Aug 25, 2020

2881099 mentioned this issue Sep 16, 2020

只依赖freesql基础包的情况下如何使用事务？ #463

Closed

zhouandke mentioned this issue Nov 8, 2020

FreeSql 能否改成非单例模式并且实现异步事务，目前使用仓储和UnitOfWork模式实现异步事务进行多表操作，代码不够简洁 #520

Closed

luoyunchong added the docs This is a document label Nov 24, 2020

zhangkeyuan mentioned this issue Apr 7, 2022

一个常驻后台的持续性的任务如果持续的提交事务，会阻塞API接口的所有事务提交。 #932

Closed

dotnetcore deleted a comment from cnkker Apr 7, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

IFreeSql 事务另类玩法 #322

IFreeSql 事务另类玩法 #322

2881099 commented May 22, 2020 •

edited

Loading

liuyl1992 commented Sep 3, 2020

zhouandke commented Nov 8, 2020

luoyunchong commented Nov 8, 2020

zhouandke commented Nov 8, 2020

2881099 commented Nov 8, 2020

luoyunchong commented Nov 10, 2020 •

edited

Loading

weilence commented Nov 15, 2021

cnkker commented Nov 17, 2021

hajiuxbz commented Dec 21, 2021

fsql 外部事务demo

通过fsql得到transaction

通过外部事务WithTransaction 指定事务

如下demo

luoyunchong commented Dec 21, 2021

2881099 commented Apr 7, 2022

TinyMaD commented Sep 7, 2023 •

edited

Loading

2881099 commented Sep 7, 2023 •

edited

Loading

TinyMaD commented Sep 7, 2023

weilence commented Oct 1, 2023

IFreeSql 事务另类玩法 #322

IFreeSql 事务另类玩法 #322

Comments

2881099 commented May 22, 2020 • edited Loading

本文内容

liuyl1992 commented Sep 3, 2020

zhouandke commented Nov 8, 2020

luoyunchong commented Nov 8, 2020

zhouandke commented Nov 8, 2020

2881099 commented Nov 8, 2020

luoyunchong commented Nov 10, 2020 • edited Loading

fsql 外部事务demo

通过fsql得到transaction

通过外部事务WithTransaction 指定事务

如下demo

weilence commented Nov 15, 2021

cnkker commented Nov 17, 2021

hajiuxbz commented Dec 21, 2021

fsql 外部事务demo

通过fsql得到transaction

通过外部事务WithTransaction 指定事务

如下demo

luoyunchong commented Dec 21, 2021

2881099 commented Apr 7, 2022

TinyMaD commented Sep 7, 2023 • edited Loading

2881099 commented Sep 7, 2023 • edited Loading

TinyMaD commented Sep 7, 2023

weilence commented Oct 1, 2023

2881099 commented May 22, 2020 •

edited

Loading

luoyunchong commented Nov 10, 2020 •

edited

Loading

TinyMaD commented Sep 7, 2023 •

edited

Loading

2881099 commented Sep 7, 2023 •

edited

Loading