MySQLのトランザクションとテーブルロックの基本事項

DB周りは全然分かっていないし毎回調べては忘れていくのでアレコレとメモ。

サンプルとして使うテーブル

mysql> desc sample;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| val   | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+

トランザクション

AUTOCOMMITは切っておく

これはまあ基本中の基本。さすがに覚えてた!
でも「SELECT @@autocommit」で調べられるのは知らなかった。

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

変更する場合はset構文を使う。

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
トランザクションの開始

BEGIN と BEGIN WORK は、トランザクションを始める START TRANSACTION のエイリアスとしてサポートされています。START TRANSACTION はスタンダード SQL 構文で、アドホック トランザクションを始める方法として推奨されています。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.4.1 START TRANSACTION、COMMIT、そして ROLLBACK 構文

START TRANSACTIONが推奨されています。が今日のコードではBEGINと書いてしまった。

暗黙コミット

START TRANSACTIONでトランザクションを開始したあと、COMMITをしなくてもコミットされてしまう構文があります。

次の各ステートメント(そしてそれらの同義語)は、まるでステートメントを実行する前に COMMIT を行ったかのように、暗黙にトランザクションを終了します。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.4.3 暗黙のコミットを引き起こすステートメント

この暗黙的なコミットについては知っていたんですが、頭の片隅で忘れかけててちょっとハマりました。

対象のテーブルについてレコードを一回空にして、データを入れ直そうとしました。
idは1から振り直して欲しかったので、auto_incrementを初期化するためにTRUNCATEを使用。
で、ロールバックの挙動を確認したんですが戻らなくてアウチ。

理由は先に述べた通り、TRUNCATEした時点で暗黙的にコミットされてしまうからでした。

mysql> insert into sample(val) values(10),(20),(30);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

mysql> select * from sample;
+----+------+
| id | val  |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  3 |   30 |
+----+------+
3 rows in set (0.00 sec)

mysql> truncate table sample;
Query OK, 0 rows affected (0.01 sec)

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

mysql> select * from sample;
Empty set (0.00 sec)

DELETEにしたらもちろん戻ります。

mysql> insert into sample(val) values(10),(20),(30);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from sample;
+----+------+
| id | val  |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  3 |   30 |
+----+------+

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

mysql> delete from sample;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from sample;
Empty set (0.00 sec)

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

mysql> select * from sample;
+----+------+
| id | val  |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  3 |   30 |
+----+------+
3 rows in set (0.00 sec)

うっかりこのあと「なんだよーじゃあALTER TABLEでauto_increment初期化するのかー」と思って書き直しました。

mysql> select * from sample;
+----+------+
| id | val  |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  3 |   30 |
+----+------+
3 rows in set (0.00 sec)

mysql> delete from sample;
Query OK, 3 rows affected (0.00 sec)

mysql> alter table sample auto_increment = 1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> select * from sample;
Empty set (0.00 sec)

ALTER TABLEも暗黙コミットでしたorz
ちゃんと読めよという教訓。

暗黙コミットせずにauto_incrementを初期化する方法ってないのかな?

テーブルロック

テーブルロックの開始

トランザクションテーブル (InnoDB テーブルなど) で LOCK TABLES および UNLOCK TABLES を使用するための正しい方法は、SET autocommit = 0 (START TRANSACTION ではなく) に続けて LOCK TABLES を指定することによってトランザクションを開始し、そのトランザクションを明示的にコミットするまで UNLOCK TABLES を呼び出さないことです

MySQL :: MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 8.4.5.1 テーブルロックとトランザクションの対話
AUTOCOMMITは切っておく

InnoDB は LOCK TABLES のコールの後そのテーブル ロックを即座にリリースし、その為にデッドロックが簡単に起きてしまうので、AUTOCOMMIT = 1 を持つべきでは有りません。もし AUTOCOMMIT=1 であれば、古いアプリケーションの不必要なデッドロックを防ぐ為に、InnoDB テーブル ロックを全く取得しないという事に注意してください。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.4.5 LOCK TABLES と UNLOCK TABLES 構文
ロック中はトランザクションが効いている
mysql> lock table sample write;

mysql> delete from sample;
Query OK, 3 rows affected (0.00 sec)

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

mysql> select * from sample;
+----+------+
| id | val  |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  3 |   30 |
+----+------+
UNLOCKのタイミング

ROLLBACK は MySQL の非トランザクション テーブル ロックを解除しません

http://dev.mysql.com/doc/refman/5.1/ja/lock-tables.html:table

ROLLBACKだけじゃなくてCOMMITでも解除はされません。
なので明示的なUNLOCK TABLESを忘れないようにしましょう。

mysql> lock table sample write;
Query OK, 0 rows affected (0.00 sec)

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

別のセッションから参照してもテーブルがロックされているので止まったまま。
unlock tablesによって参照可能になる。

mysql> unlock tables;

---別セッション---
mysql> select * from sample;
Empty set (15.48 sec)
暗黙コミットは動くけどロックは解除されない
mysql> lock table sample write;
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table sample;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sample;
Empty set (0.00 sec)

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

mysql> select * from sample;
Empty set (0.00 sec)

truncateしたあとに別セッションから参照しようとしても止まる。

ロック中に参照するすべてのテーブルに対してロックを取らなくてはいけない

LOCK TABLES を利用する時、ステートメントの中で利用する予定の全てのテーブルをロックしなければいけません

MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.4.5 LOCK TABLES と UNLOCK TABLES 構文

エラーになります。

mysql> create table sample2(id int(11));
Query OK, 0 rows affected (0.01 sec)

mysql> lock table sample write;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sample2;
ERROR 1100 (HY000): Table 'sample2' was not locked with LOCK TABLES

すべてのテーブルロックを取る必要があります。

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

mysql> lock tables sample write, sample2 write;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from sample2;
Empty set (0.00 sec)
1セッション内で続けてLOCK TABLESすると最初のロックはUNLOCKされる

トランザクションの開始をすると(例えば START TRANSACTION を利用して)、明示的に UNLOCK TABLES を実行します

MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.4.5 LOCK TABLES と UNLOCK TABLES 構文
--セッションA
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> lock tables sample write;
Query OK, 0 rows affected (0.00 sec)

--セッションB
--ロックされているので待機中
mysql> select * from sample;

--セッションA
--別のロックを取得(と同時に既存のロックを解放)
mysql> lock tables sample2 write;
Query OK, 0 rows affected (0.00 sec)

--セッションB
--参照出来るようになって結果が出力される
Empty set (39.46 sec)

ロックに関する確認の仕方

  • lockの件数を確認する

show open tables もしくは show open tables from db_nameの「In_use」を見る
はだかの隊長日記 » Mysql: lockの確認とプロセスの削除

mysql> show open tables;
+--------------------+----------------------------------------------+--------+-------------+
| Database           | Table                                        | In_use | Name_locked |
+--------------------+----------------------------------------------+--------+-------------+
| test               | sample2                                      |      1 |           0 |
  • 統計情報を見る

show status かmysqladmin extended-statusを使う
MySQL テーブルのロック - とみぞーノート

mysql> show status;
(略)
| Table_locks_immediate                    | 89          |
| Table_locks_waited                       | 0           |
(略)
/Users/kanno% mysqladmin extended-status | grep Table_locks;
| Table_locks_immediate                    | 89          |
| Table_locks_waited                       | 0           |

今回理解の対象にしなかったところ

ネクストキーロック
なんとなくでも知識入れておかないと、いつかハマりそうな気がする。
MySQL :: MySQL 5.1 リファレンスマニュアル :: 13.5.10.6 ネクスト キー ロック:ファントムの問題を防ぐ

1つめのセッションではc1 < 30の行だけロックをすればいいのですが、実際にはc1 = 30の行もロックされてしまっています。これはInnoDBアーキテクチャからもたらされている制限事項で、このロックのことをネクストキーロックといいます。ある範囲をロックする際に、一つ先の行までロックをかけることで「範囲」というものを表現する仕組みです。

MySQL InnoDBのネクストキーロック おさらい - SH2の日記

覚えることが多い。