Basistransacties in MySQL: voltooi ACID, aislamiento en autocommit

Home » Python » Basistransacties in MySQL: voltooi ACID, aislamiento en autocommit

Transacties in MySQL zijn mogelijk om met bepaalde gegevens te werken terwijl er verschillende vonnissen tussenbeide komen die als een enkele eenheid worden beschouwd. Als je op de camino valt, kun je de basis van de data in de media verlaten. In dit artikel vindt u een volledige uitleg en praktische werking van alle functies, die u kunt uitbreiden en configureren om consistentie en prestaties in evenwicht te brengen.

De basis van het gebruik ervan is dat ze worden gebruikt START TRANSACTIE, COMMIT en ROLLBACK, veremos el papel de automatisch vastleggen, niveles de aislamiento ACID, sluizen, spaarpunten, modos de acceso y TRANSACTIE INSTELLEN. Het bevat ook enkele voorbeelden van problemen met bepaalde soorten concurrencia, niet nuchtere InnoDB versus MyISAM, zoals het uitvoeren van transacties PHP (mysqli) en in opgeslagen proceduresAls u een praktische batterij gebruikt, zodat u deze tijdens de vakantie kunt gebruiken. Als u DAW, DAM of ASIR voorbereidt, kunnen wij u helpen dit te doen.

Apunten van BD voor DAW, DAM en ASIR — Cursus 2025/2026. Dit materiaal is geschikt voor de meeste praktische en praktische toepassingen dat is nodig om te kunnen werken met professionele MySQL-transacties.

notitie: Het diner doneert ons aan het beheren van nieuwe websites, zoals het genereren van meer inhoud. U kunt uw eigen technologie en al die dagen creëren.

Het is een transactie en een import

Una transacción es una unidad lógica de trabajo dat er verschillende formele vonnissen bestaan Er zijn geen toepassingen of er zijn geen toepassingen. Dit is inconsistent, vooral in operaties die kunnen variëren van tabbladen tot bestanden. Piensa en een transactie tussen bankbiljetten: se descuenta de la cuenta origine en se abona in la destino; Als een van de gevallen gevolgen heeft, kun je niet toestaan ​​dat de andere bevestiging wordt bevestigd.

MySQL, met de motor InnoDB, voer transacties op basis van een model uit ACID, de garantie op een solide integratie omvat ook de val van het systeem of de luz-cortes. Als u andere SGBD's bekijkt, voelt u zich als een huis: de concepten die u gebruikt, zijn de beste.

Propedades ACID: de garantie is gegarandeerd

• Atomiciteit: het blok is ondeelbaar; o todo oké o nada. Als een operatie mislukt, moet u de conjunctie opnieuw bekijken.
• consistentie: elke transactie kan de gegevensbasis van een andere geldige status verwijderen. Geen probleem met het onderhandelen over onmogelijkheden.
• isolatie: wat er gebeurt tijdens een transactie er is geen sprake van een versperring Als andere mensen een parallelle reactie krijgen, dan is dit een elegido.
• Duurzaamheid: una vez haces COMMIT, de cambios blijven bestaan incluso ante fallos.

InnoDB versus MyISAM: de motor is belangrijk

En MySQL existen varios opslag engines. Solo InnoDB voert transacties en claves uit; Het ademen van een bloqueo van het leven en het herstel vóór de herfst. MyISAM biedt geen transacties aan Als u prioriteit geeft aan een consistente snelheid, is het nuttig om scenario's van intensieve lessen te volgen, maar Het is niet voldoende als ZUUR nodig is.

Autocommit: dat is wat het kan beïnvloeden

MySQL tiene el modo autocommit activering door defect. Het betekent dat een expliciete transactie wordt uitgevoerd, Cada Sentencia DML wordt automatisch bevestigd como si estuvisee envuelta por START TRANSACTION y COMMIT. Si la sentencia falla, se revierte automáticamente Het is de bedoeling dat u dit niet doet, maar dat u dit niet handmatig kunt doen ROLLBACK.

Als u een transactie met een transactie begint START TRANSACTION, MySQL deactiveert tijdelijk het automatisch vastleggen ervan COMMIT o ROLLBACK. U kunt een automatische vastlegging doen tijdens een sessie met SET:

-- Ver el valor actual
SELECT @@autocommit;

-- Desactivar autocommit en la sesión actual
SET autocommit = 0;

-- Activarlo de nuevo
SET autocommit = 1;

Na autocommit deactiverenEr zijn geen permanente cambios die worden uitgestoten COMMIT; si quieres descartarlos, toepassingen ROLLBACK. Tien keer dat deze configuratie tijdens een sessie is uitgevoerd als u de verbinding opnieuw wilt instellen.

Basisprincipes: START TRANSACTIE, COMMIT en ROLLBACK

El ciclo típico es: Begin de transactie, voer DML-bewerkingen uit en bevestig of ontgrendel Begin met het resultaat. Tientallen zonden zijn het gevolg BEGIN o BEGIN WORK para iniciar, y COMMIT/ROLLBACK para finalizar:

START TRANSACTION;
  UPDATE cuentas SET saldo = saldo - 100 WHERE id = 20;
  UPDATE cuentas SET saldo = saldo + 100 WHERE id = 30;
COMMIT; -- o ROLLBACK si algo fue mal

Als het systeem tussen de twee doseringen zit UPDATE Als u de verbinding wilt maken met deactivering van automatische toewijzing, InnoDB garandeert dat de transactie onvolledig is en niet wordt bevestigd. Als er geen beperking bestaat (p. ej., CHECK de saldo no negativo) falla, la transacción se revierte y la base queda como al principio.

Savepoints: beheer het laatste deel van de transactie

met SAVEPOINT Er zijn steeds meer recuperatiepunten tijdens een transactie die mogelijk is deshacer parcialmente sin tirar todo el trabajo:

START TRANSACTION;
  UPDATE productos SET stock = stock - 2 WHERE id = 10;
  SAVEPOINT p1;
  UPDATE productos SET stock = stock - 5 WHERE id = 11;
  -- Si falla lo siguiente, solo deshacemos hasta p1
  ROLLBACK TO SAVEPOINT p1;
  -- Seguimos con otras operaciones
RELEASE SAVEPOINT p1;
COMMIT;

Si definieert verschillende SAVEPOINT met de andere naam, MySQL beschouwt het als het ultieme. También puedes eliminarlos con RELEASE SAVEPOINT om de transactie te vereenvoudigen.

Niveles de aislamiento: evenwicht tussen consistentie en rendement

De waarde van MySQL is defect REPEATABLE READEr zijn veel coherente lezingen over de vele transacties met multiversioning (MVCC).

Klassieke concurrencia-problemen que queremos acotar:

• Lectura sucia (Dirty Read): leer cambio's van andere transacties die niet goed zijn COMMIT.
• Lectura no repetible: leer de verschillende soorten zaken en verkrijg onderscheidende waarden door actualizaciones confirmadas de andere transacties tijdens de lezingen.
• Lectura fantasma: ejecutar la misma consulta y que aparezcan filas nuevas Ik vraag de invoeging te bevestigen voor andere transacties.

Comportamiento por nivel (resumen práctico): READ UNCOMMITTED permite las tres anomalías; READ COMMITTED evita lecturas sucias pero puede sufrir geen herhalingen en fantasieën; REPEATABLE READ evita sucias y no repetibles y, in het algemeen, geeft solo potentiële “fantasmen” toe; SERIALIZABLE het blok komt uit een serie, elimineer de kosten van meer blokken.

-- Consultar nivel de aislamiento (sesión y global)
SELECT @@transaction_isolation;     -- sesión
SELECT @@global.transaction_isolation; -- global

U kunt het geld verdienen door transacties, een wereldwijde transactie. Ontdek dat de mondiale cambio's invloed hebben op nieuwe verbindingen, no a las existentes.

Bloqueos en lecturas con bloqueo

Voor het onderhoud van de auto in de Verenigde Staten bloqueos (vergrendeling). InnoDB-bloque is defect nivel de fila, wat de burgemeester toestaat dat het bloqueo por tabla is. Een blok dat andere transacties mogelijk maakt, veranderen gegevens tijdens het gebruik er is nog geen einde gekomen aan het proces.

Según el nivel en la operación, MySQL kan schrijf- en schrijfblokken vereisen. Bijvoorbeeld SERIALIZABLE met het automatisch uitschakelen van de opdracht kunnen lezingen worden geblokkeerd om de consistentie te hervormen. Het doel is om te voorkomen dat andere transacties tussen uw gegevens “instant” plaatsvinden.

Toegangsmodus: LEES SCHRIJF en ALLEEN LEZEN

Transacties kunnen in de mode worden aangegeven READ WRITE (standaard) of READ ONLY. En modo solo lectura, no se permiten cambios eenvoudige tabellen, tot u reproduceerbare en stabiele informatie krijgt.

-- Establecer modo de acceso al iniciar
START TRANSACTION READ ONLY;
-- o
START TRANSACTION READ WRITE;

También puedes fijarlo con SET TRANSACTION, combinándolo con el nivel de aislamiento y el strekking van toepassing.

TRANSACTIE INSTELLEN: aislamiento, acceso y ámbitos (SESSION, GLOBAL)

Zin SET TRANSACTION sta toe dat u het islamitisch systeem en de toegangsmodus voor la próxima transacción, Voor het geheel sessie of manier globaal:

-- Aplica a la próxima transacción (si no indicas ámbito)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY;

-- Aplica a todas las transacciones de esta sesión actual
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE;

-- Aplica a sesiones futuras (no a las existentes)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Este control fino te permite Zorg voor een evenwichtige betrouwbaarheid en rendement in het gebruiksgeval, met veel analyses van meer concurrencia OLTP.

Gelijksoortige voorbeelden: Dirty Read, No Repetible en Fantasma

Dirty Read (lectura sucia): een transactie B wordt niet bevestigd door A. Con READ UNCOMMITTED esto es posible; con READ COMMITTED en adelante, nee.

-- Terminal A
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
-- (sin COMMIT todavía)

-- Terminal B
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT saldo FROM cuentas WHERE id = 1; -- puede ver el saldo "sucio"

-- Terminal A
ROLLBACK; -- los cambios se deshacen

Lectura no repetible: B bevestigt een UPDATE tussen de lezingen van A. Con REPEATABLE READ lo evitas; con READ COMMITTED puede ocurrir.

-- Terminal A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT saldo FROM cuentas WHERE id = 1; -- lectura 1

-- Terminal B (otra sesión)
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
COMMIT;

-- Terminal A
SELECT saldo FROM cuentas WHERE id = 1; -- lectura 2 (puede diferir)
ROLLBACK;

Lectura fantasmaWanneer u gegevens raadpleegt, of andere transacties invoegt die in het filter worden geplaatst. Con SERIALIZABLE se evita a costa de más bloqueos.

-- Terminal A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT SUM(saldo) FROM cuentas;

-- Terminal B
INSERT INTO cuentas(id, saldo) VALUES (4, 3000);
COMMIT;

-- Terminal A
SELECT SUM(saldo) FROM cuentas; -- puede observar un "fantasma"
ROLLBACK;

Essentiële DML en sleutelwoorden: effecten OP VERWIJDEREN / OP UPDATE

El DML agrupa SELECTEREN, INVOEGEN, BIJWERKEN en VERWIJDEREN. Bij het definiëren van de criteria in InnoDB kunt u het volgende vergelijken met de Borrados/actualisaties op de referentietabel:

• RESTRICT: impide elimineren/actualiseren van referenties. Het is de waarde van MySQL.
• CASCADE: propageer de aanval op de bestanden.
• SET NULL: pone el valor a NULL en las filas hijas.
• NO ACTION: equivalente a RESTRICT in MySQL.
• SET DEFAULT: is niet beschikbaar met InnoDB en MySQL.

Estas reglas son clave para Zorg voor een consistente referentie U kunt dat soort zaken vermijden door transacties met transacties en volledige relaties uit te voeren.

Transacties via PHP (mysqli): autocommit, commit en rollback

Si programas en PHP con mysqli, u kunt de forma sencilla-transacties controleren. Autocommit deactiverenU kunt ons raadplegen en bevestigen of het resultaat bekijken:

$db = new mysqli("localhost", "root", "pass", "database");
$db->autocommit(false);

try {
  $db->query("INSERT INTO users (name) VALUES ('marcus')");
  $db->query("UPDATE users SET name = 'jane' WHERE id = 39");
  $db->commit();
} catch (Throwable $e) {
  $db->rollback();
  // log del error
}

Met dit patroon, si cualquier sentencia falla, haces rollback() y evitas inconsistencias. U besluit of u uw intenties hervat, meldt of andere ongelukken.

Voer alle procedures uit en beheer fouten in MySQL

De MySQL-procedure kan worden aangegeven manejadores de errores voor SQLEXCEPTION y SQLWARNING, zodat ejecteert ROLLBACK automáticamente ante fallos:

DELIMITER //
CREATE PROCEDURE transferir(IN p_origen INT, IN p_destino INT, IN p_importe DECIMAL(10,2))
BEGIN
  DECLARE exit handler FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
  END;

  START TRANSACTION;
    UPDATE cuentas SET saldo = saldo - p_importe WHERE id = p_origen;
    UPDATE cuentas SET saldo = saldo + p_importe WHERE id = p_destino;
  COMMIT;
END //
DELIMITER ;

U kunt een unieke manejador gebruiken om verschillende fouten te voorkomen. Het doel is om de omkering centraal te stellen en de basis te verbreken cuando algo no va bien.

Praktische praktijkvoorbeelden: op het einde van de maand

Tienda de informática

• Inserta fabricantes indicatie code en naam; y también solo con nombre.
• Inserta productos er zijn verschillende soorten kolomkoppelingen (met en zonder code).
• Crea la tabla fabricante_productos met kolommen nombre_fabricante, nombre_producto, precio e Voer een van de vele registers in van tienda.
• Crea la vista vista_fabricante_productos met de drie kolommen anteriores.
• Elimina fabricantes als Asus o Xiaomi y razona por que puede no ser posible; ajusta claves foráneas (p. ej., ON DELETE) si hace falta.
• Actualiza códigos de fabricantes (Lenovo tot 20, Huawei a 30) en analyseer de noodzakelijke verwijzingen.
• Actualiza precios sumando 5 € per product; elimina impresoras con precio < 200 €.

Empleados

• Inserta departamentos verschillende combinaties van kolommen (met een code, met gasten).
• Inserta emleados vinculados a departamentos (con y sin código explícito).
• Crea y rellena departamento_backup van departamento.
• Elimina departamentos (Proyectos, Desarrollo) y justifica si se puede o no; het is noodzakelijk om dit aan te passen.
• Actualiza códigos (bijv. Recursos Humanos naar 30; Publicidad a 40) Evaluatie van de impact van referentie.
• Incrementa presupuestos +50.000 € solo voor < 20.000 €.
• Transactie: het elimineren van medewerkers die een samenhangende consistentie garanderen.

Jardineria

• Inserta oficina en Almería en een vertegenwoordiger van de ventilatie.
• Voeg klant toe cuyo commercial sea el emmpleado anterior; crea un pedido con al menos dos productos.
• Actualiseer de klantcode y verifica cambios en tablas relacionadas; verwijder het en herziene effecten; si geen hooicascadas, configureren ON DELETE CASCADE.
• Klanten verwijderen zonde pedidos; sube un 20% de productwaarde is onbetaalbaar; borra pagos van de klant met een beperkte kredietlimiet.
• Ajusta límite de crédito een 0 voor de klant met een eenheid van product 11679.
• Modifica la tabla detalle_pedido añadiendo campo iva; bemiddel een transactie 18 a pedidos desde enero 2009 y 21 aan de rest.
• voegt campo total_linea y calcula con precio_unidad*cantidad*(1 + (iva/100)) para todos los registros.
• Borra el cliente met een beperkte kredietlimiet: evalueer of u met een enkel consult en door dat kunt doen.
• Inserta oficina en Granada con tres comerciales y drie klanten asociados; transactie voor een kinderfiets voor klanten met hun producten; borra un cliente y ajusta foráneas si no hooi cascada; transactie voor de registratie van uw kinderpedidos.

Ensayo adicional útil: simula un pérdida de conexión con SET AUTOCOMMIT = 0, voordat de sessie wordt afgerond en u opnieuw verbinding maakt met het feit dat u blijft doorgaan en dat u nee zegt. Dit is het concept van duurzaamheid en atomicidad.

Vergelijk snel met SQL Server en Oracle

Los conceptos son homologos: BEGIN/START TRANSACTIE, COMMIT, TERUGROLLEN y isolatieniveaus. Een SQL Server bestaat als nooit tevoren MOMENTOPNAMEDat presenteer een consistent uitzicht op het begin van de transactie sin bloquear tanto como SERIALISEERBAAR. Orakel biedt een combinatie van soortgelijke de nivelles; in de praktijk, het “dialecto” pero de muziek is de misma: se busca el punto ptimo tussen aislamiento en rendimiento.

Belangrijke examen-/onderwerptips

• ¿Qué son las propiedades ACID? Defínelas y pon un ejemplo.
• ¿Cuáles zoon los van de problemen van de concurrencia Is het mogelijk om te mitigarlos met niveles de aislamiento?
• Is het een probleem met een defect in InnoDB? HERHAALBARE LEZING.
• Kunt u transacties uitvoeren met MyISAM? Nee, InnoDB is noodzakelijk.
• Verschillen InnoDB vs MyISAM: transacciones, foráneas, bloqueo por fila, recuperación, etc.
• Bankoverschrijvingen: ¿qué pasa si falla una UPDATE intermedia o si la cuenta bestaat niet? Antwoord: terugdraaien asegura consistentie.

Dominaire transacties in MySQL kunnen gecombineerd worden ACID, autocommit, aislamiento, blokken, savepoints en toegangsmodi om ons te beschermen tegen een vreemd verschijnsel. Met InnoDB, tienes las herramientas para que operaciones complejas (pagos, pedidos, inventario) als u een ongeluk ervaart. Ajusta TRANSACTIE INSTELLEN al caso de uso, apóyate en procedures en controle op fouten, en oefen met de voorgestelde gevallen: het zout van de calidad in ons systeem is nota van de volgende.