Salta el contingut
 

Cadenats d'exclusió mútua

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

License

Introducció

Les bases de dades tenen una arquitectura client-servidor, on el servidor és el responsable de gestionar l'accés a les dades. Aquest accés pot ser concurrent, és a dir, que diversos clients accedeixen a les dades al mateix temps. Això pot provocar problemes si no es controla correctament.

Un dels principals problemes derivats de l'accés concurrent és la condició de carrera. Aquesta condició es produeix quan dos o més processos intenten accedir a la mateixa dada al mateix temps, i el resultat depèn de l'ordre en què s'executen els processos.

Exemple

A partir de la base de dades banc definida en el material Transaccions, anem a realitzar dues operacions diferents en un mateix compte bancari de manera concurrent.

Perill

Aquest exemple és purament teòric i no es pot executar directament en una base de dades ja que el motor InnoDB de MySQL bloqueja automàticament les files involucrades en les sentències UPDATE segons el nivell d'aïllament de la transacció.

Vegeu Material d'ampliació per a més informació.

  • Estat inicial 

    SELECT * FROM compte_bancari;
+----+-------+-------+
| id | nom   | saldo |
+----+-------+-------+
|  1 | Pep   |  1000 |
|  2 | Maria |  2000 |
|  3 | Mar   |  3000 |
+----+-------+-------+

  • Operació 1: Transferència de 100€ del compte 'Pep' a 'Maria'. 

    START TRANSACTION;

-- (1)
UPDATE compte_bancari
  SET saldo = saldo - 100
  WHERE nom = 'Pep';

SAVEPOINT diners_retirats;

-- (2)
UPDATE compte_bancari
  SET saldo = saldo + 100
  WHERE nom = 'Maria';

-- (3)
COMMIT;

  • Operació 2: Transferència de 200€ del compte de 'Mar' a 'Pep'. 

    START TRANSACTION;

-- (4)
UPDATE compte_bancari
  SET saldo = saldo - 200
  WHERE nom = 'Mar';

SAVEPOINT diners_retirats;

-- (5)
UPDATE compte_bancari
  SET saldo = saldo + 200
  WHERE nom = 'Pep';

-- (6)
COMMIT;

Si les operacions s'executen en l'ordre (1), (2), (3), (4), (5) i (6), el resultat serà correcte. 

SELECT * FROM compte_bancari;
+----+-------+-------+
| id | nom   | saldo |
+----+-------+-------+
|  1 | Pep   |  1100 |
|  2 | Maria |  2100 |
|  3 | Mar   |  2800 |
+----+-------+-------+

Però si les operacions s'executen en l'ordre (1), (4), (2), (5), (3) i (6), el resultat serà incorrecte.

  • (1): Retira 100€ del compte de 'Pep' (Pep: 900€)
  • (4): Retira 200€ del compte de 'Mar' (Mar: 2800€)
  • (2): Ingressa 100€ al compte de 'Maria' (Maria: 2100€)
  • (5): Ingressa 200€ al compte de 'Pep' (Pep: 1200€)
    • El saldo és 1000€ + 200€, ja que els canvis de la primera transacció no s'han aplicat.
  • (3): Confirma la primera transacció. (Pep: 900€, Maria: 2100€)
  • (6): Confirma la segona transacció. (Pep: 1200€, Mar: 2800€)
SELECT * FROM compte_bancari;
+----+-------+-------+
| id | nom   | saldo |
+----+-------+-------+
|  1 | Pep   |  1200 | -- Incorrecte, no s'han retirat el 100€ de l'operació (1)
|  2 | Maria |  2100 |
|  3 | Mar   |  2800 |
+----+-------+-------+

Per evitar aquest tipus de problemes, es fan servir cadenats d'exclusió mútua (mutex, MUTual EXclusion o locks en anglés). Aquests cadenats permeten que només un procés puga accedir a una dada en un moment determinat, evitant així que diferents operacions concurrents puguen accedir i modificar la mateixa dada al mateix temps.

Bloqueig de taules

En les bases de dades MySQL es poden bloquejar les taules amb la sentència LOCK TABLES.

Documentació

Documentació oficial sobre LOCK TABLES:

La sintaxi és la següent: 

LOCK TABLE
    table_name lock_type
    [, tbl_name [[AS] alias] lock_type] ...

  • table_name: Nom de la taula a bloquejar.
  • lock_type: Tipus de bloqueig a aplicar. Els més comuns són:
    • READ: Bloqueig en mode lectura. No permet cap operació d'escriptura.
    • WRITE: Bloqueig en mode escriptura. Cap altra connexió pot llegir o escriure a la taula.
  • Es poden especificar més taules separades per comes.

Quan una taula és bloquejada, cap altra connexió pot accedir a la taula. Si ho intenta, la connexió quedarà en espera fins que la taula siga desbloquejada.

Per desbloquejar una taula, s'utilitza la sentència UNLOCK TABLES.

Documentació

Documentació oficial sobre UNLOCK TABLES:

La sintaxi és la següent: 

UNLOCK TABLES

Aquesta sentència desbloqueja totes les taules bloquejades per la connexió actual.

No obstant això, aquesta sentència no és necessària si es fa servir la sentència COMMIT o ROLLBACK, ja que aquestes sentències desbloquegen automàticament les taules bloquejades.

Perill

A una transacció, si s'utilitza la sentència LOCK TABLES, totes les sentències han de treballar sobre les taules bloquejades.

Si es tracta d'accedir o modificar una taula que no està bloquejada, es produeix l'error ERROR 1100 (HY000): Table 'table_name' was not locked with LOCK TABLES.

Informació: https://stackoverflow.com/questions/36467298/mysql-table-my-table-was-not-locked-with-lock-tables

Exemple

Si en l'exemple haguerem bloquejat la taula compte_bancari abans de realitzar les operacions, haguérem evitat el problema de la condició de carrera.

  • Estat inicial 

    SELECT * FROM compte_bancari;
+----+-------+-------+
| id | nom   | saldo |
+----+-------+-------+
|  1 | Pep   |  1000 |
|  2 | Maria |  2000 |
|  3 | Mar   |  3000 |
+----+-------+-------+

  • Operació 1: Transferència de 100€ del compte 'Pep' a 'Maria'. 

    START TRANSACTION;

-- (L1)
LOCK TABLES compte_bancari WRITE;

-- (1)
UPDATE compte_bancari
  SET saldo = saldo - 100
  WHERE nom = 'Pep';

SAVEPOINT diners_retirats;

-- (2)
UPDATE compte_bancari
  SET saldo = saldo + 100
  WHERE nom = 'Maria';

-- (3)
UNLOCK TABLES; -- No és necessari
COMMIT;

  • Operació 2: Transferència de 200€ del compte de 'Mar' a 'Pep'. 

    START TRANSACTION;

-- (L2)
LOCK TABLES compte_bancari WRITE;

-- (4)
UPDATE compte_bancari
  SET saldo = saldo - 200
  WHERE nom = 'Mar';

SAVEPOINT diners_retirats;

-- (5)
UPDATE compte_bancari
  SET saldo = saldo + 200
  WHERE nom = 'Pep';

-- (6)
UNLOCK TABLES; -- No és necessari
COMMIT;

En el cas que les operacions s'executen en l'ordre (L1), (1), (L2), (4), (2), (5), (3) i (6), on abans el resultat era incorrecte, ara el resultat és correcte.

  • (L1) La primera operació bloqueja la taula compte_bancari en mode escriptura.
  • (1): Retira 100€ del compte de 'Pep' (Pep: 900€)
  • (L2) La segona intenta bloquejar la taula compte_bancari, però com ja està bloquejada, queda en espera.
  • (2): Ingressa 100€ al compte de 'Maria' (Maria: 2100€)
  • (3): Confirma la primera transacció i desbloqueja la taula. (Pep: 900€, Maria: 2100€)
  • (L2) La taula s'ha desbloquejat, i la segona transacció bloqueja la taula compte_bancari en mode escriptura.
  • (4): Retira 200€ del compte de 'Mar' (Mar: 2800€)
  • (5): Ingressa 200€ al compte de 'Pep' (Pep: 1100€)
    • El saldo és 900€ + 200€, ja que els canvis de la primera transacció ja s'han confirmat.
  • (6): Confirma la segona transacció. (Pep: 1200€, Mar: 2800€)

Bloqueig de files

El bloqueig de taules és una eina molt potent, però també molt restrictiva. Si es bloqueja una taula, cap altra connexió pot accedir a la taula fins que siga desbloquejada. Això pot provocar problemes de rendiment si es bloqueja una taula que és molt utilitzada.

Per aquest motiu, les bases de dades modernes permeten el bloqueig de files, on només es bloqueja la fila que es vol modificar, i no la taula sencera.

En MySQL, el bloqueig de files es pot fer amb la sentència SELECT.

Documentació

Documentació oficial sobre bloqueig de files:

Aquesta sentència sols bloqueja les files que han segut seleccionades.

La sintaxi és la següent: 

SELECT select_statement
  [FOR UPDATE | FOR SHARE | LOCK IN SHARE MODE];

El tipus de bloqueig són:

  • FOR UPDATE: Bloqueig en mode escriptura. La fila no pot ser llegida ni modificada per altres connexions.

    • Les sentències UPDATE adquireixen automàticament aquest bloqueig.
    • FOR SHARE | LOCK IN SHARE MODE: Bloqueig en mode lectura. La fila pot ser llegida, però no pot ser modificada per altres connexions.

Aquest tipus de bloqueig pot afectar múltiples taules si es fan servir sentències JOIN o subconsultes.

No hi ha cap sentència UNLOCK per a desbloquejar les files bloquejades. El bloqueig s'allibera automàticament quan es realitza un COMMIT o un ROLLBACK.

Exemple

En l'exemple anterior, si en lloc de bloquejar la taula compte_bancari s'haguera bloquejat la fila que es volia modificar, les operacions s'hagueren pogut realitzar de manera concurrent sense problemes.

  • Estat inicial 

    SELECT * FROM compte_bancari;
+----+-------+-------+
| id | nom   | saldo |
+----+-------+-------+
|  1 | Pep   |  1000 |
|  2 | Maria |  2000 |
|  3 | Mar   |  3000 |
+----+-------+-------+

  • Operació 1: Transferència de 100€ del compte 'Pep' a 'Maria'. 

    START TRANSACTION;

-- (L1)
SELECT saldo
  FROM compte_bancari
  WHERE nom = 'Pep' or nom = 'Maria'
  FOR UPDATE;

-- (1)
UPDATE compte_bancari
  SET saldo = saldo - 100
  WHERE nom = 'Pep';

SAVEPOINT diners_retirats;

-- (2)
UPDATE compte_bancari
  SET saldo = saldo + 100
  WHERE nom = 'Maria';

-- (3)
UNLOCK TABLES; -- No és necessari
COMMIT;

  • Operació 2: Transferència de 200€ del compte de 'Mar' a 'Pep'. 

    START TRANSACTION;

-- (L2)
SELECT saldo
  FROM compte_bancari
  WHERE nom = 'Mar' or nom = 'Pep'
  FOR UPDATE;

-- (4)
UPDATE compte_bancari
  SET saldo = saldo - 200
  WHERE nom = 'Mar';

SAVEPOINT diners_retirats;

-- (5)
UPDATE compte_bancari
  SET saldo = saldo + 200
  WHERE nom = 'Pep';

-- (6)
UNLOCK TABLES; -- No és necessari
COMMIT;

En el cas que les operacions s'executen en l'ordre (L1), (1), (L2), (4), (2), (5), (3) i (6), on abans el resultat era incorrecte en el primer exemple, ara el resultat és correcte.

  • (L1) Bloqueja les files dels comptes de 'Pep' i 'Maria' en mode escriptura.
  • (1): Retira 100€ del compte de 'Pep' (Pep: 900€)
  • (L2) Intenta bloquejar les files dels comptes de 'Mar' i 'Pep', però com ja estan bloquejades, queda en espera.
  • (2): Ingressa 100€ al compte de 'Maria' (Maria: 2100€)
  • (3): Confirma la primera transacció i desbloqueja la taula. (Pep: 900€, Maria: 2100€)

  • (L2) La primera operació s'ha confirmat i s'han alliberat les files bloquejades. La segona transacció bloqueja les files dels comptes de 'Mar' i 'Pep' en mode escriptura.

  • (4): Retira 200€ del compte de 'Mar' (Mar: 2800€)

  • (5): Ingressa 200€ al compte de 'Pep' (Pep: 1100€)
    • El saldo és 900€ + 200€, ja que els canvis de la primera transacció ja s'han confirmat.
  • (6): Confirma la segona transacció. (Pep: 1200€, Mar: 2800€)

Material d'ampliació

Documentació

Documentació oficial sobre nivells d'aïllament de transaccions:

Documentació oficial sobre bloqueig de taules implícit:

Bibliografia

📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web.
🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.
 
CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Joan Puigcerver Ibáñez