脏读问题

[sukney]

两个执行的都一样的语句，第一个脏读了，读了两条一样的数据，第二个正常只读了一条数据，老大这个怎么能避免啊，感觉防不胜防啊。

[2881099]

是不是用了工作单元，事务未提交？

第二个方法是你自己封装的，不知道你怎么封装的。

[sukney]

方法上面有事务，现在把读的代码 放一块了，第一个读的参数不一样，第二到4参数一样， 2和3结果一样两条记录，实际数据库这参数只一条记录，4只一条记录是正常的这个类的插入修改了。 重整理了下，执行的情况如下 1.记录条数0，代码 var rechargs1 = await _myRechargeRepository .AsTable(oldOrder.MakeDate) .Where(x => x.BllId == oldOrder.Id && x.VipUserId == oldOrder.VipUserId && x.Mark == 2) .ToListAsync() 2.记录条数2，代码 var rechargs2 = await _myRechargeRepository.Select .AsTable(oldOrder.MakeDate) .Where(x => x.BllId == oldOrder.Id && x.VipUserId == oldOrder.VipUserId && x.Mark == 0) .ToListAsync(); 3.记录条数2，代码 var rechargs3 = await _myRechargeRepository.Select .AsTable(oldOrder.MakeDate) .Where(x => x.BllId == oldOrder.Id && x.VipUserId == oldOrder.VipUserId && x.Mark == 0) .ToListAsync(); 4.记录条数1 代码 var rechargs4 = await _myRechargeRepository.GetRepository<MyRecharge>().Select .AsTable(oldOrder.MakeDate) .Where(x => x.BllId == oldOrder.Id && x.VipUserId == oldOrder.VipUserId && x.Mark == 0) .ToListAsync();

[sukney]

工作单元代码 public class MyRechargeRepository : RepositoryDefault<MyRecharge>, IMyRechargeRepository {

 public MyRechargeRepository(IFreeSql fsql, UnitOfWorkManager uowManger, IAuthUser authUser) : base(uowManger?.Orm ?? fsql, uowManger, authUser)
 {
     uowManger?.Binding(this);
 }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="makeDate"></param>
 /// <param name="vipUserId"></param>
 /// <param name="amount"></param>
 /// <param name="orderId"></param>
 /// <param name="orgId"></param>
 /// <param name="mark"></param>
 /// <param name="remark"></param>
 /// <returns></returns>
 public async Task Add(DateTime makeDate, long vipUserId, decimal amount, long orderId, BllType bllType, long orgId, ushort mark, string remark)
 {
     if (vipUserId<=0)
     {
         throw new ResultException(22, "余额记录：会员ID不能为空");
     }
     if (orderId <= 0)
     {
         throw new ResultException(22, "积分记录：订单ID不能为空");
     }
     await this
          .AsTable(makeDate).InsertAsync(new MyRecharge
          {
              VipUserId = vipUserId,
              Amount = amount,
              OrgId = orgId,
              Mark = mark,
              BllType= bllType,
              CreatedTime = makeDate,
              BllId = orderId,
              Remark = remark,
          });
 }

}

[2881099]

最终都会执行SQL，且都会执行 fsql.Aop.CommndBefore 这个事件

可以在这个事件内，debug DbCmmand 数据库命令对象，例如 Cmmand.CommandText 和 Command.DbTransaction 的值

[sukney]

找到原因了是 astable的问题，几次astable 他union了两个同样的表

SELECT a."id", a."created_by", a."created_by_name", a."created_time", a."tenant_id", a."org_id", a."amount", a."bll_id", a."bll_type", a."vip_user_id", a."remark", a."mark" FROM "vip_my_recharge_202401" a WHERE (a."tenant_id" = 7905557214855237) AND (a."bll_id" = 8054737756291142 AND a."vip_user_id" = 8054737756291141 AND a."mark" = 2) end================SQL================*** || ||end 1.记录条数0，代码 var rechargs1 = await _myRechargeRepository .AsTable(oldOrder.MakeDate) .Where(x => x.BllId == oldOrder.Id && x.VipUserId == oldOrder.VipUserId && x.Mark == 2) .ToListAsync() 01:05:55 || Information || || begin================(Tenant2)--TENANT--SQL--(Tenant2)================***

SELECT * from (SELECT a."id", a."created_by", a."created_by_name", a."created_time", a."tenant_id", a."org_id", a."amount", a."bll_id", a."bll_type", a."vip_user_id", a."remark", a."mark" FROM "vip_my_recharge_202401" a WHERE (a."tenant_id" = 7905557214855237) AND (a."bll_id" = 8054737756291142 AND a."vip_user_id" = 8054737756291141 AND a."mark" = 0)) ftb

UNION ALL

SELECT * from (SELECT a."id", a."created_by", a."created_by_name", a."created_time", a."tenant_id", a."org_id", a."amount", a."bll_id", a."bll_type", a."vip_user_id", a."remark", a."mark" FROM "vip_my_recharge_202401" a WHERE (a."tenant_id" = 7905557214855237) AND (a."bll_id" = 8054737756291142 AND a."vip_user_id" = 8054737756291141 AND a."mark" = 0)) ftb end================SQL================*** || ||end 2.记录条数2，代码 var rechargs2 = await _myRechargeRepository.Select .AsTable(oldOrder.MakeDate) .Where(x => x.BllId == oldOrder.Id && x.VipUserId == oldOrder.VipUserId && x.Mark == 0) .ToListAsync(); 01:05:55 || Information || || begin================(Tenant2)--TENANT--SQL--(Tenant2)================***

SELECT * from (SELECT a."id", a."created_by", a."created_by_name", a."created_time", a."tenant_id", a."org_id", a."amount", a."bll_id", a."bll_type", a."vip_user_id", a."remark", a."mark" FROM "vip_my_recharge_202401" a WHERE (a."tenant_id" = 7905557214855237) AND (a."bll_id" = 8054737756291142 AND a."vip_user_id" = 8054737756291141 AND a."mark" = 0)) ftb

UNION ALL

SELECT * from (SELECT a."id", a."created_by", a."created_by_name", a."created_time", a."tenant_id", a."org_id", a."amount", a."bll_id", a."bll_type", a."vip_user_id", a."remark", a."mark" FROM "vip_my_recharge_202401" a WHERE (a."tenant_id" = 7905557214855237) AND (a."bll_id" = 8054737756291142 AND a."vip_user_id" = 8054737756291141 AND a."mark" = 0)) ftb end================SQL================*** || ||end 3.记录条数2，代码 var rechargs3 = await _myRechargeRepository.Select .AsTable(oldOrder.MakeDate) .Where(x => x.BllId == oldOrder.Id && x.VipUserId == oldOrder.VipUserId && x.Mark == 0) .ToListAsync(); 01:05:55 || Information || || begin================(Tenant2)--TENANT--SQL--(Tenant2)================***

SELECT a."id", a."created_by", a."created_by_name", a."created_time", a."tenant_id", a."org_id", a."amount", a."bll_id", a."bll_type", a."vip_user_id", a."remark", a."mark" FROM "vip_my_recharge_202401" a WHERE (a."tenant_id" = 7905557214855237) AND (a."bll_id" = 8054737756291142 AND a."vip_user_id" = 8054737756291141 AND a."mark" = 0) end================SQL================*** || ||end 4.记录条数1 代码 var rechargs4 = await _myRechargeRepository.GetRepository<MyRecharge>().Select .AsTable(oldOrder.MakeDate) .Where(x => x.BllId == oldOrder.Id && x.VipUserId == oldOrder.VipUserId && x.Mark == 0) .ToListAsync();

上面是语句，下面是我的astable的扩展，老大帮看看，怎么每次astable的清除以前的astable呢。 ///

/// ///

/// /// /// /// public static ISelect<T> AsTable<T>(this ISelect<T> select, DateTime date) { date = new DateTime(date.Year, date.Month, 1); if (date > DateTime.Now.AddDays(1).Date) { return select; } else if (date < DateTime.Parse(DbBeginDate.BegDate)) { return select; } return select.AsTable((t, s) => { if (t == typeof(T)) { return $"{s}_{date.ToString("yyyyMM")}"; } return $"{s}"; }); }

[sukney]

怎么让astable 只在一个 执行的 语句生效，不要在整工作单元生效。

[2881099]

多次 AsTable 是 UnionAll 机制，文档上写了。

虽然比较隐晦，但是我们debug都是基于SQL监视就不难发现问题了。

固定的机制改动会对其他使用者造成影响。

建议您自己扩展一个方法，保存表名，确定最后只执行一次astable

[sukney]

能加个手工清除astable的方法吗？ 如 select.ClearAllTable() ，select.ClearTable(“tableName”) 把这个能暴露出来_tableRules.Clear(); public static class MyFreeSqlExtensions { public static ISelect<T1> DisableGlobalAsTable<T1>(this ISelect<T1> that) { var s0p = that as Select0Provider; if (s0p != null) s0p._tableRules.Clear(); return that; } public static IInsert<T1> DisableGlobalAsTable<T1>(this IInsert<T1> that) where T1 : class { var s0p = that as InsertProvider<T1>; if (s0p != null) s0p._tableRule = null; return that; } public static IUpdate<T1> DisableGlobalAsTable<T1>(this IUpdate<T1> that) where T1 : class { var s0p = that as UpdateProvider<T1>; if (s0p != null) s0p._tableRule = null; return that; } public static IDelete<T1> DisableGlobalAsTable<T1>(this IDelete<T1> that) where T1 : class { var s0p = that as DeleteProvider<T1>; if (s0p != null) s0p._tableRule = null; return that; } public static IInsertOrUpdate<T1> DisableGlobalAsTable<T1>(this IInsertOrUpdate<T1> that) where T1 : class { var s0p = that as InsertOrUpdateProvider<T1>; if (s0p != null) s0p._tableRule = null; return that; } } 感谢老大，使用了您提供的扩展方法可以了