Casos do Dia a Dia – Procedures x Rollback

E aí galera,

Hoje vou falar sobre um assunto que gera muitas dúvidas quando estamos desenvolvendo códigos T-SQL: utilização de Procedures e Rollback.

Mas antes, não pode faltar aquele pequeno desafio:

“Quantas linhas serão retornadas no script abaixo?”

OBS: Pense na resposta antes de rodar o script de cada um dos exemplos desse post, se roubar toma um cascudo virtual hein xD

USE Traces

CREATE TABLE Teste_Rollback (
	Id INT IDENTITY,
	Data DATETIME
)

GO
CREATE PROCEDURE stpTeste_Rollback
AS
BEGIN	
	INSERT INTO Teste_Rollback
	VALUES(GETDATE())
END
GO

-- TESTE ROLLBACK
EXEC stpTeste_Rollback
 
BEGIN TRAN
	EXEC stpTeste_Rollback

	EXEC stpTeste_Rollback
ROLLBACK

EXEC stpTeste_Rollback

-- RESULTADO
SELECT * FROM Teste_Rollback

USE Traces

CREATE TABLE Teste_Rollback (

Id INT IDENTITY,

Data DATETIME

)

CREATE PROCEDURE stpTeste_Rollback

BEGIN

INSERT INTO Teste_Rollback

VALUES(GETDATE())

END

-- TESTE ROLLBACK

EXEC stpTeste_Rollback

BEGIN TRAN

EXEC stpTeste_Rollback

ROLLBACK

EXEC stpTeste_Rollback

-- RESULTADO

SELECT * FROM Teste_Rollback

A) Vai executar com sucesso e irá retornar 1 linha

B) Vai executar com sucesso e irá retornar 2 linhas

C) Vai executar com sucesso e irá retornar 3 linhas

D) Vai executar com sucesso e irá retornar 4 linhas

E) Vai gerar um ERRO após o ROLLBACK e nenhuma linha será retornada

A seguir vou fazer algumas explicações e darei a resposta desse desafio. #gogogo

Desafio Inicial:

Nesse desafio, eu começo criando uma tabela e depois crio uma procedure bem simples que insere um registro com a DATA e HORA do GETDATE().

Feito isso, iniciamos a brincadeira executando a procedure uma vez. Nesse momento, temos um registro inserido na tabela (ID 1).

Nesse caso, eu NÃO utilizei o BEGIN TRAN para iniciar uma transação. Com isso, por padrão o SQL Server considera cada comando individual como uma transação e faz o COMMIT automaticamente também. Ou seja, por baixo dos panos o SQL Server faz o BEGIN TRAN / COMMIT quando nós não especificamos explicitamente.

OBS: Para mais detalhes sobre o BEGIN TRAN, consultar o link abaixo:

https://docs.microsoft.com/pt-br/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-ver15

Logo, o registro ID 1 já foi inserido e comitado na tabela.

Depois eu abro uma nova transação explicitamente com o BEGIN TRAN e executo a procedure mais duas vezes. Com isso, vamos ter mais dois registros inseridos na tabela (ID 2 e 3). Contudo, logo depois eu faço um ROLLBACK que irá desfazer essas duas transações. Repare que o registro ID 1 não será desfeito, pois ele já foi inserido e comitado antes do BEGIN TRAN.

Por fim, executo a procedure mais uma vez para inserir o último registro (ID 4).

Então a RESPOSTA do desafio será:

B) Vai executar com sucesso e irá retornar 2 linhas

“Ahh Luiz, esse desafio aí foi muito fácil cara!!!”

Beleza, então vamos complicar um pouco mais =)

Mais Exemplos:

Agora vamos fazer vários outros exemplos e ir aumentando a dificuldade OK.

Primeiro, vamos criar quatro tabelas bem simples com um campo ID e outro com uma Data.

USE Traces

CREATE TABLE Teste_Rollback_1 (
	Id INT,
	Data DATETIME
)

CREATE TABLE Teste_Rollback_2 (
	Id INT,
	Data DATETIME
)

CREATE TABLE Teste_Rollback_3 (
	Id INT,
	Data DATETIME
)

CREATE TABLE Teste_Rollback_4 (
	Id INT,
	Data DATETIME
)

GO

USE Traces

CREATE TABLE Teste_Rollback_1 (

Id INT,

Data DATETIME

)

CREATE TABLE Teste_Rollback_2 (

Id INT,

Data DATETIME

)

CREATE TABLE Teste_Rollback_3 (

Id INT,

Data DATETIME

)

CREATE TABLE Teste_Rollback_4 (

Id INT,

Data DATETIME

)

Depois vamos criar quatro procedures, sendo que elas irão executar em cascata. Segue abaixo um resumo:

A Procedure 1 faz um INSERT na Tabela 1 e depois executa a Procedure 2;
A Procedure 2 faz um INSERT na Tabela 2 e depois executa a Procedure 3;
A Procedure 3 faz um INSERT na Tabela 3 e depois executa a Procedure 4;
Por fim, a Procedure 4 faz um INSERT na Tabela 4 e termina a execução;

GO
CREATE PROCEDURE stpTeste_Rollback_4
AS
BEGIN	
	INSERT INTO Teste_Rollback_4
	VALUES(4, GETDATE())
END
GO

CREATE PROCEDURE stpTeste_Rollback_3
AS
BEGIN	
	INSERT INTO Teste_Rollback_3
	VALUES(3, GETDATE())

	EXEC stpTeste_Rollback_4
END
GO

CREATE PROCEDURE stpTeste_Rollback_2
AS
BEGIN	
	INSERT INTO Teste_Rollback_2
	VALUES(2, GETDATE())

	EXEC stpTeste_Rollback_3
END
GO

CREATE PROCEDURE stpTeste_Rollback_1
AS
BEGIN	
	INSERT INTO Teste_Rollback_1
	VALUES(1, GETDATE())

	EXEC stpTeste_Rollback_2
END
GO

CREATE PROCEDURE stpTeste_Rollback_4

BEGIN

INSERT INTO Teste_Rollback_4

VALUES(4, GETDATE())

END

CREATE PROCEDURE stpTeste_Rollback_3

BEGIN

INSERT INTO Teste_Rollback_3

VALUES(3, GETDATE())

EXEC stpTeste_Rollback_4

END

CREATE PROCEDURE stpTeste_Rollback_2

BEGIN

INSERT INTO Teste_Rollback_2

VALUES(2, GETDATE())

EXEC stpTeste_Rollback_3

END

CREATE PROCEDURE stpTeste_Rollback_1

BEGIN

INSERT INTO Teste_Rollback_1

VALUES(1, GETDATE())

EXEC stpTeste_Rollback_2

END

Exemplo 1:

Agora, vamos executar a Procedure 1 e fazer um SELECT em cada uma das quatro tabelas e conferir o resultado.

EXEC stpTeste_Rollback_1

/*
(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)
*/

-- EXEMPLO 1 - RESULTADO
SELECT * FROM Teste_Rollback_1
SELECT * FROM Teste_Rollback_2
SELECT * FROM Teste_Rollback_3
SELECT * FROM Teste_Rollback_4

EXEC stpTeste_Rollback_1

(1 row affected)

-- EXEMPLO 1 - RESULTADO

SELECT * FROM Teste_Rollback_1

SELECT * FROM Teste_Rollback_2

SELECT * FROM Teste_Rollback_3

SELECT * FROM Teste_Rollback_4

Até aqui tudo certo, todas as procedures irão executar com sucesso e inserir um registro em cada uma das tabelas.

Está muito EASY ainda, bora dificultar mais!!!

OBS: Ao final de cada um dos exemplos, vou executar um TRUNCATE TABLE para limpar cada uma das tabelas.

-- Limpa o Resultado
TRUNCATE TABLE Teste_Rollback_1
TRUNCATE TABLE Teste_Rollback_2
TRUNCATE TABLE Teste_Rollback_3
TRUNCATE TABLE Teste_Rollback_4

-- Limpa o Resultado

TRUNCATE TABLE Teste_Rollback_1

TRUNCATE TABLE Teste_Rollback_2

TRUNCATE TABLE Teste_Rollback_3

TRUNCATE TABLE Teste_Rollback_4

Exemplo 2:

Nesse segundo exemplo, vou alterar apenas a Procedure 4 e colocar uma divisão por zero para gerar um ERRO de propósito após o INSERT na Tabela 4.

GO
ALTER PROCEDURE stpTeste_Rollback_4
AS
BEGIN	
	INSERT INTO Teste_Rollback_4
	VALUES(4, GETDATE())

	SELECT 1/0
END
GO

ALTER PROCEDURE stpTeste_Rollback_4

BEGIN

INSERT INTO Teste_Rollback_4

VALUES(4, GETDATE())

SELECT 1/0

END

Agora, eu faço as seguintes perguntas:

“Algum registro será inserido mesmo com esse erro na última procedure?“

“Ele irá desfazer algum dos INSERTs das outras procedures que já foram executadas anteriormente?”

“O registro será inserido na Tabela 4? Ou será desfeito?“

Vamos executar a Procedure 1 novamente e validar!!!

EXEC stpTeste_Rollback_1

/*
(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

Msg 8134, Level 16, State 1, Procedure stpTeste_Rollback_4, Line 7 &#91;Batch Start Line 150]
Divide by zero error encountered.
*/

-- EXEMPLO 2 - RESULTADO
SELECT * FROM Teste_Rollback_1
SELECT * FROM Teste_Rollback_2
SELECT * FROM Teste_Rollback_3
SELECT * FROM Teste_Rollback_4
GO

EXEC stpTeste_Rollback_1

(1 row affected)

Msg 8134, Level 16, State 1, Procedure stpTeste_Rollback_4, Line 7 [Batch Start Line 150]

Divide by zero error encountered.

-- EXEMPLO 2 - RESULTADO

SELECT * FROM Teste_Rollback_1

SELECT * FROM Teste_Rollback_2

SELECT * FROM Teste_Rollback_3

SELECT * FROM Teste_Rollback_4

Mesmo com o erro no final, todos os registros foram inseridos nas tabelas. Como expliquei anteriormente, quando não utilizamos o BEGIN TRAN explicitamente, o SQL Server irá considerar cada um dos INSERTs como uma transação e fazer o COMMIT logo em seguida. Com isso, quando acontece o erro da divisão por zero, todos os INSERTs já foram comitados nas tabelas e não serão desfeitos.

Vamos limpar as tabelas e seguir para o próximo exemplo! #next

-- Limpa o Resultado
TRUNCATE TABLE Teste_Rollback_1
TRUNCATE TABLE Teste_Rollback_2
TRUNCATE TABLE Teste_Rollback_3
TRUNCATE TABLE Teste_Rollback_4
GO

-- Limpa o Resultado

TRUNCATE TABLE Teste_Rollback_1

TRUNCATE TABLE Teste_Rollback_2

TRUNCATE TABLE Teste_Rollback_3

TRUNCATE TABLE Teste_Rollback_4

Exemplo 3:

Vamos alterar novamente a Procedure 4, mas dessa vez vou colocar um BEGIN TRAN no início dela. Se ela executar sem erros, no final será feito o COMMIT, caso contrário irá fazer o ROLLBACK.

GO
ALTER PROCEDURE stpTeste_Rollback_4
AS
BEGIN
	BEGIN TRAN
		INSERT INTO Teste_Rollback_4
		VALUES(1, GETDATE())

		SELECT 1/0

	IF(@@ERROR = 0)
		COMMIT
	ELSE
		ROLLBACK
END
GO

ALTER PROCEDURE stpTeste_Rollback_4

BEGIN

BEGIN TRAN

INSERT INTO Teste_Rollback_4

VALUES(1, GETDATE())

SELECT 1/0

IF(@@ERROR = 0)

COMMIT

ELSE

ROLLBACK

END

“E agora, será que o registro da Tabela 4 será inserido? E os outros registros?“

Vamos executar a Procedure 1 novamente e validar!!!

EXEC stpTeste_Rollback_1

/*
(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

Msg 8134, Level 16, State 1, Procedure stpTeste_Rollback_4, Line 7 &#91;Batch Start Line 200]
Divide by zero error encountered.
*/

-- EXEMPLO 3 – RESULTADO
SELECT * FROM Teste_Rollback_1
SELECT * FROM Teste_Rollback_2
SELECT * FROM Teste_Rollback_3
SELECT * FROM Teste_Rollback_4
GO

EXEC stpTeste_Rollback_1

(1 row affected)

Msg 8134, Level 16, State 1, Procedure stpTeste_Rollback_4, Line 7 [Batch Start Line 200]

Divide by zero error encountered.

-- EXEMPLO 3 – RESULTADO

SELECT * FROM Teste_Rollback_1

SELECT * FROM Teste_Rollback_2

SELECT * FROM Teste_Rollback_3

SELECT * FROM Teste_Rollback_4

Eita! Dessa vez, o registro não foi inserido na Tabela 4, pois ele estava dentro de uma transação (BEGIN TRAN) e ela foi desfeita (ROLLBACK). Com isso, tudo que foi executado após o BEGIN TRAN será desfeito!

“Caramba Luiz, acho que estou conseguindo entender agora! Que legal!”

Calma que eu ainda vou complicar um pouco mais!!! #next

-- Limpa o Resultado
TRUNCATE TABLE Teste_Rollback_1
TRUNCATE TABLE Teste_Rollback_2
TRUNCATE TABLE Teste_Rollback_3
TRUNCATE TABLE Teste_Rollback_4
GO

-- Limpa o Resultado

TRUNCATE TABLE Teste_Rollback_1

TRUNCATE TABLE Teste_Rollback_2

TRUNCATE TABLE Teste_Rollback_3

TRUNCATE TABLE Teste_Rollback_4

Exemplo 4:

Dessa vez, vou alterar a Procedure 4 para gerar o ERRO no final, mas sem utilizar o BEGIN TRAN. Além disso, vou incluir o BEGIN TRAN na Procedure 3.

GO
ALTER PROCEDURE stpTeste_Rollback_4
AS
BEGIN
	INSERT INTO Teste_Rollback_4
	VALUES(1, GETDATE())

	SELECT 1/0
END

GO
ALTER PROCEDURE stpTeste_Rollback_3
AS
BEGIN
	BEGIN TRAN
		INSERT INTO Teste_Rollback_3
		VALUES(3, GETDATE())

		EXEC stpTeste_Rollback_4

	IF(@@ERROR = 0)
		COMMIT
	ELSE
		ROLLBACK	
END
GO

ALTER PROCEDURE stpTeste_Rollback_4

BEGIN

INSERT INTO Teste_Rollback_4

VALUES(1, GETDATE())

SELECT 1/0

END

ALTER PROCEDURE stpTeste_Rollback_3

BEGIN

BEGIN TRAN

INSERT INTO Teste_Rollback_3

VALUES(3, GETDATE())

EXEC stpTeste_Rollback_4

IF(@@ERROR = 0)

COMMIT

ELSE

ROLLBACK

END

Vamos executar a Procedure 1 e conferir o resultado!

EXEC stpTeste_Rollback_1

/*
(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

Msg 8134, Level 16, State 1, Procedure stpTeste_Rollback_4, Line 7 &#91;Batch Start Line 261]
Divide by zero error encountered.
*/

-- Valida o Resultado Final
SELECT * FROM Teste_Rollback_1
SELECT * FROM Teste_Rollback_2
SELECT * FROM Teste_Rollback_3
SELECT * FROM Teste_Rollback_4
GO

EXEC stpTeste_Rollback_1

(1 row affected)

Msg 8134, Level 16, State 1, Procedure stpTeste_Rollback_4, Line 7 [Batch Start Line 261]

Divide by zero error encountered.

-- Valida o Resultado Final

SELECT * FROM Teste_Rollback_1

SELECT * FROM Teste_Rollback_2

SELECT * FROM Teste_Rollback_3

SELECT * FROM Teste_Rollback_4

Dessa vez, a transação (BEGIN TRAN) foi aberta no início da Procedure 3. Depois, foi executado o INSERT nas Tabelas 3 e 4. Porém, como aconteceu um erro no final da Procedure 4, todas as operações que foram feitas após o BEGIN TRAN serão desfeitas. Por esse motivo, apenas as Tabelas 3 e 4 ficaram vazias.

-- Limpa o Resultado
TRUNCATE TABLE Teste_Rollback_1
TRUNCATE TABLE Teste_Rollback_2
TRUNCATE TABLE Teste_Rollback_3
TRUNCATE TABLE Teste_Rollback_4
GO

-- Limpa o Resultado

TRUNCATE TABLE Teste_Rollback_1

TRUNCATE TABLE Teste_Rollback_2

TRUNCATE TABLE Teste_Rollback_3

TRUNCATE TABLE Teste_Rollback_4

Exemplo 5:

Acredito que nesse momento você já deve ter entendido a ideia. Então vamos para o último exemplo!

Aqui vou remover o BEGIN TRAN da Procedure 3 e colocar no início da Procedure 1.

GO
ALTER PROCEDURE stpTeste_Rollback_3
AS
BEGIN
	INSERT INTO Teste_Rollback_3
	VALUES(3, GETDATE())

	EXEC stpTeste_Rollback_4
END

GO
ALTER PROCEDURE stpTeste_Rollback_1
AS
BEGIN
	BEGIN TRAN
		INSERT INTO Teste_Rollback_1
		VALUES(1, GETDATE())

		EXEC stpTeste_Rollback_2

	IF(@@ERROR = 0)
		COMMIT
	ELSE
		ROLLBACK	
END
GO

ALTER PROCEDURE stpTeste_Rollback_3

BEGIN

INSERT INTO Teste_Rollback_3

VALUES(3, GETDATE())

EXEC stpTeste_Rollback_4

END

ALTER PROCEDURE stpTeste_Rollback_1

BEGIN

BEGIN TRAN

INSERT INTO Teste_Rollback_1

VALUES(1, GETDATE())

EXEC stpTeste_Rollback_2

IF(@@ERROR = 0)

COMMIT

ELSE

ROLLBACK

END

“Bora executar o nosso último teste. Você já sabe qual será o resultado dessa vez?”

EXEC stpTeste_Rollback_1

/*
(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

Msg 8134, Level 16, State 1, Procedure stpTeste_Rollback_4, Line 8 &#91;Batch Start Line 321]
Divide by zero error encountered.
*/

-- Valida o Resultado Final
SELECT * FROM Teste_Rollback_1
SELECT * FROM Teste_Rollback_2
SELECT * FROM Teste_Rollback_3
SELECT * FROM Teste_Rollback_4
GO

EXEC stpTeste_Rollback_1

(1 row affected)

Msg 8134, Level 16, State 1, Procedure stpTeste_Rollback_4, Line 8 [Batch Start Line 321]

Divide by zero error encountered.

-- Valida o Resultado Final

SELECT * FROM Teste_Rollback_1

SELECT * FROM Teste_Rollback_2

SELECT * FROM Teste_Rollback_3

SELECT * FROM Teste_Rollback_4

Conforme era esperado, nenhum registro foi inserido nas tabelas. No início da Procedure 1 nós abrimos uma transação (BEGIN TRAN) e tivemos um erro no final da Procedure 4. Logo, novamente todas as operações que foram feitas após o BEGIN TRAN serão desfeitas. Portanto, nesse caso TODOS os INSERTs serão desfeitos!

“TOP Luiz! Ficou muito mais fácil entender com esses exemplos cara, obrigado!!!” =)

Download – Scripts:

Segue abaixo os scripts utilizados nesse post:

https://github.com/luizvitorf/SQLServer/blob/master/Scripts/T-SQL/Casos%20do%20Dia%20a%20Dia%20-%20Procedures%20x%20Rollback.sql

Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server

Desafio Inicial:

Mais Exemplos:

Exemplo 1:

Exemplo 2:

Exemplo 3:

Exemplo 4:

Exemplo 5:

Download – Scripts:

Compartilhe isso:

Deixe uma respostaCancelar resposta