Transacciones en base de datos: ACID, niveles de aislamiento y deadlocks

Las transacciones en base de datos son fundamentales para garantizar la consistencia e integridad de datos en aplicaciones modernas. Permiten ejecutar operaciones seguras y confiables incluso cuando múltiples usuarios acceden a los datos simultáneamente.

En una base de datos enfrentamos desafíos críticos: ¿cómo garantizar que en una transferencia bancaria no se pierda dinero? ¿Cómo evitamos que dos usuarios modifiquen simultáneamente los mismos datos y se sobrescriban mutuamente? ¿Cómo aseguramos que ante un fallo del sistema no queden estados inconsistentes?

La solución está en las transacciones, el mecanismo fundamental para operaciones seguras en base de datos. Las transacciones siguen las propiedades ACID (Atomicity, Consistency, Isolation, Durability), que garantizan que las operaciones de datos se ejecutan completamente o no se ejecutan en absoluto.

Con múltiples usuarios accediendo simultáneamente emergen nuevos desafíos: los niveles de aislamiento definen cuánto pueden influirse mutuamente las transacciones, desde problemas simples de lectura hasta registros fantasma. Los deadlocks ocurren cuando las transacciones se esperan mutuamente y se bloquean entre sí. La concurrencia optimista ofrece una alternativa moderna al bloqueo pesimista tradicional, detectando y resolviendo conflictos durante la validación.

Estos conceptos no son solo teóricos, sino críticos para sistemas bancarios, plataformas de comercio electrónico, aplicaciones de redes sociales y cualquier software que trabaje de forma confiable con datos persistentes.

¿Qué son las transacciones?

Definición y fundamentos

Una transacción es una unidad lógica de trabajo que consiste en una o más operaciones de base de datos. Las transacciones deben tratarse como una unidad atómica: o se ejecutan todas las operaciones correctamente o ninguna.

Propiedades de las transacciones

-- Transacción como unidad lógica
BEGIN TRANSACTION;

-- Operación 1: Débito de la cuenta
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;

-- Operación 2: Crédito de la cuenta  
UPDATE Konten SET saldo = saldo + 100 WHERE konto_id = 2;

-- Ambas operaciones exitosas o ninguna
COMMIT;
-- o en caso de error: ROLLBACK;

Propiedades ACID

Las propiedades ACID son el fundamento de los sistemas de transacciones confiables. Garantizan que las operaciones de base de datos permanecen consistentes y confiables incluso ante fallos, caídas del sistema o acceso simultáneo de múltiples usuarios. Cada propiedad aborda desafíos específicos: Atomicity previene operaciones parciales, Consistency mantiene reglas de negocio, Isolation protege contra interferencias mutuas y Durability asegura almacenamiento permanente.

Atomicity (Atomicidad)

Atomicity asegura que una transacción se ejecuta completamente o no se ejecuta en absoluto. Esto es crítico para operaciones que constan de varios pasos, como transferencias bancarias, donde el dinero debe debitarse y acreditarse simultáneamente. Sin atomicidad, las operaciones parcialmente completadas en caso de fallos del sistema pueden causar datos inconsistentes.

-- Ejemplo de atomicidad
CREATE TABLE Transaktionen (
    trans_id INT PRIMARY KEY AUTO_INCREMENT,
    von_konto INT,
    zu_konto INT,
    betrag DECIMAL(10,2),
    status VARCHAR(20),
    zeitpunkt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Transferencia atómica
DELIMITER //
CREATE PROCEDURE ueberweisung(
    IN von_konto_id INT,
    IN zu_konto_id INT,
    IN betrag DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Verificar si el saldo es suficiente
    DECLARE aktuelles_saldo DECIMAL(10,2);
    SELECT saldo INTO aktuelles_saldo FROM Konten WHERE konto_id = von_konto_id;
    
    IF aktuelles_saldo < betrag THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unzureichendes Saldo';
    END IF;
    
    -- Debitar dinero
    UPDATE Konten SET saldo = saldo - betrag WHERE konto_id = von_konto_id;
    
    -- Acreditar dinero
    UPDATE Konten SET saldo = saldo + betrag WHERE konto_id = zu_konto_id;
    
    -- Registrar la transacción
    INSERT INTO Transaktionen (von_konto, zu_konto, betrag, status)
    VALUES (von_konto_id, zu_konto_id, betrag, 'ERFOLGREICH');
    
    COMMIT;
END //
DELIMITER ;

-- Invocación
CALL ueberweisung(1, 2, 100.00);

Lo que muestra este ejemplo: el procedimiento almacenado demuestra atomicidad perfecta usando DECLARE EXIT HANDLER FOR SQLEXCEPTION, que ejecuta automáticamente ROLLBACK ante cualquier error. La transferencia completa (validación de saldo, débito, crédito, registro) se trata como una unidad indivisible.

Puntos importantes en este ejemplo:

Manejo de errores: el handler captura todos los errores SQL y garantiza rollback automático
Validación de saldo: antes de ejecutar se verifica que hay suficiente fondos
Completitud: los cuatro pasos deben ejecutarse exitosamente, si no ninguno se ejecuta
Registro: la transacción se registra solo al éxito, garantizando consistencia en la pista de auditoría

Consistency (Consistencia)

Consistency asegura que la base de datos permanece en un estado consistente después de una transacción. Esta propiedad mantiene las reglas de negocio e integridad de datos, garantizando que se respetan todas las restricciones, triggers y relaciones definidas. En una transferencia bancaria significa que el saldo total de todas las cuentas permanece constante y ninguna cuenta puede volverse negativa.

-- Ejemplo de reglas de consistencia
CREATE TABLE Konten (
    konto_id INT PRIMARY KEY,
    inhaber VARCHAR(100),
    saldo DECIMAL(10,2) NOT NULL,
    CHECK (saldo >= 0)  -- El saldo no puede ser negativo
);

CREATE TABLE Ueberweisungsregeln (
    regel_id INT PRIMARY KEY,
    max_betrag_pro_tag DECIMAL(10,2),
    max_anzahl_pro_tag INT
);

-- Transacción que asegura consistencia
DELIMITER //
CREATE PROCEDURE konsistente_ueberweisung(
    IN von_konto_id INT,
    IN zu_konto_id INT,
    IN betrag DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Regla de negocio: verificar importe máximo
    DECLARE max_betrag DECIMAL(10,2);
    SELECT max_betrag_pro_tag INTO max_betrag 
    FROM Ueberweisungsregeln WHERE regel_id = 1;
    
    IF betrag > max_betrag THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Betrag exceeds maximum';
    END IF;
    
    -- Validación de saldo (forzada por CHECK constraint)
    DECLARE aktuelles_saldo DECIMAL(10,2);
    SELECT saldo INTO aktuelles_saldo FROM Konten WHERE konto_id = von_konto_id;
    
    IF aktuelles_saldo < betrag THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;
    
    -- Ejecutar operaciones
    UPDATE Konten SET saldo = saldo - betrag WHERE konto_id = von_konto_id;
    UPDATE Konten SET saldo = saldo + betrag WHERE konto_id = zu_konto_id;
    
    COMMIT;
END //
DELIMITER ;

Lo que muestra este ejemplo: la protección de consistencia se realiza mediante múltiples mecanismos: la restricción CHECK (saldo >= 0) previene saldos negativos, el procedimiento almacenado verifica las reglas de negocio antes de ejecutar, y si se violan se cancela la transacción automáticamente.

Puntos importantes en este ejemplo:

Restricciones de base de datos: el CHECK constraint impone reglas de negocio a nivel de base de datos
Validación de lógica de negocio: el procedimiento implementa reglas de negocio adicionales
Rollback automático: cuando se violan restricciones la transacción se cancela automáticamente
Integridad de datos: múltiples capas de protección de consistencia (restricción + lógica de aplicación)

Aislamiento

Aislamiento garantiza que las transacciones ejecutadas simultáneamente no se interfieran entre sí. Esto previene problemas clásicos de concurrencia como Dirty Reads (lectura de datos no confirmados), Non-Repeatable Reads (resultados diferentes al leer nuevamente) y Phantom Reads (nuevos registros aparecen entre lecturas). El aislamiento se controla mediante diferentes niveles de aislamiento que ofrecen un equilibrio entre consistencia y rendimiento.

-- Ejemplo de aislamiento
-- Sesión 1:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

-- Sesión 2 (paralela):
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

-- Sesión 1 lee datos
SELECT * FROM Konten WHERE konto_id = 1;

-- Sesión 2 modifica datos
UPDATE Konten SET saldo = 1500 WHERE konto_id = 1;
COMMIT;

-- Sesión 1 lee nuevamente (depende del nivel de aislamiento)
SELECT * FROM Konten WHERE konto_id = 1;

Durabilidad

Durability garantiza que los cambios de una transacción se guardan de forma permanente.

-- Ejemplo de durabilidad
-- Después de COMMIT los cambios son permanentes
START TRANSACTION;
UPDATE Konten SET saldo = 2000 WHERE konto_id = 1;
COMMIT;  -- Los cambios ahora son permanentes

-- Incluso ante un fallo del sistema se preservan los cambios
-- (mediante Write-Ahead Logging y otros mecanismos)

Niveles de aislamiento

Los niveles de aislamiento definen cuánto pueden interferir las transacciones ejecutadas simultáneamente. Proporcionan un equilibrio importante entre consistencia de datos y rendimiento del sistema: mayor aislamiento significa más seguridad pero también más sobrecarga y operaciones potencialmente más lentas. La elección del nivel de aislamiento correcto depende de los requisitos específicos de la aplicación y de la consistencia de datos necesaria.

READ UNCOMMITTED

El nivel de aislamiento más bajo, permite “Dirty Reads”. Este nivel se utiliza raramente porque puede resultar en datos inconsistentes, pero maximiza el rendimiento con bloqueos mínimos.

-- Ejemplo READ UNCOMMITTED
-- Sesión 1:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE Konten SET saldo = 500 WHERE konto_id = 1;
-- ¡Aún no confirmado!

-- Sesión 2:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM Konten WHERE konto_id = 1;
-- Lee el valor no confirmado (500) - ¡Dirty Read!

-- Sesión 1:
ROLLBACK;  -- El cambio se deshace

-- Sesión 2 ha leído datos inválidos

Lo que muestra este ejemplo: READ UNCOMMITTED permite leer datos que aún no han sido confirmados. La sesión 2 lee un valor (500) que será deshecho por la sesión 1, resultando en datos inconsistentes.

Puntos clave en este ejemplo:

Problema de Dirty Read: La sesión 2 lee datos no confirmados que luego se invalidan
Ventaja de rendimiento: No se requieren bloqueos, velocidad máxima de lectura
Inconsistencia de datos: Riesgo de resultados de lectura inconsistentes
Caso de uso: Solo para sistemas donde la consistencia absoluta no es crítica

READ COMMITTED

Previene Dirty Reads pero permite Non-Repeatable Reads.

-- Ejemplo READ COMMITTED
-- Sesión 1:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT saldo FROM Konten WHERE konto_id = 1;  -- Lee 1000

-- Sesión 2:
START TRANSACTION;
UPDATE Konten SET saldo = 1500 WHERE konto_id = 1;
COMMIT;

-- Sesión 1 lee nuevamente:
SELECT saldo FROM Konten WHERE konto_id = 1;  -- Ahora lee 1500
-- ¡Non-Repeatable Read!

REPEATABLE READ

Previene Dirty Reads y Non-Repeatable Reads pero permite Phantom Reads.

-- Ejemplo REPEATABLE READ
-- Sesión 1:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM Konten WHERE inhaber LIKE 'A%';  -- Lee 3 cuentas

-- Sesión 2:
START TRANSACTION;
INSERT INTO Konten VALUES (4, 'Anna Schmidt', 2000);
COMMIT;

-- Sesión 1 lee nuevamente:
SELECT * FROM Konten WHERE inhaber LIKE 'A%';  -- Aún 3 cuentas
-- La nueva fila no se ve (sin Phantom Read en MySQL)

SERIALIZABLE

El nivel de aislamiento más alto, previene todas las anomalías.

-- Ejemplo SERIALIZABLE
-- Sesión 1:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT AVG(saldo) FROM Konten;  -- Calcula el promedio

-- Sesión 2:
START TRANSACTION;
INSERT INTO Konten VALUES (5, 'Bernd Mueller', 3000);
-- ¡Espera hasta que la sesión 1 termine!

-- Sesión 1:
COMMIT;

-- Sesión 2 puede continuar ahora
COMMIT;

Control de concurrencia

Control de concurrencia pesimista

Bloquea recursos de forma proactiva para evitar conflictos.

-- Ejemplo de bloqueo pesimista
-- Bloqueos explícitos
START TRANSACTION;

-- Bloquear la fila
SELECT * FROM Konten WHERE konto_id = 1 FOR UPDATE;

-- Otras transacciones deben esperar
-- Sesión 2:
SELECT * FROM Konten WHERE konto_id = 1 FOR UPDATE;
-- Espera hasta que sesión 1 se confirme o se revierta

-- Realizar operaciones
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;

COMMIT;  -- El bloqueo se libera

Control de concurrencia optimista

Asume que los conflictos son raros y los resuelve cuando es necesario. Este enfoque es especialmente efectivo en sistemas con muchas operaciones de lectura pero pocas de escritura. En lugar de bloquear recursos de forma proactiva (bloqueo pesimista), los conflictos se detectan durante la verificación y la transacción se repite si es necesario. Esto reduce la sobrecarga de los bloqueos y mejora la escalabilidad.

-- Control de concurrencia optimista con columna de versión
CREATE TABLE Produkte (
    produkt_id INT PRIMARY KEY,
    name VARCHAR(100),
    preis DECIMAL(10,2),
    bestand INT,
    version INT DEFAULT 0
);

-- Actualización con verificación de versión
DELIMITER //
CREATE PROCEDURE update_produkt_optimistic(
    IN produkt_id INT,
    IN neuer_preis DECIMAL(10,2),
    IN erwartete_version INT
)
BEGIN
    DECLARE affected_rows INT;
    
    START TRANSACTION;
    
    UPDATE Produkte 
    SET preis = neuer_preis, version = version + 1
    WHERE produkt_id = produkt_id AND version = erwartete_version;
    
    SET affected_rows = ROW_COUNT();
    
    IF affected_rows = 0 THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Concurrency conflict - product was modified';
    ELSE
        COMMIT;
    END IF;
END //
DELIMITER ;

-- Uso
-- Primero leer
SELECT produkt_id, name, preis, version FROM Produkte WHERE produkt_id = 1;

-- Actualizar con versión esperada
CALL update_produkt_optimistic(1, 29.99, 5);

Lo que muestra este ejemplo: El control de concurrencia optimista utiliza una columna de versión para detectar conflictos. La sentencia UPDATE verifica si la versión esperada sigue siendo actual e incrementa la versión en caso de actualización exitosa.

Puntos clave en este ejemplo:

Columna de versión: Cada cambio incrementa la versión, lo que permite un historial de cambios
Detección de conflictos: La cláusula WHERE verifica la versión antes de actualizar
Sin bloqueos: No se requieren bloqueos explícitos, mejor rendimiento en operaciones de lectura
Lógica de reintento: En caso de conflictos, la aplicación debe reintentar la transacción

Deadlocks

Detección y prevención de deadlocks

Los deadlocks ocurren cuando las transacciones se espera mutuamente y se bloquean. Es un problema clásico en sistemas concurrentes donde dos o más transacciones quedan esperando por recursos que ya están bloqueados por otras transacciones. Los deadlocks causan el estancamiento del sistema y necesitan ser detectados y resueltos automáticamente, generalmente mediante la anulación de una de las transacciones involucradas.

-- Ejemplo de deadlock
-- Session 1:
START TRANSACTION;
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;
-- Espera por konto_id = 2
UPDATE Konten SET saldo = saldo + 100 WHERE konto_id = 2;

-- Session 2 (paralela):
START TRANSACTION;
UPDATE Konten SET saldo = saldo - 50 WHERE konto_id = 2;
-- Espera por konto_id = 1
UPDATE Konten SET saldo = saldo + 50 WHERE konto_id = 1;

-- DEADLOCK! Ambas se esperan mutuamente

Lo que muestra este ejemplo: Un escenario clásico de deadlock: la Session 1 bloquea la cuenta 1 y quiere acceder a la cuenta 2, mientras que la Session 2 bloquea la cuenta 2 y espera poder acceder a la cuenta 1. Las dos transacciones se bloquean mutuamente.

Puntos importantes en este ejemplo:

Circular Wait: Ambas transacciones esperan por recursos que están bloqueados por la otra
Resource Holding: Cada transacción ya posee un bloqueo y espera adquirir otro
Deadlock Detection: La base de datos debe detectar el deadlock y abortar una de las transacciones
Prevention Strategy: Un orden consistente de bloqueos podría evitar este deadlock

Estrategias de prevención de deadlocks

-- 1. Orden consistente de bloqueos
DELIMITER //
CREATE PROCEDURE sichere_ueberweisung(
    IN von_konto_id INT,
    IN zu_konto_id INT,
    IN betrag DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    -- Siempre bloquear en el mismo orden
    IF von_konto_id < zu_konto_id THEN
        SET @lock1 = von_konto_id;
        SET @lock2 = zu_konto_id;
    ELSE
        SET @lock1 = zu_konto_id;
        SET @lock2 = von_konto_id;
    END IF;
    
    START TRANSACTION;
    
    -- Bloqueos en orden consistente
    SELECT * FROM Konten WHERE konto_id = @lock1 FOR UPDATE;
    SELECT * FROM Konten WHERE konto_id = @lock2 FOR UPDATE;
    
    -- Ejecutar operaciones
    IF von_konto_id < zu_konto_id THEN
        UPDATE Konten SET saldo = saldo - betrag WHERE konto_id = von_konto_id;
        UPDATE Konten SET saldo = saldo + betrag WHERE konto_id = zu_konto_id;
    ELSE
        UPDATE Konten SET saldo = saldo + betrag WHERE konto_id = zu_konto_id;
        UPDATE Konten SET saldo = saldo - betrag WHERE konto_id = von_konto_id;
    END IF;
    
    COMMIT;
END //
DELIMITER ;

-- 2. Reintentos basados en timeouts
DELIMITER //
CREATE PROCEDURE ueberweisung_mit_retry(
    IN von_konto_id INT,
    IN zu_konto_id INT,
    IN betrag DECIMAL(10,2)
)
BEGIN
    DECLARE retry_count INT DEFAULT 0;
    DECLARE max_retries INT DEFAULT 3;
    DECLARE deadlock_detected BOOLEAN DEFAULT FALSE;
    
    retry_loop: WHILE retry_count < max_retries DO
        BEGIN
            DECLARE EXIT HANDLER FOR 1213  -- Deadlock error code
            BEGIN
                SET deadlock_detected = TRUE;
                SET retry_count = retry_count + 1;
                IF retry_count < max_retries THEN
                    -- Espera breve antes del reintento
                    DO SLEEP(0.1 * retry_count);
                END IF;
            END;
            
            -- Ejecutar transacción
            CALL sichere_ueberweisung(von_konto_id, zu_konto_id, betrag);
            
            -- Éxito, salir del bucle
            LEAVE retry_loop;
        END;
        
        IF deadlock_detected AND retry_count >= max_retries THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Max retries exceeded';
        END IF;
        
        SET deadlock_detected = FALSE;
    END WHILE;
END //
DELIMITER ;

Monitoreo de deadlocks

-- Obtener información de deadlocks (MySQL)
SHOW ENGINE INNODB STATUS;

-- Monitorear transacciones en deadlock
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- Mostrar estado de bloqueos
SELECT 
    object_name,
    lock_type,
    lock_mode,
    lock_status,
    engine_transaction_id
FROM performance_schema.data_locks
WHERE object_name = 'Konten';

Gestión de transacciones en diferentes bases de datos

MySQL/MariaDB

-- Características específicas de MySQL
-- Controlar autocommit
SET autocommit = 0;  -- Control manual de transacciones
SET autocommit = 1;  -- Autocommit automático (por defecto)

-- Savepoints para rollbacks parciales
START TRANSACTION;
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;
SAVEPOINT sp1;

UPDATE Konten SET saldo = saldo - 50 WHERE konto_id = 2;
SAVEPOINT sp2;

-- Retroceder al savepoint
ROLLBACK TO sp1;
COMMIT;  -- Solo el primer cambio persiste

-- Transacciones XA para sistemas distribuidos
XA START 'xid1';
UPDATE Konten SET saldo = saldo - 100 WHERE konto_id = 1;
XA END 'xid1';
XA PREPARE 'xid1';
XA COMMIT 'xid1';

PostgreSQL

-- Características específicas de PostgreSQL
-- Niveles de aislamiento de transacciones
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Advisory Locks (bloqueos a nivel de aplicación)
SELECT pg_advisory_lock(12345);  -- Adquirir bloqueo
SELECT pg_advisory_unlock(12345);  -- Liberar bloqueo

-- Snapshots de transacciones
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM Konten WHERE konto_id = 1;

-- En otra sesión:
UPDATE Konten SET saldo = 2000 WHERE konto_id = 1;
COMMIT;

-- La sesión original sigue viendo el valor antiguo
SELECT * FROM Konten WHERE konto_id = 1;

Oracle

-- Características específicas de Oracle
-- Transacciones de solo lectura
SET TRANSACTION READ ONLY;
SELECT * FROM Konten;  -- Garantiza una vista consistente

-- Transacciones autónomas
DELIMITER //
CREATE PROCEDURE log_transaktion(
    IN transaktion_id INT,
    IN beschreibung VARCHAR(200)
)
AS
BEGIN
    -- Transacción autónoma
    PRAGMA AUTONOMOUS_TRANSACTION;
    
    INSERT INTO Transaktionslog (trans_id, beschreibung, zeitpunkt)
    VALUES (transaktion_id, beschreibung, SYSTIMESTAMP);
    
    COMMIT;  -- Commit solo para la transacción autónoma
END;
//

-- Savepoints
SAVEPOINT sp1;
-- Operaciones
ROLLBACK TO sp1;

Buenas prácticas para la gestión de transacciones

1. Mantener transacciones cortas

-- Incorrecto: Transacción larga
START TRANSACTION;
SELECT * FROM grosse_tabelle;  -- Consulta larga
-- ... muchas otras operaciones ...
UPDATE kleine_tabelle SET wert = 1;
COMMIT;

-- Correcto: Transacción corta y enfocada
SELECT * FROM grosse_tabelle;  -- Fuera de la transacción
START TRANSACTION;
UPDATE kleine_tabelle SET wert = 1;
COMMIT;

2. Elegir el nivel de aislamiento adecuado

-- Para la mayoría de casos de uso
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Para consultas analíticas
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Para operaciones financieras críticas
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

3. Implementar manejo de errores

-- Manejo robusto de errores
DELIMITER //
CREATE PROCEDURE robusta_transaccion()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
                                   @errno = MYSQL_ERRNO,
                                   @text = MESSAGE_TEXT;
        
        ROLLBACK;
        
        -- Logging
        INSERT INTO error_log (error_time, error_code, error_message)
        VALUES (NOW(), @errno, @text);
        
        -- Propagar error
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Lógica de transacción
    INSERT INTO tabla1 (valor) VALUES (1);
    UPDATE tabla2 SET valor = 2 WHERE id = 1;
    
    COMMIT;
END //
DELIMITER ;

4. Usar Connection Pooling

// Ejemplo en Java con Connection Pool
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class TransactionService {
    private DataSource dataSource;
    
    public void executeInTransaction(TransactionCallback callback) {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            
            callback.execute(conn);
            
            conn.commit();
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            throw new RuntimeException("Transaction failed", e);
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    @FunctionalInterface
    public interface TransactionCallback {
        void execute(Connection conn) throws SQLException;
    }
}

Conceptos relevantes para examen

Propiedades ACID importantes

Propiedad	Descripción	Implementación
Atomicity	Todo o nada	Rollback, Write-Ahead Logging
Consistency	Estado consistente	Constraints, Triggers
Isolation	Sin interferencias	Locks, Isolation Levels
Durability	Almacenamiento permanente	Redo Logs, Checkpoints

Comparativa de Isolation Levels

Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads	Performance
READ UNCOMMITTED	✅	✅	✅	Máxima
READ COMMITTED	❌	✅	✅	Alta
REPEATABLE READ	❌	❌	✅ (MySQL: ❌)	Media
SERIALIZABLE	❌	❌	❌	Mínima

Tareas típicas de examen

Explicar propiedades ACID
Comparar Isolation Levels
Implementar prevención de deadlocks
Seleccionar estrategias de transacción correctas
Analizar problemas de concurrencia

Resumen

La gestión de transacciones es fundamental para aplicaciones de base de datos confiables:

Propiedades ACID garantizan integridad de datos
Isolation Levels controlan el comportamiento concurrente
Prevención de deadlocks asegura estabilidad del sistema
Control de concurrencia Optimistic vs Pessimistic
Best Practices optimizan rendimiento y confiabilidad

Un buen diseño de transacciones requiere comprender los requisitos de la aplicación y los mecanismos subyacentes de la base de datos.

Lecturas recomendadas: Bases de datos

Keine Bücher für Kategorie "datenbanken" gefunden.

Täglicher Artikel

Transacciones en base de datos: ACID, niveles de aislamiento y deadlocks

¿Qué son las transacciones?

Definición y fundamentos

Propiedades de las transacciones

Propiedades ACID

Atomicity (Atomicidad)

Consistency (Consistencia)

Aislamiento

Durabilidad

Niveles de aislamiento

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

Control de concurrencia

Control de concurrencia pesimista

Control de concurrencia optimista

Deadlocks

Detección y prevención de deadlocks

Estrategias de prevención de deadlocks

Monitoreo de deadlocks

Gestión de transacciones en diferentes bases de datos

MySQL/MariaDB

PostgreSQL

Oracle

Buenas prácticas para la gestión de transacciones

1. Mantener transacciones cortas

2. Elegir el nivel de aislamiento adecuado

3. Implementar manejo de errores

4. Usar Connection Pooling

Conceptos relevantes para examen

Propiedades ACID importantes

Comparativa de Isolation Levels

Tareas típicas de examen

Resumen

Lecturas recomendadas: Bases de datos

Nächster Artikel in Bases de Datos

Entradas relacionadas

Normalización de Bases de Datos: 1NF, 2NF, 3NF y BCNF

SQL vs NoSQL: Comparación de Bases de Datos

ER-Modelos: Entity Relationship y Cardinalidad