Início / dba / Perguntas / 259167
Accepted
jean
Asked: 2020-02-08 09:11:22 +0800 CST

Pode ocorrer um rollback/commit, independente, para cada iteração dentro de um cursor?

  • 772

Temos esse ecossistema de soluções contábeis e, basicamente, ele ficou com duas tabelas:

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

Temos alguns aplicativos usando e todos usam um procedimento armazenado para inserir novos registros.

SP_InsertAccountFluxRecord

Passamos a lista de detalhes para o SP como um tipo de tabela de usuário personalizado. Ele faz muitas validações para garantir que todos os registros tenham pelo menos um crédito e um débito e a soma dos créditos menos os débitos seja zero. Também valida se BankAccountId é válido para cada tipo de operação.

Está funcionando perfeitamente, há alguns anos, em centenas de clientes, inserindo muitos milhões de registros a cada semana. Hospedamos o banco de dados para a maioria de nossos clientes, mas alguns deles insistem que seus dados devem ser hospedados internamente. Estamos bem com isso, pois podemos obter um canal aberto para manutenção.

Hoje tivemos um problema com um desses clientes auto-hospedando seu banco de dados.

Encontramos uma dúzia de registros "ruins". Esses registros estão faltando os detalhes da dívida, o que é impossível, pois esse aplicativo específico cria um AccountFluxRecord a partir de um par de detalhes de débito/crédito, um é o espelho do outro. Ele não pode nem aceitar uma entrada de usuário sem um par válido de contas bancárias, mas descobrimos que o campo DebtBankAccountId é nulo nesses registros inválidos.

Mantemos um registro de todas as operações, o registro reflete os registros ruins que são inseridos como estão, não há exclusões ou atualizações sobre eles. Obs: Usamos o modo de transação de snapshot para evitar alguns problemas de deadlock que tivemos no passado.

TLDR Eu tenho uma dúzia de registros inválidos, inseridos por um SP feito para bloquear registros inválidos a serem inseridos, como se todas as validações de aplicativos e procedimentos armazenados fossem ignoradas. Abaixo há um trecho mostrando como as validações são tratadas dentro do SP.

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

Descrição: Dentro de um SP existe um cursor inserindo um registro de detalhe para cada iteração, após realizar algumas validações. Uma inserção pode ser confirmada e outra pode ser retrovertida mesmo se não estivermos usando o controle de transação dentro do loop?

sql-server-2017

1 respostas

  1. Best Answer

    Depois de muito testar encontrei a resposta. Sim, pode desde que você o execute AD HOC, sem controle de transação.

    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

    A aplicação obteve um sólido controle de transações.

    O problema se originou porque alguém estava adulterando a base. Acho que alguém colocou um perfil para executar e tentou imitar algumas chamadas de aplicativos.

    • 0

relate perguntas