Dúvida: Transação com múltiplas procedures

Question

Olá Pessoal,

Bem estou com dúvida quanto ao uso do Begin Transation, RollBack e Commit.

Eu leio na internet sobre seu uso, assim como suas facilidades, entretanto isso não está sendo tão simples assim para mim.

Eu preciso realizar 3 operações e apenas concluir se todas as operações forem executadas com sucesso, caso contrário tudo volta ao normal.

Sendo assim eu criei 4 procedures:

·         InsereTabela

·         AtualizaMovimento

·         CriaConta

·         IniciaTransacao

Justamente na última procedure eu inicio a execução das 3 dessas operações:

create procedure InciaTransacao

Begin transaction

          exec InsereTabela

          exec AtualizaMovimento

          exec CriaConta         



          if @@Error <> 0

                rollback

          else

                commit transaction 

 Isso está correto?

Quando ocorre um erro nada é modificado, o que teoricamente seria o correto. Mas o estranho, é que quando ocorre o erro tanto um print ou select que eu coloco antes @@ERROR ou rollback não é executado.

Vocês poderiam também avaliar se a  estrutura que eu montei também está correta?

E finalmente, nas tabelas eu tenho primary key com identity(1,1), mesmo com o rollback o valor do id não deveria voltar?

Obrigado.

Answer 1

Olá,

Qual o seu SQL Server?

Dentro das procedures

          exec
 InsereTabela

          exec
 AtualizaMovimento

          exec
 CriaConta  

existe algum commit? Se os códigos das procedures possuim dependência o ideal seria realizar tudo na mesma procedure.

Só para adiantar, os identitys não voltam quando acontece um erro.

Abraços

---

Demétrio Silva

Answer 2

Olá Demétrio,

É o SQL SERVER 2000.

Não tem commit nas outras procedures, só na procedure IniciaTransacao

Answer 3

Matt_nab,

Já tentou fazer o que eu sugeri?

Abraços

---

Demétrio Silva

Answer 4

Então Demétrio, o duro que eu não queria colocar tudo em uma só procedure, pois eu quero reutilizar as PROCEDURES separadamente em outros módulos do programa!

Answer 5

Ola Matt,

Da maneira que vc esta fazendo pode da certo sim, voce só precisa ficar atento na hora de verificar se houve erro

create procedure InciaTransacao

Begin transaction

          exec InsereTabela
          if @@error<> 0 
            goto trataerro
  
          exec AtualizaMovimento
          if @@error<> 0 
            goto trataerro

          exec CriaConta         
          if @@error<> 0 
            goto trataerro

commit transaction 

trataerro:
print 'erro...'
rollback

att.

Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!

Answer 6

Olá,

Não entendi o problema.

create procedure teste as
begin

    set nocount on
    
    select 1/0

end

begin transaction

exec teste
       
print 'ok'

if @@error <> 0
begin
    rollback
end
else
begin
    commit transaction
end

Abraços

---

Demétrio Silva

Answer 7

Demetrio

Este seu script tem um erro
o @@error esta sendo atribuido em relacao ao PRINT e não a proc.

O @@error é referente ao ultimo comando...

Att,
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!

Answer 8

Usando o TRY CATCH como tratamento de erro nao acontece isto..

---

MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!

Answer 9

Matt,

Mas você pretender chamar uma procedure dentro da outra?

---

Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA

Answer 10

Demétrio,

Olha só que interessante, seu exemplo funcionou mas olha nesse caso:

 

create procedure b as
begin
  set nocount on 
  select cast('a' as int)
end
go 

create procedure a as
begin
  begin transaction
    exec b
    print 'ok'
    
    if @@error <> 0
       rollback
    else
       commit transaction
end
go

exec a

Ai não ocorre o Print.

 

 

Answer 11

Olá Marcelo!

Também não funcionou, ele faz o rollback mas sem mensagem de erro.

Answer 12

Matt,

Por isso eu perguntei agora a pouco se você desejava executar uma procedure dentro da outra!!!

Foi justamente o que eu imaginei.

---

Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA

Answer 13

Ola matt qual a versao do seu SQL?

se for 2000 vc precisa ficar atendo ao erro no nivel de batch (o sql aborta naquele ponto) ja no 2005 isto nao ocorre mais


att.
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!

Answer 14

Olá Marcelo!

Sim é o 2000.

Tem como fazer alguma coisa quanto a esse nível de batch?

Answer 15

Olá Junior,

Desculpe, eu não tinha visto a mensagem.

É isso mesmo, eu vou reutilizar procedures para um novo módulo. Nesse módulo, as 3 operações devem ser concluídos ou tudo volta como estava.

Answer 16

Não posso usar TRY por que é o SQL2000!

Answer 17

Tem sim.

Valeu Marcelo.

Abraços

---

Demétrio Silva

Answer 18

Demétrio, você viu que alterando aquele seu exemplo para um erro de tipos de dados, o PRINT não executa?

create procedure b as
begin
  set nocount on 
  select cast('a' as int)
end
go 

create procedure a as
begin
  begin transaction
    exec b
    print 'ok'
    
    if @@error <> 0
       rollback
    else
       commit transaction
end
go

exec a

Answer 19

Matt,

Minha procedure é diferente da sua. Meu exemplo só era uma SP e o seu são duas.

Abraços

---

Demétrio Silva

Answer 20

Ola Matt,
Então existe um ppt muito bom sobre tratamento de erros do PASS vou localizar e te passo.

mas olha este teste de erro de BATCH LEVEL

CREATE PROCEDURE DemoSemErro

AS

SELECT 'Nao existe erro aqui'

go

CREATE PROCEDURE DemoBatchAbort AS

DECLARE @Err as INT

SELECT 'Primeiro select'

declare @Param int

exec DemoSemErro @Param

SET @Err = @@Error

SELECT 'Segunto select'

IF @Err <> 0 RETURN @Err

go

-- executa os testes

declare @Ret int

exec @Ret = DemoBatchAbort

select @@Error 

select @Ret

O sql vai abortar e nao vai exibir o @@error e nem o @ret (isto nao acontece mais no 2005) o sql 2000 aborta naquele ponto...

Att.
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!

Answer 21

Matt,

Realmente:

SELECT 1/0
IF @@ERROR <> 0
BEGIN
	PRINT 'ERRO 1/0'
END   
ELSE
BEGIN
	PRINT 'OK'
END   

GO

SELECT CAST('A' AS INT)
IF @@ERROR <> 0
BEGIN
	PRINT 'ERRO CAST A INT'
END   
ELSE
BEGIN
	PRINT 'OK'
END   

Não sei te explicar porque um aparece e outro não. Até o level do erro é o mesmo.

Vamos esperar para ver se alguem do fórum explica.

Abraços

---

Demétrio Silva

Answer 22

Olá Marcelo!

Entendi o seu exemplo, mas uma curiosidade, como estou usando o SQL2000, o processo está sendo abortado.

Mas o que acontece com um processo abortado dentro de um BEGIN TRANSACTION?

UM ROLLBACK AUTOMATICO!?

Por quê pra mim, parece que ocorre o ROLLBACK por que os Insert's na tabela são apagados.

Abraços.

Answer 23

Matt,

Realmente:

SELECT 1/0

IF @@ERROR <> 0

BEGIN

	PRINT 'ERRO 1/0'

END   

ELSE

BEGIN

	PRINT 'OK'

END   



GO



SELECT CAST('A' AS INT)

IF @@ERROR <> 0

BEGIN

	PRINT 'ERRO CAST A INT'

END   

ELSE

BEGIN

	PRINT 'OK'

END   

Não sei te explicar porque um aparece e outro não. Até o level do erro é o mesmo.

Vamos esperar para ver se alguem do fórum explica.

Abraços

---

Demétrio Silva

Pois é Demétrio,

Isso é muito estranho. Até agora só percebi isso em Erros de Tipos de Dados.

Answer 24

Ola matt.

exato o SQL 2000  aborta e reverte a transacao.

OBS.
sinceramente acho que nao tem outro modo p/ tratamento erro no level de batch ao ser ser o DBA ou analista visualisar o erro executando no Query Anbalyzer, mas nunca testei na aplicacao nao sei se um ON ERROR na aplicacao vai tratar....

att.
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!

Answer 25

Matt

este erro nao acontece no tipo de dados, no meu exemplo acima enviei um parametro que nao existia e aconteu o erro..

OBS. Como ja disse isto nao ocorre no 2005. (exibe o erro mas nao aborta)

Att.
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!

Answer 26

Matt

este erro nao acontece no tipo de dados, no meu exemplo acima enviei um parametro que nao existia e aconteu o erro..

OBS. Como ja disse isto nao ocorre no 2005. (exibe o erro mas nao aborta)

Att.
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!

Olá Marcelo,

Sim eu entendi o seu exemplo, mas o Tipo de dados pelo qual me referi era na minha procedure. :)

Answer 27

Ola matt.

exato o SQL 2000  aborta e reverte a transacao.

OBS.
sinceramente acho que nao tem outro modo p/ tratamento erro no level de batch ao ser ser o DBA ou analista visualisar o erro executando no Query Anbalyzer, mas nunca testei na aplicacao nao sei se um ON ERROR na aplicacao vai tratar....

att.
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!

Olá Marcelo,


Realmente muito obrigado pela explicação! Ótimo que o mesmo faça o rollback automático, pois essa era a minha principal preocupação.

Bem, só tenho a agradecer a você e todos que ajudaram. Essa comunidade é excelente!

Obrigado,

Matheus.

Answer 28

Ola Matt.

Obrigado pelo retorno....

att.
Marcelo Fernandes

---

MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!