主页 / dba / 问题 / 259167
Accepted
jean
Asked: 2020-02-08 09:11:22 +0800 CST

对于游标内的每次迭代，是否可以独立地发生回滚/提交？

  • 772

我们有这个会计解决方案生态系统，基本上，它有两张表：

dbo.AccountFluxRecord
(
   Id,
   TotalCredits,
   TotalDebits,
   CreditMinusDebit,
   Application (the application used to insert the record)
)

dbo.AccountFluxRecordDetail
(
   Id,
   AccountFluxId,       (FK to above table)
   Type,                (Credit or Debit)
   Value,
   CreditBankAccountId, (nullable FK)
   DebtBankAccountId    (nullable FK)
)

我们有一些应用程序使用它，并且都使用存储过程来插入新记录。

SP_InsertAccountFluxRecord

我们将列表操作系统详细信息作为自定义用户表类型传递给 SP。它进行了大量验证，以确保所有记录至少有一个贷方和一个借方，并且贷方减去债务的总和为零。它还验证 BankAccountId 是否对每种操作都有效。

几年来，它在数百名客户中完美运行，每周插入数百万条记录。我们为大多数客户托管数据库，但其中一些坚持他们的数据必须在内部托管。我们可以接受，因为我们可以获得一个开放的维护渠道。

今天，我们遇到了其中一位客户自托管数据库的问题。

我们发现了十几个“坏”记录。这些记录缺少债务详细信息，这是不可能的，因为该特定应用程序从一对借记/贷记详细信息创建了一个 AccountFluxRecord，其中一个是另一个的镜像。如果没有一对有效的银行账户，它甚至无法接受用户输入，但我们发现在这些不良记录中 DebtBankAccountId 字段为空。

我们保留所有操作的日志，日志反映了错误记录按原样插入，没有删除或更新。Obs：我们确实使用快照事务模式来避免我们过去遇到的一些死锁问题。

TLDR 我得到了十几个无效记录，它们是由一个 SP 插入的，用于阻止插入无效记录，就好像所有应用程序和存储过程验证都被绕过了一样。下面有一个片段显示了如何在 SP 中处理验证。

IF (@SumDetailDebit <> @SumDetailCredit) 
BEGIN
    set @Mesage = @ErrorPrefix + N'TOTAL DEBIT IS DIFFERENT FROM CREDIT!';
    THROW 50000, @Mesage ,1
END

问题：在进行一些验证后，在 SP 内有一个游标为每次迭代插入详细记录。即使我们没有在循环内使用事务控制，是否可以提交一个插入而回滚另一个插入？

sql-server-2017

1 个回答

  1. Best Answer

    经过大量测试，我找到了答案。是的，它可以，因为您运行它是 AD HOC，没有事务控制。

    create table dbo.TOBEINSERTEDINTO
(
  ID int not null
)
GO

/*********************************************************************/
/***   Test scenario                                               ***/
/*********************************************************************/
create procedure TestCursor
as
begin
    declare @id int

    declare cursor_test cursor read_only fast_forward
    for
    select * from (values(1),(2),(3),(null)) as src(id) order by id desc

    open cursor_test

    declare @status int = 0 

        while @status = 0
        begin
            fetch next from cursor_test into @id

            set @status = @@fetch_status

            if (@status = 0) 
            begin

                if (@id is null)
                begin
                    throw 50000, n'!!!!!!!!! id cannot be NULL !!!!!!!!!!' ,1
                end

                insert into dbo.TOBEINSERTEDINTO(ID) values(@id)
            end
        end

    close cursor_test
    deallocate cursor_test
end;
GO

/*********************************************************************/
/***   Calling inside a transaction, with a try..catch can be ok   ***/
/*********************************************************************/

begin tran
begin try
    Exec TestCursor
end try

begin catch
    if @@trancount > 0  
    rollback
end catch

if @@trancount > 0  
    commit transaction; 

select * from dbo.TOBEINSERTEDINTO

GO

/*********************************************************************/
/***   Lack of transaction control leads to inconsistence          ***/
/*********************************************************************/
exec TestCursor
GO

select * from dbo.TOBEINSERTEDINTO
GO

    该应用程序获得了可靠的事务控制。

    问题的产生是因为有人篡改了基地。我猜有人运行了一个配置文件并试图模仿一些应用程序调用。

    • 0

相关问题