Uma transação no SQL Server é um grupo sequencial de declarações ou consultas para executar tarefas únicas ou múltiplas em um banco de dados. Cada transação pode ter operações únicas de leitura, gravação, atualização ou exclusão ou uma combinação de todas essas operações. Cada transação deve acontecer duas coisas no SQL Server:
conversão e conversão de tipo java
- Todas as modificações são bem-sucedidas quando a transação é confirmada.
- Ou todas as modificações são desfeitas quando a transação é revertida.
Uma transação não pode ser bem-sucedida até que todas as operações do conjunto sejam concluídas. Isso significa que se algum argumento falhar, a operação de transação falhará. Cada transação começa com a primeira instrução SQL executável e termina quando encontra uma confirmação ou reversão, explícita ou implicitamente. Ele usa o COMPROMETER-SE ou REVERTER instruções explicitamente, bem como implicitamente quando uma instrução DDL é usada.
A representação pictórica abaixo explica o processo de transação:
O exemplo a seguir explicará o conceito de transação:
Este exemplo usará o sistema de banco de dados bancário para explicar o conceito de transação. Suponha que um cliente de banco queira sacar dinheiro de sua conta usando o modo ATM. O ATM pode realizar esta operação em três etapas:
- O Primeiro passo é verificar a disponibilidade do valor solicitado na conta.
- O segundo passo deduz o valor da conta se o valor estiver disponível e, em seguida, atualiza o saldo da conta.
- O terceiro passo é escrever a operação de retirada de dinheiro no arquivo de log. Esta etapa escreve sobre se a transação foi bem-sucedida ou falhou. Se for bem-sucedido, grave a modificação dos dados no banco de dados. Caso contrário, a transação será revertida ao estado anterior.
O princípio básico por trás das transações é que, se uma das instruções retornar um erro, todo o conjunto de alterações será revertido para garantir a integridade dos dados. E se as transações forem bem-sucedidas, todas as alterações serão permanentes no banco de dados. Assim, se houver queda de energia ou outros problemas ao sacar dinheiro em um caixa eletrônico, as transações garantem que nosso saldo permaneça consistente. Uma instrução de transação executa melhor essas operações porque as quatro propriedades principais da transação tornam todas as operações mais precisas e consistentes. As quatro propriedades da transação são chamadas de ACID.
Propriedades de transação
As propriedades da transação são chamadas de propriedade ACID (Atomicidade, Consistência, Isolamento, Durabilidade), que são discutidas em detalhes abaixo:
Atomicidade: Esta propriedade garante que todas as instruções ou operações incluídas na transação sejam executadas com sucesso. Caso contrário, toda a transação será abortada e todas as operações serão revertidas ao estado anterior quando alguma operação falhar.
Consistência: Esta propriedade garante que o banco de dados mude de estado somente quando uma transação for confirmada com sucesso. Também é responsável por proteger os dados contra falhas.
Isolamento: Esta propriedade garante que todas as transações sejam isoladas de outras transações, ou seja, cada operação da transação é operada de forma independente. Também garante que as declarações sejam transparentes entre si.
Durabilidade: Esta propriedade garante que o resultado das transações confirmadas persista permanentemente no banco de dados, mesmo se o sistema travar ou falhar.
Modos de transação no SQL Server
Existem três modos de transação diferentes que o SQL Server pode usar:
Modo de transação de confirmação automática: É o modo de transação padrão do SQL Server. Ele avaliará cada instrução SQL como uma transação e os resultados serão confirmados ou revertidos de acordo. Assim, as instruções bem-sucedidas são imediatamente confirmadas, enquanto as instruções com falha são imediatamente revertidas.
Modo de transação implícito. Este modo permite que o SQL Server inicie a transação implícita para cada instrução DML, mas requer explicitamente o uso de comandos commit ou rollback no final das instruções.
Modo de transação explícito: Este modo é definido pelo usuário e nos permite identificar com exatidão os pontos inicial e final de uma transação. Ele será abortado automaticamente em caso de erro fatal.
Controle de transações
A seguir estão os comandos usados para controlar transações:
NOTA: Podemos usar apenas instruções DML (INSERT, UPDATE e DELETE) para comandos da Transaction Control Language. Não podemos usá-los ao criar ou eliminar tabelas porque essas operações são confirmadas no banco de dados automaticamente.
Estado da transação
Indica como ocorrem as transações durante sua vida. Descreve o estado atual da transação, bem como a forma como a transação será processada no futuro. Esses estados definem as regras que determinam se uma transação é confirmada ou abortada.
Vamos descrever cada estado de transação no SQL Server:
Estado ativo: A transação está em estado ativo enquanto as instruções da transação estão sendo executadas. Ele muda para o 'estado parcialmente comprometido' se todas as operações de 'leitura e gravação' forem concluídas sem erros. Se alguma instrução falhar, ela muda para o 'estado de falha'.
Parcialmente comprometido: Quando todas as operações de leitura e gravação forem concluídas, a alteração é feita na memória principal ou buffer local. O estado iria para 'estado comprometido' se as alterações se tornarem permanentes no banco de dados. Caso contrário, vai para o 'estado de falha'.
Estado falido: Uma transação vai para o estado de falha quando qualquer instrução da transação falha ou uma modificação permanente no banco de dados falha.
Estado abortado: A transação passa de um 'Estado falido' para um 'estado abortado' quando ocorre qualquer tipo de falha. As alterações são removidas ou revertidas porque essas alterações são feitas apenas no buffer local ou na memória principal em estados anteriores.
Estado Comprometido: Uma transação é concluída e entra nesse estado quando as alterações se tornam permanentes no banco de dados e terminadas no 'estado encerrado'.
Estado encerrado: Se não houver rollback e a transação estiver no 'estado comprometido', o sistema está consistente e pronto para uma nova transação enquanto a antiga é encerrada.
contar distinto
Implementação de transação no SQL Server
Vejamos alguns exemplos para entender como podemos implementar a transação no SQL Server. Aqui usaremos o 'Produtos' tabela para demonstrar todos os estados da transação.
Os seguintes scripts SQL criam a tabela Produto no banco de dados selecionado:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
A seguir, execute os scripts abaixo para inserir dados nesta tabela:
INSERT INTO Product VALUES(111, 'Mobile', 10000, 10), (112, 'Laptop', 20000, 15), (113, 'Mouse', 300, 20), (114, 'Hard Disk', 4000, 25), (115, 'Speaker', 3000, 20);
Execute a instrução SELECT para verificar os dados:
Exemplo de transação COMMIT
É uma boa ideia dividir as instruções SQL usadas na transação em diversas partes lógicas. E então, podemos decidir se devemos confirmar ou reverter os dados. As etapas a seguir ilustram a criação de uma transação:
- Inicie a transação usando o INICIAR TRANSAÇÃO comando.
- Escreva as instruções SQL e divida-as com base em nossas necessidades
- Use o COMPROMETER-SE extrato para concluir a transação e salvar as alterações permanentemente.
Abaixo estão os comandos que explicam as operações COMMIT no SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements INSERT INTO Product VALUES(116, 'Headphone', 2000, 30) UPDATE Product SET Price = 450 WHERE Product_id = 113 -- Commit changes COMMIT TRANSACTION
Se nenhum erro for encontrado, veremos a seguinte saída onde cada instrução SQL da transação é executada de forma independente:
As instruções INSERT e UPDATE não podem ser revertidas após a transação ter sido confirmada. Ao verificarmos a tabela após a operação de commit, veremos os seguintes dados:
Exemplo de transação ROLLBACK
Usaremos o comando ROLLBACK para desfazer quaisquer transações que ainda não foram salvas no banco de dados e retornar ao ponto onde a transação começou. O exemplo a seguir explica a operação ROLLBACK no SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116
Depois de executarmos a transação acima, podemos ver que ela será executada com sucesso. No entanto, isso não afetará nenhuma alteração no banco de dados porque até que não executemos a instrução COMMIT ou ROLLBACK, as alterações não poderão se tornar permanentes. Portanto, temos a opção de usar o comando de transação ROLLBACK para reverter todas as operações do banco de dados. Aqui está o extrato completo da transação:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116 --Undo Changes ROLLBACK TRANSACTION
Uso da variável @@Error Global em transações:
Esta variável é usado para verificar se há um erro ou não. O exemplo abaixo explica seu conceito. Aqui, primeiro iniciaremos a transação usando o comando BEGIN e depois escreveremos duas instruções de inserção. A seguir, usaremos a variável global do sistema @@ERRO no Declaração SE para verificar um erro. Se o valor for maior que 0, significa que há algum erro. Agora, a transação é revertida; caso contrário, a transação será confirmada.
BEGIN TRANSACTION INSERT INTO Product VALUES(115,'Speaker', 3000, 25) -- Check for error IF(@@ERROR > 0) BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END
Quando a transação acima for executada, notaremos que ela foi revertida. Isso se deve à nossa tentativa de inserir um valor duplicado na coluna Chave primária.
Transação de reversão automática
A maioria das transações contém mais de uma consulta. Durante a execução da transação, se alguma das instruções SQL produzir um erro, nenhuma modificação ocorrerá no banco de dados e as instruções restantes não serão executadas. Este conceito é conhecido como transação de reversão automática no SQL Server. Vamos usar um exemplo simples para demonstrar esse processo.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Esta transação produz a seguinte saída:
Nesta saída, podemos ver que a instrução insert foi executada com sucesso. No entanto, a execução da instrução de atualização encontrou um erro devido ao problema de conversão do tipo de dados. Nesse caso, o SQL Server não permite nenhuma alteração no banco de dados, o que significa que a operação de inserção não adiciona nenhum valor e a instrução select não é executada.
Savepoint em transações
O savepoint insere uma marca especial em uma transação que nos permite reverter todas as alterações executadas após o savepoint. Também é usado para reverter qualquer parte específica da transação, em vez de toda a transação. Podemos defini-lo usando o SALVAR TRANSAÇÃO sp_name declaração. O exemplo a seguir explicará o uso do savepoint em transações que confirmam a instrução insert e revertem a instrução delete.
BEGIN TRANSACTION INSERT INTO Product VALUES(117, 'USB Drive', 1500, 10) SAVE TRANSACTION InsertStatement DELETE FROM Product WHERE Product_id = 116 SELECT * FROM Product ROLLBACK TRANSACTION InsertStatement COMMIT SELECT * FROM Product;
Veja o resultado abaixo, onde podemos ver que o ID do produto 116 foi excluído e 117 foi inserido na primeira saída. No entanto, na segunda saída, a operação de exclusão é revertida devido ao ponto de salvamento.
Como liberar um savepoint em uma transação?
Liberar ponto de salvamento é usado para remover o ponto de salvamento nomeado da transação atual sem reverter os resultados das consultas executadas após o ponto de salvamento. O MySQL possui esse comando, mas o SQL Server não fornece nenhum comando para liberar um ponto de salvamento. Em vez disso, eles são liberados automaticamente no final de uma transação de commit ou rollback, portanto não precisamos nos preocupar com eles no momento.
strings de classificação java
Transação implícita no SQL Server
Podemos definir uma transação implícita habilitando a opção IMPLICIT_TRANSACTIONS. O exemplo a seguir explicará esse conceito facilmente:
SET IMPLICIT_TRANSACTIONS ON UPDATE Product SET Quantity = 10 WHERE Product_id = 113 SELECT IIF(@@OPTIONS & 2 = 2, 'Implicit Transaction Mode ON', 'Implicit Transaction Mode OFF' ) AS 'Transaction Mode' SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Nesta transação, usamos duas opções @@OPÇÃO e @@TRANCOUNT. O @@OPTOPN fornece informações sobre as opções SET atuais e @@TRANCOUNT fornece a instrução BEGIN TRANSACTION na sessão atual.
Agora, a execução da transação retornará a saída abaixo:
Transação explícita no SQL Server
Uma transação explícita deve ser definida através do comando BEGIN TRANSACTION porque identifica o ponto inicial da transação explícita. Podemos definir a transação explícita no SQL Server conforme abaixo:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
Na sintaxe, a opção trans_name indica um nome exclusivo de uma transação. O @trans_name_var indica uma variável definida pelo usuário que armazena o nome da transação. finalmente, o MARCA opção nos permite marcar uma transação específica no arquivo de log.
A transação explícita através do comando BEGIN TRANSACTION adquiriu um bloqueio dependendo do nível de isolamento dos recursos relacionados à transação. Ajuda a reduzir problemas de bloqueio. Veja o exemplo abaixo:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Aqui está o resultado:
Transação marcada no SQL Server
A transação marcada é usada para adicionar uma descrição a uma transação específica nos arquivos de log. Podemos usá-lo como um ponto de recuperação no lugar de uma data e hora ao restaurar um banco de dados para um estado anterior. Devemos saber que a marca é adicionada nos arquivos de log somente quando a transação marcada modifica o banco de dados. Podemos entender seu conceito usando o exemplo a seguir.
Suponha que modificamos o banco de dados acidentalmente e não sabemos o momento exato da alteração dos dados; nesse caso, a recuperação dos dados pode demorar muito. Porém, se utilizarmos as transações marcadas, pode ser uma ferramenta útil para determinar o momento exato das alterações de dados.
A sintaxe a seguir ilustra a transação marcada no SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Aqui devemos definir o nome da transação e depois adicionar a opção COM MARK. No exemplo abaixo, vamos deletar registros e adicionar a marca no arquivo de log:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
O histórico de logmark tabela está incluída no banco de dados msdb e armazena informações sobre cada transação marcada que foi confirmada. Execute a instrução abaixo para obter os detalhes da tabela logmarkhistory:
SELECT * FROM msdb.dbo.logmarkhistory
Transação nomeada no SQL Server
Também podemos fornecer um nome para nossa transação no SQL Server. É sempre recomendado usar a transação nomeada ao trabalhar com muitas transações em uma única consulta. O exemplo abaixo explica como renomear uma transação:
BEGIN TRANSACTION AddProduct INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Product_name = 'Pen Drive' WHERE Product_id = 117 COMMIT TRANSACTION AddProduct
Aqui está o resultado:
Conclusão
Este artigo fornecerá uma visão geral completa da transação nas instruções do SQL Server. As transações são úteis em sistemas de banco de dados relacionais porque garantem a integridade do banco de dados.