1

We have this ecosystem of accounting solutions and, basically, it got two tables:

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)
)

We have a few applications using it and all use a stored procedure to insert new records.

SP_InsertAccountFluxRecord

We pass the list os details to the SP as a custom user table type. It makes lots of validations to ensure all records have at least one credit and one debit and the sum of credits minus debts to be zero. It also validates if BankAccountId is valid for each kind of operation.

It's working flawlessly, for a couple of years now, in hundreds of customers, inserting many millions of records each week. We host the database for most of our customers but some of them insist their data must be hosted in house. We are Ok with that since we can get a open channel for maintenance.

Today we got an issue with one of those customers self-hosting their database.

We have found a dozen "bad" records. Those records are missing the debt detail, that is impossible since that particular application creates an AccountFluxRecord from a pair of debit/credit details, one is the mirror of the other. It cannot even accept a user input without a valid pair of bank accounts but we found the DebtBankAccountId field to be null in those bad records.

We keep a log of all operations, the log reflects the bad records are inserted as they are, there are no deletes or updates over them. Obs: We do use snapshot transaction mode to avoid some deadlock problems we got in the past.

TLDR I got a dozen invalid records, inserted by an SP made to block invalid records to be inserted at all, as if all application and Stored Procedure validation where bypassed. Below there's a snippet showing how validations are handled inside the SP.

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

Question: Inside an SP there is a cursor inserting a detail record for each iteration, after making some validations. Can one insert be commited and other be backrolled even if we are not using transaction control inside the loop?

1
  • No, both inserts are in the same transaction so it will be committed or rolledback
    – zsheep
    Feb 7 '20 at 17:15
0

After testing a lot I found the answer. Yes, it can since you run it AD HOC, without transaction control.

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

The application got a solid transaction control.

The problem originated because someone was tampering with the base. I guess someone put a profile to run and tried to mimic some app calls.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.