トランザクション処理　BEGIN,COMMIT,ROLLBACK

SQL

トランザクションの最後でROLLBACKを実行してBEGIN以降のSQL文は実行しなかったことになる。

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

mysql> INSERT INTO t_hanbai VALUES
    -> (9,'2005-08-10','1001','100001',5);
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE t_syohin SET zaikosu = zaikosu - 5
    -> WHERE shcd = '100001';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> select * from t_hanbai;
+----+------------+------+--------+-------+
| id | ndate      | tkcd | shcd   | suryo |
+----+------------+------+--------+-------+
|  1 | 2005-07-11 | 1001 | 100001 |    10 |
|  2 | 2005-07-12 | 1001 | 100101 |     3 |
|  3 | 2005-07-12 | 1002 | 100201 |     5 |
|  4 | 2005-07-21 | 1003 | 100A01 |     7 |
|  5 | 2005-07-22 | 1005 | 100B01 |     2 |
+----+------------+------+--------+-------+
5 rows in set (0.00 sec)

最後でCOMMITを実行して更新が反映される

mysql> INSERT INTO t_hanbai VALUES
    -> (9,'2005-08-10','1001','100001',5);
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE t_syohin SET zaikosu = zaikosu - 5
    -> WHERE shcd = '100001';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> select * from t_hanbai;
+----+------------+------+--------+-------+
| id | ndate      | tkcd | shcd   | suryo |
+----+------------+------+--------+-------+
|  1 | 2005-07-11 | 1001 | 100001 |    10 |
|  2 | 2005-07-12 | 1001 | 100101 |     3 |
|  3 | 2005-07-12 | 1002 | 100201 |     5 |
|  4 | 2005-07-21 | 1003 | 100A01 |     7 |
|  5 | 2005-07-22 | 1005 | 100B01 |     2 |
+----+------------+------+--------+-------+
5 rows in set (0.00 sec)