Aulas práticas - Ficha 6

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)

0. Código SQL de apoio

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)

1. Uso de START TRANSACTION, COMMIT e ROLLBACK

  1. Execute a seguinte sequências de instruções, notando que esta se inicia com 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 |
+-----------+-------+

  2. 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.

  3. Agora 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.

2. Revisão do código de "transfer"

Recarregue account.sql e considere o código inicial do procedimento transfer.

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, executando:

CALL transfer(4, 5, 100, @done);
SELECT @done;
SELECT * FROM ACCOUNT;

deverá 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)

  1. 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.

  2. 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.

  3. 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.

3. Serialização de transações concorrentes

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 falhar (no sentido de serem desfeitas com ROLLBACK) nas condições descritas no exercício anterior (ids de contas não existentes; saldo insuficiente nas contas debitadas).

Assuma o estado inicial da BD definido em account.sql (conta AccountId = i tem um saldo de Value = i * 100 para i=1,...,5). Indique se T1 e/ou T2 completam ou desfeita, 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. e considere os 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

Dica: Os estados(s) possíveis são dados por T1 seguida de T2, ou vice-versa. Analise o que deverá obter em cada caso. Para validar a sua análise, se transfer estiver definido correctamente, 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;

4. Serialização de transações concorrentes (cont.)

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, dependendo da ordem relativa dos efeito lógicos de T1 e T2, e de T3 e T4.