Introducció
En aquest tema, explorarem les transaccions i la concurrència en PostgreSQL. Les transaccions són una característica fonamental de qualsevol sistema de bases de dades relacional, ja que permeten agrupar múltiples operacions SQL en una sola unitat de treball. La concurrència, d'altra banda, es refereix a la capacitat de la base de dades per gestionar múltiples operacions simultànies de manera segura i eficient.
Objectius
- Entendre què són les transaccions i com funcionen.
- Aprendre a utilitzar les instruccions
BEGIN
,COMMIT
iROLLBACK
. - Comprendre els nivells d'aïllament de les transaccions.
- Gestionar la concurrència utilitzant bloquejos i altres mecanismes.
Conceptes Clau
Què és una Transacció?
Una transacció és una seqüència d'una o més operacions SQL que es tracten com una unitat indivisible. Les transaccions segueixen el model ACID:
- Atomicitat: Totes les operacions dins d'una transacció es completen amb èxit o cap d'elles ho fa.
- Consistència: Les transaccions porten la base de dades d'un estat vàlid a un altre estat vàlid.
- Aïllament: Les operacions dins d'una transacció són invisibles per a altres transaccions fins que es completen.
- Durabilitat: Un cop una transacció es completa, els canvis són permanents.
Instruccions de Transacció
BEGIN
: Inicia una nova transacció.COMMIT
: Finalitza la transacció actual i fa permanents tots els canvis.ROLLBACK
: Anul·la la transacció actual i desfà tots els canvis.
Exemple de Transacció
BEGIN; INSERT INTO comptes (usuari, saldo) VALUES ('johndoe', 1000); UPDATE comptes SET saldo = saldo - 100 WHERE usuari = 'johndoe'; UPDATE comptes SET saldo = saldo + 100 WHERE usuari = 'janedoe'; COMMIT;
En aquest exemple, si qualsevol de les operacions falla, podem utilitzar ROLLBACK
per desfer tots els canvis.
Nivells d'Aïllament de les Transaccions
PostgreSQL suporta quatre nivells d'aïllament:
- Read Uncommitted: Les transaccions poden veure canvis no confirmats d'altres transaccions.
- Read Committed: Les transaccions només poden veure canvis confirmats d'altres transaccions (nivell per defecte).
- Repeatable Read: Les transaccions veuen un instantani consistent de la base de dades en el moment en què comencen.
- Serializable: Les transaccions es processen de manera que sembla que s'executen seqüencialment.
Exemple de Nivells d'Aïllament
Concurrència
Bloquejos
PostgreSQL utilitza diversos tipus de bloquejos per gestionar la concurrència:
- Bloquejos de fila: Utilitzats per operacions que modifiquen files específiques.
- Bloquejos de taula: Utilitzats per operacions que afecten una taula sencera.
Exemple de Bloquejos
Consells per Gestionar la Concurrència
- Utilitza nivells d'aïllament adequats per a les teves necessitats.
- Evita transaccions llargues per reduir la probabilitat de bloquejos.
- Utilitza
LOCK
amb precaució per evitar bloquejos morts (deadlocks).
Exercicis Pràctics
Exercici 1: Crear una Transacció
- Inicia una transacció.
- Insereix una nova fila en una taula.
- Actualitza una fila existent.
- Confirma la transacció.
Solució
BEGIN; INSERT INTO comptes (usuari, saldo) VALUES ('newuser', 500); UPDATE comptes SET saldo = saldo + 200 WHERE usuari = 'existinguser'; COMMIT;
Exercici 2: Gestionar un Error en una Transacció
- Inicia una transacció.
- Insereix una nova fila en una taula.
- Intenta actualitzar una fila inexistent.
- Anul·la la transacció si hi ha un error.
Solució
BEGIN; INSERT INTO comptes (usuari, saldo) VALUES ('newuser', 500); -- Aquesta operació fallarà si 'nonexistentuser' no existeix UPDATE comptes SET saldo = saldo + 200 WHERE usuari = 'nonexistentuser'; -- Si hi ha un error, anul·la la transacció ROLLBACK;
Resum
En aquest tema, hem après sobre les transaccions i la concurrència en PostgreSQL. Hem vist com utilitzar les instruccions BEGIN
, COMMIT
i ROLLBACK
, així com els diferents nivells d'aïllament de les transaccions. També hem explorat com gestionar la concurrència utilitzant bloquejos. Aquests conceptes són fonamentals per assegurar la integritat i la consistència de les dades en aplicacions que utilitzen PostgreSQL.
Curs de PostgreSQL
Mòdul 1: Introducció a PostgreSQL
Mòdul 2: Operacions bàsiques de SQL
Mòdul 3: Consultes SQL avançades
Mòdul 4: Disseny de bases de dades i normalització
Mòdul 5: Funcionalitats avançades de PostgreSQL
Mòdul 6: Optimització i millora del rendiment
- Optimització de consultes
- Estratègies d'indexació
- Analitzant el rendiment de les consultes
- Vacuuming i manteniment
Mòdul 7: Seguretat i gestió d'usuaris
- Rols d'usuari i permisos
- Mètodes d'autenticació
- Encriptació de dades
- Còpia de seguretat i restauració
Mòdul 8: Treballant amb JSON i funcionalitats NoSQL
Mòdul 9: Extensions i eines avançades
- PostGIS per a dades geoespacials
- Cerca de text complet
- Wrappers de dades externes
- PL/pgSQL i altres llenguatges procedimentals