Referencia: http://www.devmedia.com.br/post-15331-Transacoes-SQL-SERVER.html
O que é transação?
É uma unidade lógica de processamento que tem por objetivo preservar a integridade e a consistência dos dados. Esse processamento pode ser executado todo ou não garantindo a atomicidade das informações.
Uma transação e um conjunto SQL que alcançam um trabalho único.
Durante uma transação, se todos os passos não puderem ser completados sem interferência, nenhuma deles devera ser realizado.
O teste clássico ACID
ACID: ATOMICIDADE
Todas as partes da transação devem ser completadas ou nenhuma delas será completada. Você pode executar parte da transação.
ACID: CONSISTENCIA
Uma transação completa deixa o banco de dados consistente ao final da transação.
ACID: ISOLAMENTO
Isolamento quer dizer que cada transação deve ter uma visão constante do banco de dados, sem levar consideração qualquer outra transação.
ACID: DURABILIDADE
Depois da transação, o banco de dados precisa salvar os dados corretamente e protege-lo da falta de energia ou outras ameaças. Isto geralmente e geralmente e gerenciado através do registro de transações salvas em local diverso do que o banco de dados principal.
Uma transação e um conjunto SQL que alcançam um trabalho único.
Durante uma transação, se todos os passos não puderem ser completados sem interferência, nenhuma deles devera ser realizado.
O teste clássico ACID
ACID: ATOMICIDADE
Todas as partes da transação devem ser completadas ou nenhuma delas será completada. Você pode executar parte da transação.
ACID: CONSISTENCIA
Uma transação completa deixa o banco de dados consistente ao final da transação.
ACID: ISOLAMENTO
Isolamento quer dizer que cada transação deve ter uma visão constante do banco de dados, sem levar consideração qualquer outra transação.
ACID: DURABILIDADE
Depois da transação, o banco de dados precisa salvar os dados corretamente e protege-lo da falta de energia ou outras ameaças. Isto geralmente e geralmente e gerenciado através do registro de transações salvas em local diverso do que o banco de dados principal.
A sintaxe básica de uma transação é:
Begin Transaction
--Corpo de comando
Commit ou Rollback
--Corpo de comando
Commit ou Rollback
Comandos:
PARA VERIFICAR O ID DO PROCESSO DA CESSÃO
SELECT @@SPID
PARA VERIFICAR LOCKS
SP_LOCK 51 (51 representa número do ID da cessão obtido na consulta anterior).
Se IndId estiver igual a 0 significa que não existes LOCKS.
PARA VERIFICAR TRANSAÇÕES ATIVAS
SELECT @@TRANCOUNT
PARA INICIAR UMA TRANSAÇÃO
BEGIN TRANSACTION
PARA CANCELAR A TRANSAÇÃO
ROLLBACK
PARA CONFIRMAR A TRANSAÇÃO
COMMIT
MODIFICANDO TIME OUT OU TEMPO DE ESPERA
SET LOCK TIMEOUT -1 LOCK INDEFINIDAMETE
SET LOCK TIMEOUT 5000ms LOCK POR 5 SEGUNDOS
No SQL SERVE temos uma função de sistema que faz a endentificação de um erro dentro de uma transação chamada de ‘@@ERROR’ função essa que por padrão recebe o valor 0 (zero) caso não ocorra nem um erro , no caso de algum erro ela assume o valor 1 (um).
1. Exemplo @@ERROR:
BEGIN TRANSACTION
UPDATE FROM TbContas
SET NuSaldo= 10.000
WHERE NuSaldo < 50
IF @@ERROR = 0
COMMIT
ELSE
ROLLBACK
END
No exemplo 1 estamos iniciando uma transação para executar a mudança de saldo de algumas determinadas contas onde, em caso de um erro irar executar o comando ‘rollback’ para finalizar a transação e retornar os valores dos saldos, caso tudo ocorra sem erro ira executar a transação e confirmando a alteração.
As vezes as mensagens de erro não são muito claras para os usuarios e pensando nisso, procurando ajudar os desenvolvedores e administradores de sistema foi introduzido no SQL a função ‘RAISERROR’, que permite com que você pensonalizar as mensagens de erro facilitando na hora de algum suporte.
2. Exemplo RAISERROR
BEGIN TRANSACTION
UPDATE FROM TbContas
SET NuSaldo= 10.000
WHERE NuSaldo < 50
IF @@ERROR = 0
COMMIT
ELSE
ROLLBACK
RAISERROR(5001,16,1,’ERRO NA ALTERACAO DO SALDO DA TBL TBCONTA’) END
No exemplo 2 estamos usando o mesmo exemplo 1 porém acresentamos em caso de algum erro uma mensagem pensonalizada para a nossa transação.
Tipo de Transações
A Microsoft criou duas classificações com relação aos tipos de transações, onde, podemos classificar as transações que iniciam com BEGIN TRANSACTION com transações explicitas, as transações implicitas são as transações que estão envolvidas algum processor de INSERT,UPDATE e DELETE onde o SQL SERVER trata cada um desse comandos com uma transação.
Caso ocorra a necessidade de deixa o SQL SERVER no modo implicito basta executar a seguinte linha de comando abaixo, feito isso todo processo executado pelo SQL será tratado com uma transação.
Tipo de Transações
A Microsoft criou duas classificações com relação aos tipos de transações, onde, podemos classificar as transações que iniciam com BEGIN TRANSACTION com transações explicitas, as transações implicitas são as transações que estão envolvidas algum processor de INSERT,UPDATE e DELETE onde o SQL SERVER trata cada um desse comandos com uma transação.
Caso ocorra a necessidade de deixa o SQL SERVER no modo implicito basta executar a seguinte linha de comando abaixo, feito isso todo processo executado pelo SQL será tratado com uma transação.
SET IMPLICIT_TRANSACTIONS ON
Assim com podemos defenir que todos os processos que o SQL SERVER execute se transforme em alguma transação, pode automatizar o processo, caso ocorra algum erro não se farar necessidade executar o comando ‘rollback’ executando a linha de comando a baixo fazendo com que em caso de algum erro, seja desfeita a todos os processos que estão no corpo de comando de uma transação, sem a necessidade de usar a função @@ERROR para verificar a ocorrencia de erros.
SET XACT_ABORT ON
Todo a transação é registrada no log do banco de dados, pois quando executamos alguma comando de INSERT, UPDATE E DELETE o mesmo não executa a operação na tabela para garantir atomicidade das informações, pois caso ocorra algum eventual problema durante a transação o engine do SQL SERVER possa confirmar as transações confirmadas ou desfazer as transações registradas.
Os saves points
O save point é o comando utilizado para confirma pequenas transações que estão dentro de uma maior garantindo a execução de uma parte da transação onde para utilizamos devemos atribuir um nome ao save point para identificalo.
Os saves points
O save point é o comando utilizado para confirma pequenas transações que estão dentro de uma maior garantindo a execução de uma parte da transação onde para utilizamos devemos atribuir um nome ao save point para identificalo.
3 . Exemplo Save Point:
BEGIN TRANSACTION
UPDATE FROM TbContas
SET NuSaldo= 10.000
WHERE NuSaldo < 50
SAVE TRANSACTION INS_TBLOG INSERT INTO tblog SELECT NuSaldo, IdConta From Deleted
COMMIT
No exemplo 3 estamos criando uma transação que irar mudar todos os saldos de quem possui o valor menor que 50 para 10.000, onde estamos criando um save point para garantir que as informações antigas sejam inseridas na tabela de log do sistema para garantir a integridade da informações, caso futuramente tenha sido feita alguma alteração que não deveria ocorrer.
O que é Lock?
É o bloqueio que os registro sofre automaticamente ou manualmente para garantir a integridade das informações que possam estar em uma transação ou não, os bloqueios são utilizados para não deixa com que varios usuarios alterem o mesmo registro.
O SQL SERVER possui o tipo de bloqueio compartilhado, exclusivo, atualizacao, intenção, alteração de volument e esquema, onde o compartilhado permite com que o mesmo registro, seja acessado por varios usuarios ao mesmo tempo bastante utilizado quando vamos selecionar alguns registro, já o exclusivo ele não permite com que o mesmo registro, seja alterado, acessado ou excluido ao mesmo tempo.
O bloqueio de atualização evita que ocorra um DeadLock, quando o os registro estão sendo alterados ocorrendo uma troca entre os bloqueio compartilhados e exclusivos, já o bloqueio de intenção ele bloqueia o registro que se encontra em uma transação para que outra transação não venha a bloquear o mesmo.
O bloqueio de alteração em volume ocorre quando temos que alterar uma grande quantidade de dados, já o bloqueio de esquema esta subdividido em dois onde tempos o modificado e estavel, onde o modificado bloqueio que ocorre a nivel de tabela quando a estrutura da mesma esta sendo alterada, já o estavel é o bloqueio que correr quando tempos uma consulta a uma tabela e a mesma esta sofrendo algum tipo de mudança na sua estrutura.
O SQL SERVER possui o tipo de bloqueio compartilhado, exclusivo, atualizacao, intenção, alteração de volument e esquema, onde o compartilhado permite com que o mesmo registro, seja acessado por varios usuarios ao mesmo tempo bastante utilizado quando vamos selecionar alguns registro, já o exclusivo ele não permite com que o mesmo registro, seja alterado, acessado ou excluido ao mesmo tempo.
O bloqueio de atualização evita que ocorra um DeadLock, quando o os registro estão sendo alterados ocorrendo uma troca entre os bloqueio compartilhados e exclusivos, já o bloqueio de intenção ele bloqueia o registro que se encontra em uma transação para que outra transação não venha a bloquear o mesmo.
O bloqueio de alteração em volume ocorre quando temos que alterar uma grande quantidade de dados, já o bloqueio de esquema esta subdividido em dois onde tempos o modificado e estavel, onde o modificado bloqueio que ocorre a nivel de tabela quando a estrutura da mesma esta sendo alterada, já o estavel é o bloqueio que correr quando tempos uma consulta a uma tabela e a mesma esta sofrendo algum tipo de mudança na sua estrutura.
Tipo de recursos que podem ser bloqueados
• RID – identificador de uma linha utilizado para bloquear um única linha.
• KEY – um lock sobre uma linha de um índice usado para proteger um conjunto de chaves.
• PAGE – uma página de 8 KB (de dados ou de um índice).
• EXTENT – um grupo contíguo de 8 páginas.
• HOBT – um lock (heap ou B-tree) que protege um índice ou a heap de páginas de dados de uma tabela que não tenha um índice clustered.
• TABLE – uma tabela inteira (incluindo dados e índices).
• FILE – um ficheiro da base de dados.
• APPLICATION – um recurso específico de uma base de dados.
• METADATA – os metadados.
• ALLOCATION_UNIT – um “application unit”.
• DATABASE – uma base de dados completa.
Niveis de Isolamento dos registro dentro de uma transação .
São controle criando pela Microsoft para garantir o versionamento dos registro podendo ser:
Read uncommitted
Especifica que as instruções podem ler linhas que foram modificadas por outras transações, mas que ainda não foram confirmadas.
Read committed
Especifica que as instruções não podem ler dados que foram modificados, mas ainda não confirmados por outras transações. Isto previne leituras sujas. Dados podem ser alterados através de outras transações entre instruções individuais dentro da transação atual, resultando em leituras não repetíveis ou dados fantasmas. Esta opção é o padrão do SQL Server.
Repeatable read
Especifica que as instruções não podem ler dados que foram modificados, mas ainda não confirmados por outras transações e que nenhuma outra transação pode modificar dados que foram lidos pela transação atual até que esta seja concluída.
Snapshot
Especifica que os dados lidos por qualquer instrução em uma transação serão a versão transacionalmente consistente que existia no início da transação.
Serializable
Nível de isolamento que bloqueia que outras transações atualizem ou insiram alguma linha que possa se qualificar para alguma das instruções executadas pela transação atual. Isto significa que, se alguma das instruções de uma transação for executada uma segunda vez, ela lerá o mesmo conjunto de linhas. Os bloqueios de intervalo são mantidos até que a transação seja concluída. Esse é o mais restritivo dos níveis de isolamento, pois ele bloqueia intervalos de chaves inteiros até que a transação seja concluída.