Bases de Dados (CC2005), Dep. Ciência de Computadores, FCUP
Eduardo R. B. Marques, DCC/FCUP
Objectivos: Introdução a transações em bases de dados.
Referências: Transações ("slides" das aulas teóricas)
Baixe o ficheiro account.sql que contém:
mysql> source account.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM ACCOUNT;
+-----------+-------+
| AccountId | Value |
+-----------+-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| 4 | 400 |
| 5 | 500 |
+-----------+-------+
5 rows in set (0.00 sec)Execute a seguinte sequência de instruções, notando que esta se inicia com a instrução START TRANSACTION que define o início de uma transação:
START TRANSACTION;
INSERT INTO ACCOUNT(AccountId, Value) VALUES(6, 600);
UPDATE ACCOUNT SET Value = Value - 100 WHERE AccountId = 1;
UPDATE ACCOUNT SET Value = Value + 100 WHERE AccountId = 2;
SELECT * FROM ACCOUNT;Deverá obter:
+-----------+-------+
| AccountId | Value |
+-----------+-------+
| 1 | 0 |
| 2 | 300 |
| 3 | 300 |
| 4 | 400 |
| 5 | 500 |
| 6 | 600 |
+-----------+-------+Execute de seguida:
ROLLBACK;
SELECT * FROM ACCOUNT;A instrução ROLLBACK desfaz os efeitos da transação. Deverá observar que o estado anterior ao início da transação foi reposto, i.e., haverá 5 contas em que a conta i volta a ter o valor de i * 100. Ou seja, deverá voltar a obter:
+-----------+-------+
| AccountId | Value |
+-----------+-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| 4 | 400 |
| 5 | 500 |
+-----------+-------+
5 rows in set (0.00 sec)Repita as instruções no passo 1 e execute de seguida
COMMIT;
SELECT * FROM ACCOUNT;Deverá observar que os efeitos da transação se tornaram persistentes, i.e.:
+-----------+-------+
| AccountId | Value |
+-----------+-------+
| 1 | 0 |
| 2 | 300 |
| 3 | 300 |
| 4 | 400 |
| 5 | 500 |
| 6 | 600 |
+-----------+-------+Recarregue account.sql e considere o código inicial do procedimento transfer nesse ficheiro.
CREATE PROCEDURE transfer(IN id1 INT, IN id2 INT, IN amount INT, OUT done BOOLEAN)
BEGIN
START TRANSACTION;
UPDATE ACCOUNT
SET Value = Value - amount
WHERE AccountId = id1;
UPDATE ACCOUNT
SET Value = Value + amount
WHERE AccountId = id2;
SET DONE = TRUE;
COMMIT;
END $Observe que transfer executa uma transação onde é debitado um montante amount de uma conta id1 e credita esse valor em outra conta id2. Adicionalmente, é retornado no parâmetro de saída done o valor TRUE. Por exemplo, tendo em conta o estado inicial da BD, podemos executar:
CALL transfer(4, 5, 100, @done);
SELECT @done;
SELECT * FROM ACCOUNT;e obter:
+-------+
| @done |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
+-----------+-------+
| AccountId | Value |
+-----------+-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| 4 | 300 |
| 5 | 600 |
+-----------+-------+
5 rows in set (0.00 sec)Observe que transfer completa a transação com sucesso mesmo que as contas id1 e/ou id2 não existam!
Por exemplo execute:
source account.sql
CALL transfer(3, 666, 300, @done1);
CALL transfer(666, 4, 400, @done2);
SELECT * FROM ACCOUNT;Obterá o seguinte estado na BD, em que as contas 3 e 4 foram respectivamente debitadas e creditadas de forma "indevida":
+-----------+-------+
| AccountId | Value |
+-----------+-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 0 |
| 4 | 800 |
| 5 | 500 |
+-----------+-------+Modifique o código por forma à transação ser desfeita com ROLLBACK caso alguma das contas não exista e devolva done = FALSE nesse caso.
Outro problema é que transfer não verifica se o saldo da conta debitada (id1) é suficiente para a transferência
Por exemplo execute:
source account.sql
CALL transfer(1, 2, 200, @done);
SELECT * FROM ACCOUNT;Obterá um valor negativo para a conta 1:
+-----------+-------+
| AccountId | Value |
+-----------+-------+
| 1 | -100 |
| 2 | 400 |
| 3 | 300 |
| 4 | 400 |
| 5 | 500 |
+-----------+-------+ Modifique novamente o código de transfer por forma a que seja verificado que o saldo da conta debitada (id1) é igual ou superior a amount. Em caso negativo, analogamente ao exercício anterior, a transação deve ser desfeita com ROLLBACK e o parâmetro de saída done deve preenchido com FALSE.
Seria adequado também já agora validar se id1 != id2, e desfazer a transação quando id1 = id2. Embora os efeitos de débito e crédito se anulem neste caso, a operação não faz sentido lógico do ponto de vista de "regras de negócio". Modifique o código de novo.
Assuma o estado inicial da BD definido em account.sql (conta AccountId = i tem um saldo de Value = i * 100 para i=1,...,5), e considere que
T1: CALL transfer(a,b,100,@done1)
e
T2: CALL transfer(c,d,200,@done2)
são transações que executam concorrentemente, e que poderão completar com COMMIT ou ser desfeitas com ROLLBACK nas condições descritas no exercício anterior (desfeitas no caso de ids de contas não existentes, saldo insuficiente nas conta debitada, e transferência para a própria conta).
Indique se T1 e/ou T2 completam (com COMMIT) ou são desfeitas (com ROLLBACK), e quais poderão ser o(s) estado(s) da BD depois de uma execução serializável de T1 e T2 em concorrência a partir do estado inicial em cada um dos seguintes casos:
| Caso | a | b | c | d |
|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 4 |
| 2 | 666 | 3 | 3 | 4 |
| 3 | 2 | 1 | 1 | 4 |
| 4 | 2 | 3 | 3 | 4 |
| 5 | 2 | 1 | 2 | 4 |
Em cada caso tem de analisar 2 possibilidades: T1 seguida de T2, ou o inverso. Para validar a sua análise, se tiver completado transfer correctamente no exercício anterior, pode por exemplo executar:
SET @a = ...;
SET @b = ...;
SET @c = ...;
SET @d = ...;
-- T1 >> T2
source account.sql;
CALL transfer(@a, @b, 100, @done1);
CALL transfer(@c, @d, 200, @done2);
SELECT * FROM ACCOUNT;
-- T2 >> T1
source account.sql;
CALL transfer(@c, @d, 200, @done2);
CALL transfer(@a, @b, 100, @done1);
SELECT * FROM ACCOUNT;Faça uma análise semelhante aos casos do exercício anterior considerando a execução de 4 transações ilustrada abaixo, onde T1 e T2 executam concorrentemente seguidas de T3 e T4 que também executam concorrentemente.
Dica: Os casos a considerar para uma execução serializável são 4, tendo em conta a ordem relativa entre T1 e T2, e entre T3 e T4.
Considere o seguinte escalonamento das correspondentes operações de leitura e escrita da DB que estão implícitas nas transações T1 e T2 do exercício anterior:
| T1 | T2 |
|---|---|
| X1 = read(ACCOUNT[a]) | |
| X1.Value = X1.Value - 100; | |
| write(ACCOUNT[a],X1); | |
| X2 = read(ACCOUNT[c]) | |
| X2.Value = X2.Value - 200; | |
| Y1 = read(ACCOUNT[b]) | |
| Y1.Value = Y1.Value + 100; | |
| write(ACCOUNT[b],Y1); | |
| write(ACCOUNT[c],X2) | |
| Y2 = read(ACCOUNT[d]) | |
| Y2.Value = Y2.Value + 200; | |
| write(ACCOUNT[d],Y2); |
Assuma que ambas as transações fazem "commit" com sucesso e justifique se a execução é serializável em cada um dos seguinte casos:
(a) a = 1, b = 2, c = 3, d = 4
(b) a = 1, b = 2, c = 2, d = 3
(c) a = 1, b = 2, c = 1, d = 3
Assuma que T1 é desfeita no fim com ROLLBACK e que T2 completa. Em alguns dos casos (a) a (c) da alínea anterior poderíamos ter uma situação de "leitura suja"?