Corso MariaDB / Verificare le righe prima di eseguire UPDATE e DELETE

UPDATE modifica i valori memorizzati e DELETE elimina le righe che soddisfano la condizione. Esaminare prima la stessa condizione WHERE e verificare poi la modifica in una transazione InnoDB prima di scegliere COMMIT o ROLLBACK: questo riduce gli errori evitabili, ma non sostituisce un piano di recupero.

Definire la destinazione, il limite del recupero e il momento della conferma

Entrambi gli esperimenti seguenti terminano con ROLLBACK. Servono a imparare il controllo prima della conferma su una tabella di esercizio indipendente, senza garantire il recupero dei dati di produzione. Le modifiche importanti richiedono backup verificati, privilegi adatti e una politica per le operazioni concorrenti. Individuare quando inizia e termina la transazione e quale connessione la esegue.

Controllo Metodo Motivo
Server e database Dettagli della connessione; SELECT DATABASE() Evitare la destinazione sbagliata
Motore SHOW CREATE TABLE L'esempio richiede InnoDB
Righe SELECT con lo stesso WHERE Esaminare ID stabili e valori correnti attesi
Risultato ROW_COUNT immediato; rilettura Controllare quantità e valori modificati
Conclusione COMMIT o ROLLBACK Terminare la transazione della connessione

Un UPDATE o DELETE senza WHERE riguarda tutte le righe. Una schermata di conferma o LIMIT da solo non definiscono un ambito sicuro: la scrittura deve comunque valutare una condizione appropriata subito prima di modificare i dati.

Le regole ufficiali sono descritte nei riferimenti ROW_COUNT, DELETE, UPDATE e FOR UPDATE.

Preparare la tabella e verificare il comportamento della connessione

Collegarsi al server di studio come course_user e selezionare shopcourse. La guida al terminale e ai file SQL UTF-8 spiega come salvare il codice ed eseguirlo con SOURCE C:/mariadb-lab/lesson.sql, sostituendo il percorso con quello reale. Eseguire SQL nel client MariaDB, non al prompt del sistema operativo.

Controllare SELECT VERSION(), DATABASE() e SHOW TABLES prima di creare products_18_lab. Il nome appartiene soltanto a questo esercizio. Se esiste già, verificarne definizione e righe; eliminarlo solo dopo aver confermato che sia un oggetto di esercitazione eliminabile. Non sostituire tabelle applicative esistenti.

USE shopcourse;
CREATE TABLE products_18_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p18_price CHECK (price >= 0),
  CONSTRAINT ck_p18_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_18_lab
  (product_id, product_name, category, price, stock, note)
VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

SQL conserva le stringhe coreane originali per verificare UTF-8 in modo coerente. Gli ID da 1 a 6 rappresentano quaderno, penna, tazza, lampada da tavolo, cartellina e cavo. Le categorie significano cancelleria, articoli per la casa ed elettronica. Inserire le stringhe come mostrate, confrontare i risultati tramite gli ID e salvare il file in UTF-8.

SELECT DATABASE() AS selected_database, @@autocommit AS autocommit_mode;
SHOW CREATE TABLE products_18_lab;

Confermare ENGINE=InnoDB. Uno START TRANSACTION esplicito può terminare con COMMIT o ROLLBACK anche quando la connessione usa normalmente autocommit=1. Tutte le istruzioni devono rimanere nella stessa connessione. Uno strumento che apre una nuova connessione o conferma dopo ogni istruzione richiede una gestione diversa. Completare le istruzioni di definizione dei dati, o DDL, prima dell'esperimento sulle transazioni.

Bloccare e verificare le scorte attese prima di modificarle

Il prodotto 1 parte con scorte pari a 20. Leggere questo stato atteso, iniziare una transazione e rileggere la stessa condizione con FOR UPDATE. Se un'altra scrittura ha cambiato il valore, interrompere e verificarne la causa invece di allentare la condizione soltanto per ottenere una corrispondenza.

SELECT product_id, product_name, stock FROM products_18_lab
WHERE product_id = 1 AND stock = 20;
START TRANSACTION;
SELECT product_id, product_name, stock FROM products_18_lab
WHERE product_id = 1 AND stock = 20 FOR UPDATE;
UPDATE products_18_lab SET stock = stock - 3
WHERE product_id = 1 AND stock = 20;
SELECT ROW_COUNT() AS changed_rows;
SELECT product_id, stock FROM products_18_lab WHERE product_id = 1;
ROLLBACK;
SELECT product_id, stock FROM products_18_lab WHERE product_id = 1;

changed_rows vale 1, le scorte diventano 17 nella transazione e ROLLBACK ripristina 20. L'espressione sottrae dal valore attualmente memorizzato, mentre WHERE controlla le scorte precedenti attese. I blocchi restano fino alla fine della transazione: non lasciarla aperta durante una lunga attesa di input dell'utente.

L'ambito dei blocchi dipende da indici, condizioni e livello di isolamento. L'esempio riguarda una riga tramite chiave primaria e non elimina ogni conflitto o stallo tra transazioni. Dopo un errore o un timeout, riesaminare l'operazione e terminare esplicitamente la transazione.

Eliminare una riga controllata e verificare il ripristino

La destinazione è soltanto l'ID 2 con scorte zero. Anche l'ID 6 ha scorte zero, ma rimane fuori da questa eliminazione. Includere ID univoco e valore verificato per evitare di estendere accidentalmente la condizione a tutti i prodotti esauriti.

SELECT product_id, product_name, stock FROM products_18_lab
WHERE product_id = 2 AND stock = 0;
START TRANSACTION;
SELECT product_id, product_name, stock FROM products_18_lab
WHERE product_id = 2 AND stock = 0 FOR UPDATE;
DELETE FROM products_18_lab WHERE product_id = 2 AND stock = 0;
SELECT ROW_COUNT() AS deleted_rows;
SELECT COUNT(*) AS rows_inside_transaction FROM products_18_lab;
ROLLBACK;
SELECT COUNT(*) AS rows_after_rollback FROM products_18_lab;

deleted_rows vale 1; il conteggio scende a cinque nella transazione e torna a sei dopo ROLLBACK. Interrogare nuovamente l'ID 2 per verificare che sia tornato proprio quel prodotto. DELETE rimuove le righe selezionate senza eliminare le definizioni delle colonne.

Interpretare correttamente zero righe interessate

START TRANSACTION;
UPDATE products_18_lab SET stock = stock
WHERE product_id = 1;
SELECT ROW_COUNT() AS changed_rows;
ROLLBACK;

In una normale connessione del client da terminale, assegnare a una colonna il valore che possiede già segnala zero righe effettivamente modificate. Zero può quindi significare valore invariato, nessuna riga corrispondente o condizione sul valore precedente non soddisfatta. I client con CLIENT_FOUND_ROWS possono invece segnalare le righe corrispondenti. Verificare le impostazioni del driver prima di interpretare il conteggio.

Leggere ROW_COUNT subito dopo la scrittura. Se quantità o valori differiscono dal piano, annullare con ROLLBACK e rivedere la condizione SELECT. Un'operazione separata destinata a conservare la modifica sceglierebbe COMMIT e rileggerebbe lo stato confermato. La sequenza normale di questo esercizio evita intenzionalmente di confermare le modifiche di prova.

Riconoscere ciò che ROLLBACK non può annullare

ROLLBACK riguarda il lavoro transazionale non ancora confermato in questa connessione. Non annulla un COMMIT precedente, una modifica già confermata automaticamente o qualsiasi operazione su motori non transazionali. CREATE, DROP e TRUNCATE hanno confini DDL e di commit implicito e devono restare separati da questo esercizio di annullamento.

Se le impostazioni per gli aggiornamenti sicuri rifiutano un'istruzione, controllare condizioni sulla chiave primaria e ambito prima di disattivare una protezione. Dopo una modifica di produzione involontaria già confermata, interrompere ulteriori scritture e valutare il recupero da backup e log. Applicare l'operazione aritmetica inversa non è un metodo universale di ripristino.

Prima di ripetere, controllare database selezionato, definizione reale della tabella e righe memorizzate, salvando ciò che va conservato. Eliminare solo products_18_lab di questo articolo. DROP TABLE rimuove definizione e righe e non viene annullato da un normale ROLLBACK: tenere la pulizia separata dagli esperimenti sulle transazioni.

DROP TABLE shopcourse.products_18_lab;