ぽかぽかコード日和

とっても暑い夏の日にプログラミングはじめました☀️

【MySQL】TCL文（トランザクション）

データベース管理システム(DBMS)には、
安全で確実なデータ操作とデータ管理を保証するため トランザクションという仕組みがあり、それを操作・制御するSQL文をTCL文という。

トランザクションとは

1つ以上のSQL文を1つのまとまりとして扱う。
処理が正常に完了したときのみデータベースに反映(コミット)し、
途中で問題が発生したときは、すべての処理を取り消して元の状態に戻す(ロールバック)仕組みのこと。

☀︎トランザクションのACID特性☀︎
原子性(Atomicity)・・・トランザクション処理が、すべて成功かすべて失敗かで終了すること。
一貫性(Consistency)・・・トランザクション実行の前後でデータベースの内容に矛盾がないこと。
独立性(Isolation)・・・複数のトランザクションを同時に実行したとき、他のトランザクションの影響を受けないこと。分離性。
耐久性(Durability)・・・トランザクションが正常終了すると、コミットした結果は障害が発生しても消失しないこと。

トランザクション指示

START TRANSACTION・・・トランザクション開始

  • またはBEGIN; (BEGINはストアドプログラムで使う。)

  • デフォルトの自動コミットが一時的に無効になる。

  • トランザクション中のSQL文は「仮の状態」で管理される。
COMMIT・・・処理を確定し、トランザクション終了
ROLLBACK・・・処理を全消去し、トランザクション終了
START TRANSACTION;

INSERT INTO orders_archive
SELECT *
FROM orders
WHERE purchase_date <= '2024-01-31';

DELETE 
FROM orders
WHERE purchase_date <= '2024-01-31';

COMMIT;

☀︎データをアーカイブに移動して、元のデータを削除している。

START TRANSACTION;

DELETE 
FROM orders
WHERE purchase_date <= '2024-01-31';

ROLLBACK;

☀︎元のデータの削除を取り消している。

  • ROLLBACK;より先にCOMMIT;と書いてしまうと確定されてしまい取り消せない。

トランザクションの排他制御(ロック)

ロックとは、データベースの更新中にデータの不整合が発生しないように
一時的にアクセス制限をかけて他のトランザクションから更新できないようにする仕組みのこと。

  • トランザクションの独立性を守る。
  • トランザクションの開始によりロックをかけることができ、その間は他のトランザクションでは「ロック開放待ち」の状態になる。
    終了の文(COMMIT/ROLLBACK)でロックが解放される。
  • 排他ロック・・・更新(INSERT/UPDATE/DELETE)するときに使う。他からは参照も更新もできない。
  • 共有ロック・・・参照(SELECT)するときに使う。他からは参照はできるが、更新はできない。
  • デットロックとは、お互いにロック開放待ちになる状態のこと。
  • ロックはできるだけ最小の範囲に留めておくことが推奨される。

参考にしたサイト