MariaDB講座 / UPDATE・DELETEの対象を確認してから変更する

MariaDB講座

UPDATEは値を変え、DELETEは条件に合う行を削除します。同じWHEREで対象を読み、InnoDBトランザクション内で件数と値を確認してから確定・取消を判断します。ただし、バックアップや復旧計画の代わりにはなりません。

対象と確定の境界を決める

この二つの変更実験はROLLBACKで終え、独立した実習表を使います。WHEREなしのUPDATE・DELETEは全行を対象とします。確認画面やLIMITだけで範囲が正しくなるわけではなく、実際の書き込みにも適切な条件が必要です。

接続先、SELECT DATABASE()、エンジン、主キーと予想値、直後のROW_COUNT()、読み戻した値と終了操作を順に確認します。トランザクションの境界と同時接続のロックも関係します。

InnoDBの独立した表を準備する

SQLファイルの実行方法でcourse_userとして学習サーバーへ接続し、SELECT VERSION(), DATABASE();とSHOW TABLES;で対象を確認します。通常例はUTF-8の.sqlファイルで実行できます。同名の実習表がある場合は定義と行を調べ、必要なデータを保存してから判断します。既存のアプリケーション表を置き換えません。

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の文字列はUTF-8実習の条件をそろえるため、原文の韓国語サンプルを維持しています。ID1～6はノート、ペン、マグカップ、スタンドライト、ファイル、ケーブルです。分類は文具・生活用品・電子用品で、結果はIDと数値を基準に照合できます。ファイルはUTF-8、接続もutf8mb4を使ってください。

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

ENGINE=InnoDBを確認します。autocommit=1でも明示したSTART TRANSACTIONはCOMMITまたはROLLBACKで終えられますが、全SQLを同じ接続で実行する必要があります。各文で新接続や自動確定をするツールは別の扱いが必要です。DDLは実験を始める前に完了します。

現在の在庫をロックして更新する

ID1の初期在庫20を条件にも含めて確認します。

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は1、実験中は17、ROLLBACK後は20です。予想値が変わって対象がない場合は、条件を緩めず原因を調べます。ロックは終了まで残るため、長い対話待ちのまま開きません。範囲は索引、条件と分離レベルにも依存し、この一行の例がすべての競合を防ぐわけではありません。エラーやタイムアウト後も状態を確認して終了します。

確認した一行だけを削除して戻す

在庫0でもID6は残し、ID2だけを削除します。

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は1、途中の件数は5、ROLLBACK後は6です。ID2の行も読み戻して確認します。DELETEは行を削除するもので、表の列定義は残ります。

影響件数0の意味を読む

同じ値を代入する例です。

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

通常のコマンドライン接続では実際に変わった行は0です。0は無一致だけでなく値が同じ、予想値条件が不一致なども意味し得ます。CLIENT_FOUND_ROWSを使うドライバーは一致件数を返す場合があるので、設定を確認します。ROW_COUNT()は書き込み直後に読み、予定と違えば取消して対象を見直します。実際に保存する別の処理ならCOMMIT後の再確認が必要です。