Aulas práticas - Ficha 10

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. Familiarização com START TRANSACTION, COMMIT e ROLLBACK

  1. 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 |
    +-----------+-------+
  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. 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)
  3. 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 |
    +-----------+-------+

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

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

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;

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, tendo em conta a ordem relativa entre T1 e T2, e entre T3 e T4.

5. Escalonamentos serializáveis

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

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