SQL；トランザクション、ロック(制御)

トランザクションとは？

• データの整合性を保つ方法

ロック：同時実行制御

複数ユーザーが同時に、クエリを実行してDBのデータのを書き換えると、
エラー(クラッシュ)を招く可能性があるため、ロックをかけて他のユーザーが
クエリを実行できないようにする仕組み。

• 実現方法

  • 全てのユーザーは、クエリを実行する前に、START TRANSACTION;を実行する。
    
  • 1連のクエリが実行し終わったら、COMMIT;を実行する。

try {

# START TRANSACTION;
# データを操作する処理
# COMMIT;

} catch() {

# ROLLBACK;
# エラー処理(例、echo '操作を失敗しました。';)

}

変更を無かったことにする方法 -> ROLLBACK;

mysql > START TRANSACTION;

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO
    ->   departments (dept_no, dept_name)
    -> VALUES
    ->   ('d010', 'strategy')
    -> ;
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM departments;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
| d010    | strategy  ※追加　    |
+---------+--------------------+
10 rows in set (0.00 sec)

mysql> ROLLBACK;  ※ <- ここで、今までの処理を無かったことにしている
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM departments;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
+---------+--------------------+
9 rows in set (0.00 sec)

変更を永続(保存)する -> COMMIT; を打つ！

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO
    ->   departments (dept_no, dept_name)
    -> VALUES
    ->   ('d010', 'strategy')
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM departments;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
| d010    | strategy   ※追加    |
+---------+--------------------+
10 rows in set (0.01 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM departments;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
| d010    | strategy   ※追加    |
+---------+--------------------+
10 rows in set (0.01 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM departments;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
| d010    | strategy ※追加されたまま |
+---------+--------------------+
10 rows in set (0.01 sec)


mysql> DELETE FROM departments WHERE dept_no = 'd010';
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM departments;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
+---------+--------------------+
9 rows in set (0.00 sec)