Material: Cadenats d'exclusió mútua

<div class="page"> <div class="cover text-center"> <img class="mx-auto" src=/itb/images/logo_mislata.png alt="logo"> # Cadenats d'exclusió mútua <div class="text-end fit-content ms-auto my-3 mt-auto pt-3"> <p><strong>Autor:</strong> Joan Puigcerver Ibáñez</p> <p><strong>Correu electrònic:</strong> j.puigcerveribanez@edu.gva.es</p> <p><strong>Curs:</strong> 2023/2024</p> </div> <div> <p class="fw-bold mb-0">Llicència: BY-NC-SA</p> <p class="d-none d-md-block">(Reconeixement - No Comercial - Compartir Igual)</p> <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.ca" target="_blank"> <img class="mx-auto" src="/itb/images/license.png" alt="Licence"/> </a> </div> </div> </div> {:toc} ## 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. ::::: example A partir de la base de dades `banc` definida en el [material Transaccions](/itb/DAM-BD/UD6/materials/01_transactions.html), anem a realitzar dues operacions diferents en un mateix compte bancari de manera concurrent. :::: danger 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ó](#material-d'ampliacio) per a més informació. :::: - __Estat inicial__ ```sql SELECT * FROM compte_bancari; +----+-------+-------+ | id | nom | saldo | +----+-------+-------+ | 1 | Pep | 1000 | | 2 | Maria | 2000 | | 3 | Mar | 3000 | +----+-------+-------+ ``` <div class="row row-cols-1 row-cols-md-2"> <div class="col"> - __Operació 1__: Transferència de 100€ del compte 'Pep' a 'Maria'. ```sql 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; ``` </div> <div class="col"> - __Operació 2__: Transferència de 200€ del compte de 'Mar' a 'Pep'. ```sql 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; ``` </div> </div> Si les operacions s'executen en l'ordre __(1), (2), (3), (4), (5) i (6)__, el resultat serà correcte. ```sql 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€)__{.red} - 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€)__ ```sql 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`. ::: docs Documentació oficial sobre `LOCK TABLES`: - MariaDB: https://mariadb.com/kb/en/lock-tables/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html ::: La sintaxi és la següent: ```sql 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`. ::: docs Documentació oficial sobre `UNLOCK TABLES`: - MariaDB: https://mariadb.com/kb/en/transactions-unlock-tables/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html ::: La sintaxi és la següent: ```sql 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. ::: danger 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 ::: ::: example 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__ ```sql SELECT * FROM compte_bancari; +----+-------+-------+ | id | nom | saldo | +----+-------+-------+ | 1 | Pep | 1000 | | 2 | Maria | 2000 | | 3 | Mar | 3000 | +----+-------+-------+ ``` <div class="row row-cols-1 row-cols-md-2"> <div class="col"> - __Operació 1__: Transferència de 100€ del compte 'Pep' a 'Maria'. ```sql 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; ``` </div> <div class="col"> - __Operació 2__: Transferència de 200€ del compte de 'Mar' a 'Pep'. ```sql 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; ``` </div> </div> 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€)__{.green} - 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`. ::: docs Documentació oficial sobre bloqueig de files: - MariaDB: - `LOCK IN SHARE MODE`: https://mariadb.com/kb/en/lock-in-share-mode/ - `FOR UPDATE`: https://mariadb.com/kb/en/for-update/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html ::: Aquesta sentència sols bloqueja les files que han segut seleccionades. La sintaxi és la següent: ```sql 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`. ::: example 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__ ```sql SELECT * FROM compte_bancari; +----+-------+-------+ | id | nom | saldo | +----+-------+-------+ | 1 | Pep | 1000 | | 2 | Maria | 2000 | | 3 | Mar | 3000 | +----+-------+-------+ ``` <div class="row row-cols-1 row-cols-md-2"> <div class="col"> - __Operació 1__: Transferència de 100€ del compte 'Pep' a 'Maria'. ```sql 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; ``` </div> <div class="col"> - __Operació 2__: Transferència de 200€ del compte de 'Mar' a 'Pep'. ```sql 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; ``` </div> </div> 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€)__{.green} - 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ó ::: docs Documentació oficial sobre nivells d'aïllament de transaccions: - MariaDB: https://mariadb.com/kb/en/transaction-isolation-levels/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html Documentació oficial sobre bloqueig de taules implícit: - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html ::: ## Bibliografia - https://ca.wikipedia.org/wiki/Cadenat_(inform%C3%A0tica)