Gerenciamento de Transações no SQL Server (pt-BR)

  • Artigo

Introdução

Quando desejamos gerenciar ou controlar transações dentro do SQL Server podemos utilizar transações implícitas, ou seja a cada transação que for realizada no SQL Server é necessário que seja especificado um COMMIT ou ROOLBACK  ou também utilizar O SAVE TRANSACTION, iremos ver a seguir os dois modos.

Entendendo os Modos

  • Transações Implícitas

SET IMPLICIT_TRANSACTIONS ON;

 

CREATE TABLE Tempdb.dbo.TesteTransaction

(

      ID INT IDENTITY(1,1) NOT NULL,

      Nome VARCHAR(50)

)

go

 

INSERT INTO Tempdb.dbo.TesteTransaction (Nome)

VALUES (‘Luan Moreno Medeiros Maciel’)

GO 100

 

SELECT *

FROM Tempdb.dbo.TesteTransaction

 http://luanmorenodba.files.wordpress.com/2011/10/image_thumb6.png?w=224&h=244

 Ou seja a transação está em espera, tanto é que se visualizarmos em SP_LOCK teremos que a tabela está em LOCK por causa da transação que ainda não foi comitada.

 http://luanmorenodba.files.wordpress.com/2011/10/image_thumb7.png?w=493&h=179

 Sendo assim,  se realizarmos um ROLLBACK TRANSACTION nessa sessão nenhuma estrutura terá sido criada.

 http://luanmorenodba.files.wordpress.com/2011/10/image_thumb8.png?w=168&h=28

http://luanmorenodba.files.wordpress.com/2011/10/image_thumb9.png?w=332&h=111

 Como foi executado um ROLLBACK na transação, não encontramos a estrutura.

 Outra forma de conseguimos realizar o controle de uma transação é utilizando o SAVE TRANSACTION como dito anteriormente, que é um recurso pouco usado, porém bem interessante, por isso vamos analisar como ele funciona.

  • Save Transaction

E se você desejasse que dentro de uma transação fosse realizadas várias operações, e cada uma dessas operações fossem controladas, isso é possível ?

 Sim, com esse recurso podemos fazer o controle dentro de uma transação, sendo assim vamos realizar a criação da estrutura para podermos utilizar esse recurso.

CREATE DATABASE WorkSpace

go

 

USE WorkSpace

go

 

 

CREATE TABLE DadosEmpregados

(

      ID INT IDENTITY(1,1) NOT NULL,

      Nome VARCHAR(50) NOT NULL,

      DataNascimento DATE NULL

)

go

ALTER TABLE DadosEmpregados

ADD CONSTRAINT PK_DadosEmpregados_ID

PRIMARY KEY (ID)

Nesse momento irei abrir uma transação e utilizar o recurso SAVE TRANSACTION

http://luanmorenodba.files.wordpress.com/2011/10/image_thumb10.png?w=408&h=129

Conseguimos visualizar que, temos agora uma transação aberta e logo depois realizo o save desta transação. Agora iremos ver o que o LOG da base de dados nós mostra, utilizando o DBCC LOG, vemos que..

 DBCC Log(WorkSpace,3)

 http://luanmorenodba.files.wordpress.com/2011/10/image_thumb11.png?w=767&h=144

No Log da base de dados, foi marcado um CheckPoint, temos uma marca dentro do LSN.

 http://luanmorenodba.files.wordpress.com/2011/10/image_thumb12.png?w=699&h=134

 http://luanmorenodba.files.wordpress.com/2011/10/image_thumb13.png?w=244&h=175

 Conseguimos ver o SPID ou seja a conexão que está realizando a transação, a data , ID da transação, nome do Savepoint sendo este o nome que foi colocado no SAVE TRANSACTION.

 Tendo esse *MARK *no LSN a Engine do SQL Server/Storage Engine (Transaction Services)  será possível voltar a esse ponto dentro da transação, interessante não?

 Nesse momento será realizado dois tipos de operações DML  o insert e o delete, sempre realizando um SAVE TRANSACTION antes das operações.

INSERT INTO DadosEmpregados (Nome, DataNascimento)

OUTPUT inserted.*

VALUES (‘Luan Moreno Medeiros Maciel’,’1988-07-20′)

GO 100

 

SAVE TRANSACTION DadosEmpregadosSecondInsert

 

INSERT INTO DadosEmpregados (Nome, DataNascimento)

OUTPUT inserted.*

VALUES (‘Luan Moreno Medeiros Maciel’,’1988-07-20′)

GO 10

 

SAVE TRANSACTION DadosEmpregadosFirtDelete

 

DELETE FROM DadosEmpregados

WHERE ID = 1

 Depois das operações realizadas, eu irei realizar o ROLLBACK do segundo SAVE TRANSACTION

http://luanmorenodba.files.wordpress.com/2011/10/image_thumb14.png?w=409&h=54

 Nesse momento, se visualizarmos a tabela teremos os 100 registos que foram inseridos na primeira massa de dados, referente ao SAVE TRANSACTION DadosEmpregadosFirstInsert

 http://luanmorenodba.files.wordpress.com/2011/10/image_thumb15.png?w=316&h=227

Explicando: 

1 Save Transaction = Antes das 100 inserções.

2 Save Transaction = Antes das 10 inserções

3 Save Transaction = Antes do Delete ID = 1

Sendo assim quando foi realizado o ROLLBACK TRANSACTION DadosEmpregadosSecondInsert a transação foi voltada ao momento das 100 primeiras inserções depois de serem realizadas. Logo depois podemos realizar o commit da transação.

 http://luanmorenodba.files.wordpress.com/2011/10/image_thumb16.png?w=244&h=80

Visualizamos nesse momento que não temos transações abertas nessa sessão.