WEBデザイン制作講義の「MySQL」のトランザクション処理とSQLコマンド操作

「MySQL」のトランザクション処理※画像クリックで拡大表示（リンク）

• トランザクション処理とは
  複数の作業を連結した処理単位を「トランザクション」といい、トランザクションとして管理された処理は「すべて成功」か「すべて失敗」のどちらかの保証が必要です。
  
  
  • トランザクション処理とコミット
    あるレコード更新する場合に、まず検索（SELECT文）して、その後変更した内容を更新する（UPDATE文）連続した処理が必要になります。この連続した処理がトランザクション処理であり、処理途中でエラーが発生した場合には、「ロールバック」というトランザクション開始位置（セーブポイント）に戻って処理を継続することが求められます。
    トランザクション処理がOKのときには、コミット（COMMIT文）して、処理を進めます。通常の処理では、更新（UPDATE文）ごとに自動的にコミットするモード（AUTO COMMIT）になっておりますので、トランザクション処理では、「AUTO COMMIT」をオフにして処理をすることが一般的です。
  • ロック制御の必要性
    複数の処理が同時に実行されているデータベースで、ある処理「A」が入庫処理の追加更新する場合は、最初に該当する商品を検索（SELECT文）し、格納されている在庫数「100個」に入庫のデータの入庫数「10個」を加算して在庫数として更新（UPDATE文）する処理です。
    別の処理「B」で出庫するため、たまたま同じ商品を検索（在庫数「100個」）して、「A」の入庫処理の更新前に出庫処理「20個」をしたときの在庫数「80個」として更新すると、その直後に「A」の在庫数「110個」で更新した場合に、処理「B」の出庫数が反映されず在庫数の矛盾が発生してしまいます。
    この矛盾を防止するため、先の処理（この例では「A」）の更新が終了するまで、他の処理（この例では「B」）が待たされる「ロック」制御（排他制御）が必要になります。ロック制御が「レコード単位」（一般的に自動でロック）か「テーブル単位」（一般的に手動でロック）かにより、処理スピードが大きく変わってきます。
  • 分離レベルのモード
    分離レベル「ISOLATION LEVEL」は、「SQL92」で規定されている次の4つがあり、あるトランザクション処理と別のトランザクション処理の分け隔たり（分離）の設定が可能です。
    ・「READ UNCOMMITTED」：他の処理によって行われている、書きかけのデータまで読み取る（コミットされる前の変更をみる）ことが可能
    ・「READ COMMITTED」：コミットされた時点での変更をみることが可能
    ・「REPEATABLE READ」：コミットされるまでトランザクション内で読み取り（同じSELECT文）を何回実行しても、同じ値となることを保証（MySQLのデフォルトモード）
    ・「SERIALIZABLE」：コミットされるまでテーブルロックされ、トランザクションを１つ１つ順に実行（最も安全にデータを操作できるが、相対的にスピードが遅くなる）
    

    【 分離レベルとデータの読み取り方 】

    分離レベル	ダーティリード	ノンリピータブルリード	ファントムリード
    READ UNCOMMITTED	〇	〇	〇
    READ COMMITTED	Ｘ	〇	〇
    REPEATABLE READ	Ｘ	Ｘ	〇*
    SERIALIZABLE	Ｘ	Ｘ	Ｘ

    （注）*：MySQLの「InnoDB」エンジンを利用した場合はファントム・リードが起きません。

• MySQLのデータベースエンジン比較と選択
  

  エンジン	MyISAM	InnoDB	NDB
  最大格納容量	256TB	64TB	384EB
  トランザクション	Ｘ	〇	〇
  ロック精度	テーブル	行	行
  MVCC	Ｘ	〇	〇
  クラスタDB	Ｘ	Ｘ	〇
  インデックス特徴	前文検索	クラスタ化	ハッシュ
  特徴	インデックスキーをコントロール	ACIDトランザクション処理	クラスタ化データベースサポート

  トランザクション処理には、デフォルトの「InnoDB」データベース・エンジンが最適です。
• データベース・エンジンを確認するには、
  「show table status」のコマンド入力で表示されます。
  

トランザクション処理データベース作成とSQLの修飾句コマンド操作

• トランザクション処理の「ec_trans」データベース作成
  トランザクション処理の理解する上で、事例として下記に「商品トランザクション処理のER図」を示します。
  
  
  • MySQLコマンドプロンプトを起動し、「ec_trans」データベースを作成します。
  • 「商品トランザクション処理のER図」からDDLのファイルを作成して、「source」コマンドから作成したファイルを指定して、テーブルを作成します。
    商品トランザクション処理の「DDL」：「products_transaction_DDL.sql」のダウンロード
  • サンプルの商品トランザクションデータ内容は、
    商品トランザクション処理のデータ内容（PDF）
    を参照して、「INSERT」文を作成して、データを挿入します。
    商品トランザクションデータの「INSERT」文：「insert_products_transaction.sql」のダウンロード
  • MySQL Toolsの「MySQL Administrator」を利用して、「ec_trans」のバックアップファイルを作成します。下記には、そのバックアップファイルがありますので、「リストア」でデータベースを作成することもできます。
    商品トランザクション処理のBackUp(sql)：「ProductsTransaction_BackUp.sql」のダウンロード
  • SQLコマンドの操作の実践（ビデオ表示）
    「ec_trans」データベースの作成方法（3分）
    「ec_trans」データベースのバックアップ方法（1分55秒）
• 「SQL」コマンド「SELECT」文の修飾句の操作
  「注文明細トランザクション」のテーブルを利用して、「SELECT」文の修飾句を理解し、SQLコマンドの操作を実践します。
  

  【 注文明細トランザクション：「SELECT」文の修飾句 】

  修飾句	説明とコマンド例
  DISTINCT	重複する行を取り除いて表示する sql> select distinct order_number, order_date, customer_id from order_detail;
  WHERE	条件を指定する sql> select order_number,order_date from order_detail where order_date='2014-05-06';
  GROUP BY	データをグループ化する sql> select order_number, count(*), customer_id from order_detail group by order_number;
  HAVING	グループ化されたものに条件を設定する sql> select order_number, count(*) as item_num, customer_id from order_detail group by order_number having item_num>1;
  ORDER BY	結果の並び順を指定する sql> select order_number, sum(quantity * order_price) as amount,order_date from order_detail group by order_number order by amount desc;
  LIMIT	指定した部分だけを結果として取得する sql> select order_number, sum(quantity * order_price) as amount,order_date from order_detail group by order_number order by amount desc limit 5;

  注文明細の「SELECT」修飾句のコマンド操作例：「orderDetail_select_operation.sql」のダウンロード
  理解を深めるため、操作事例を参考にして、コマンド操作を実践してください。
• 「SELECT」文の修飾句操作の実践（ビデオ表示）
  「order_detail」テーブルの修飾句の操作：１
  （9分40秒）
  「order_detail」テーブルの修飾句の操作：２
  （7分36秒）