Resumo : neste tutorial, você aprenderá sobre transações MySQL e como usar as instruções START TRANSACTION, COMMIT e ROLLBACK para gerenciar transações no MySQL.
Introdução às transações MySQL
No mundo dos bancos de dados, uma transação é uma sequência de uma ou mais instruções SQL executadas como uma única unidade de trabalho.
As transações permitem garantir a integridade dos dados, permitindo que um conjunto de operações seja totalmente concluído ou totalmente revertido em caso de erro.
O MySQL suporta transações por meio das instruções START TRANSACTION, COMMITe ROLLBACK:
START TRANSACTION– Marcar o início de uma transação. Observe queBEGINouBEGINWORKsão os aliases do arquivoSTART TRANSACTION.COMMIT– Aplicar as alterações de uma transação ao banco de dados.ROLLBACK– Desfaça as alterações de uma transação revertendo o banco de dados para o estado anterior ao início da transação.
Por padrão, quando você executa uma instrução SQL, o MySQL automaticamente a envolve em uma transação e confirma a transação automaticamente.
Para instruir o MySQL a não iniciar uma transação implicitamente e confirmar as alterações automaticamente, você define o valor da autocommitvariável como 0ou OFF:
SET autocommit = OFF;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Ou:
SET autocommit = 0;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Para ativar o modo de confirmação automática, defina o valor da autocommitvariável como 1ou ON:
SET autocommit = 1;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Ou:
SET autocommit = ON;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Exemplo de transações básicas do MySQL
1) Configurando tabelas de amostra
Primeiro, conecte-se ao servidor MySQL :
mysql -u root -pLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, crie um banco de dados chamado bankse uma tabela chamada users:
CREATE DATABASE banks;
USE banks;
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
email VARCHAR(255)
);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )2) Exemplo MySQL COMMIT
Primeiro, inicie uma transação que insira uma nova linha na userstabela e atualize o email do usuário:
START TRANSACTION;
INSERT INTO users (id, username)
VALUES (1, 'john');
UPDATE users
SET email = '[email protected]'
WHERE id = 1;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, recupere os dados da userstabela:
SELECT * FROM users;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+----+----------+----------------------+
| id | username | email |
+----+----------+----------------------+
| 1 | john | [email protected] |
+----+----------+----------------------+
1 row in set (0.00 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A saída mostra que a userstabela tem uma linha, mas ela só é visível para a sessão atual, não para outras sessões.
Se você abrir outra sessão e consultar dados da userstabela, não verá nenhuma linha na userstabela. A razão é que a transação na primeira sessão não foi confirmada.
Quinto, confirme a transação:
COMMIT;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A COMMITinstrução aplica todas as alterações feitas durante a transação, tornando-as permanentes e visíveis para outras sessões do banco de dados.
Se você abrir a segunda sessão, poderá ver os dados da userstabela.
3) Exemplo de ROLLBACK do MySQL
Primeiro, inicie uma transação:
START TRANSACTION;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, insira uma nova linha na userstabela e atualize emailpara o usuário:
INSERT INTO users (id, username)
VALUES (2, 'jane');
UPDATE users
SET email = '[email protected]'
WHERE id = 2;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Terceiro, reverta a transação:
ROLLBACK;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A ROLLBACKinstrução desfaz todas as alterações feitas durante a transação, revertendo o banco de dados ao estado anterior ao início da transação.
Por fim, selecione os dados da userstabela:
SELECT * FROM users;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+----+----------+----------------------+
| id | username | email |
+----+----------+----------------------+
| 1 | john | [email protected] |
+----+----------+----------------------+
1 row in set (0.00 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A saída mostra que a transação foi revertida, portanto não há nenhuma nova linha na userstabela.
Usando transações MySQL em procedimentos armazenados
1) Configurando tabelas de amostra
Primeiro, altere o banco de dados atual para banks:
CREATE DATABASE banks;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, crie uma nova tabela chamada accountspara armazenar os titulares e saldos das contas:
CREATE TABLE accounts (
account_id INT AUTO_INCREMENT PRIMARY KEY ,
account_holder VARCHAR(255) NOT NULL,
balance DECIMAL(10, 2) NOT NULL
);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Terceiro, crie uma tabela chamada transactionspara armazenar as transações entre contas:
CREATE TABLE transactions (
transaction_id INT AUTO_INCREMENT PRIMARY KEY,
account_id INT NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
transaction_type ENUM('DEPOSIT', 'WITHDRAWAL') NOT NULL,
FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Finalmente, insira duas linhas na accountstabela:
INSERT INTO accounts (account_holder, balance)
VALUES ('John Doe', 1000.00),
('Jane Doe', 500.00);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Transferir dinheiro entre duas contas
O seguinte procedimento cria um procedimento armazenado que transfere dinheiro entre duas contas:
DELIMITER //
CREATE PROCEDURE transfer(
IN sender_id INT,
IN receiver_id INT,
IN amount DECIMAL(10,2)
)
BEGIN
DECLARE rollback_message VARCHAR(255) DEFAULT 'Transaction rolled back: Insufficient funds';
DECLARE commit_message VARCHAR(255) DEFAULT 'Transaction committed successfully';
-- Start the transaction
START TRANSACTION;
-- Attempt to debit money from account 1
UPDATE accounts SET balance = balance - amount WHERE account_id = sender_id;
-- Attempt to credit money to account 2
UPDATE accounts SET balance = balance + amount WHERE account_id = receiver_id;
-- Check if there are sufficient funds in account 1
-- Simulate a condition where there are insufficient funds
IF (SELECT balance FROM accounts WHERE account_id = sender_id) < 0 THEN
-- Roll back the transaction if there are insufficient funds
ROLLBACK;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = rollback_message;
ELSE
-- Log the transactions if there are sufficient funds
INSERT INTO transactions (account_id, amount, transaction_type) VALUES (sender_id, -amount, 'WITHDRAWAL');
INSERT INTO transactions (account_id, amount, transaction_type) VALUES (receiver_id, amount, 'DEPOSIT');
-- Commit the transaction
COMMIT;
SELECT commit_message AS 'Result';
END IF;
END //
DELIMITER ;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )O procedimento armazenado de transferência transfere um valor entre duas contas: remetente e destinatário. Possui três parâmetros:
sender_id– o ID do remetente.receiver_id– o ID do receptor.amount– o valor que será transferido entre as duas contas.
Como funciona.
Primeiro, inicie a transação usando a START TRANSACTIONinstrução:
START TRANSACTION;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, aumente o saldo do remetente e diminua o saldo do destinatário:
UPDATE
accounts
SET
balance = balance - amount
WHERE
account_id = sender_id;
UPDATE
accounts
SET
balance = balance + amount
WHERE
account_id = receiver_id;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Terceiro, reverta a transação se o saldo da conta do remetente não for suficiente e também emita uma mensagem de erro:
IF (SELECT balance FROM accounts WHERE account_id = sender_id) < 0 THEN
ROLLBACK;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = rollback_message;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Caso contrário, adicione duas linhas à transactionstabela e aplique as alterações ao banco de dados confirmando a transação (na ELSEfilial):
INSERT INTO transactions (account_id, amount, transaction_type) VALUES (1, -amount, 'WITHDRAWAL');
INSERT INTO transactions (account_id, amount, transaction_type) VALUES (2, amount, 'DEPOSIT');
COMMIT;
SELECT commit_message AS 'Result';Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Chamando o procedimento armazenado de transferência
Primeiro, recupere os saldos das contas:
SELECT * FROM accounts;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+------------+----------------+---------+
| account_id | account_holder | balance |
+------------+----------------+---------+
| 1 | John Doe | 1000.00 |
| 2 | Jane Doe | 500.00 |
+------------+----------------+---------+
2 rows in set (0.00 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, transfira 100 do ID da conta 1 para o ID da conta 2:
CALL transfer(1,2,100);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+------------------------------------+
| Result |
+------------------------------------+
| Transaction committed successfully |
+------------------------------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Como a conta 1 possui fundos suficientes, a transação foi bem-sucedida.
Terceiro, revise os saldos das contas:
SELECT
*
FROM
accounts
WHERE
account_id IN (1, 2);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+------------+----------------+---------+
| account_id | account_holder | balance |
+------------+----------------+---------+
| 1 | John Doe | 900.00 |
| 2 | Jane Doe | 600.00 |
+------------+----------------+---------+
2 rows in set (0.00 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A saída indica que o ID da conta 1 tem saldo de 900 e o ID da conta 2 tem saldo de 600, o que está correto.
SELECT * FROM transactions;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+----------------+------------+---------+------------------+
| transaction_id | account_id | amount | transaction_type |
+----------------+------------+---------+------------------+
| 1 | 1 | -100.00 | WITHDRAWAL |
| 2 | 2 | 100.00 | DEPOSIT |
+----------------+------------+---------+------------------+
2 rows in set (0.00 sec)Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A transactionstabela também possui duas linhas que registram a transferência.
Quarto, tente transferir 1.000 do ID da conta 1 para o ID da conta 2:
CALL transfer(1,2,1000);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
ERROR 1644 (45000): Transaction rolled back: Insufficient fundsLinguagem de código: SQL (linguagem de consulta estruturada) ( sql )Como o ID da conta 1 não possui fundos suficientes, a transação foi revertida. Além disso, os saldos de ambas as contas foram revertidos:
SELECT * FROM accounts;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+------------+----------------+---------+
| account_id | account_holder | balance |
+------------+----------------+---------+
| 1 | John Doe | 900.00 |
| 2 | Jane Doe | 600.00 |
+------------+----------------+---------+
2 rows in set (0.00 sec)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Resumo
- Uma transação é uma sequência de instruções SQL executadas como uma única unidade de trabalho.
- Use o
STARTTRANSACTIONextrato para iniciar uma transação. - Use a
COMMITinstrução para aplicar as alterações feitas durante a transação ao banco de dados. - Use a
ROLLBACKinstrução para reverter as alterações feitas durante a transação e reverter o estado do banco de dados antes do início da transação.
