みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

mysql のテーブルロックについて

Mysqlにてテーブルをロックしようとして以下2つの方法を試し疑問に思ったので質問します。

1.select for update によるテーブルロック
以下のselect for update(where句にレコードを一意に特定できない条件を指定) をコマンドライン
で実行。
select * from test where test_state=0 for update;

別に起動したコマンドラインから下記アップデート文を実行したところ
ロックされることが確認できました。
update test set test_official=0 where test_id = 1;

しかし、最初に実行した select for update 文を下記のようにしたところ上記update文はロックされず
に実行されてしまいました。
select * from test where test_state is null for update;
これはなぜでしょうか。

投稿日時 - 2013-09-29 14:29:55

QNo.8284419

すぐに回答ほしいです

質問者が選んだベストアンサー

#1 です。

「一意に特定できる条件を指定せずに SELECT FOR UPDATE した場合はすべての行をロックする」という挙動は、ブログを書いた方の見解に過ぎませんよね?

そのブログの中で参照している下記のページを見ると、微妙に違う気がします。
http://dev.mysql.com/doc/refman/4.1/ja/innodb-next-key-locking.html
http://dev.mysql.com/doc/refman/5.1/ja/innodb-next-key-locking.html


そのページから読み取れることと、実際に動作を試したことから、下記の様なことが言えます。

> InnoDB が行レベルロックを行うのは、テーブルのインデックスを検索またはスキャンする際に、検出したインデックスレコードに共有ロックまたは排他ロックを設定するためです。

【A】 InnoDB の行ロックで実際にロックが掛かるのはインデックスのレコードである。

したがって、インデックスの無いカラムを SELECT FOR UPDATE の条件に含めた場合は、全ての行がロックされてしまうと思われます。主キーやユニークキーである必要は無く、インデックスが張ってあるか否かが問題の様です。
また、条件にインデックスが有るカラムだけしか出現していなくても、下記の様な値を直接比較しないような条件では全ての行をロックする様です。
UPDATE user SET ・・・ WHERE LENGTH(name) = 4;


> あるユーザがインデックスのレコード R に共有ロックまたは排他ロックを設定すると、他のユーザはインデックス順で R の直前に新しいインデックスレコードを挿入できなくなります。

【B】 指定された条件より少し広い範囲がロックされる。

ネクストキーロックといって、条件に合致するが現存するインデックスに含まれない値のインサートをロックするために、条件に合致する範囲のすぐ外側のインデックスまでロックする様です。

例えば、下記の様なデータが存在していた場合を考えます。
10, 20, 30
これを hoge > 15 AND hoge < 25 という条件によって FOR UPDATE でロックしたとします。
実際にロックされるのはインデックスですが、20 のインデックスだけをロックしても 18 や 23 のINSERTは出来てしまいますので、少し広い 10, 20, 30 のインデックスの範囲をロックする様です。
したがって、他のトランザクションから 9 や 31 のデータはロックされずにINSERTできますが、12 や 27 のデータはロックされる事になります。


なお、動作を確認したのは ver 5.5.8 です。

投稿日時 - 2013-10-07 00:14:54

お礼

ご回答ありがとうございました。

投稿日時 - 2013-10-29 21:10:22

ANo.3

このQ&Aは役に立ちましたか?

11人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.2

以下は、
http://dev.mysql.com/doc/refman/5.1-olh/ja/innodb-locking-reads.html
より引用。

SELECT ... FOR UPDATE は検索で特定されたインデックスレコードに対し、ほかのセッションが SELECT ... LOCK IN SHARE MODE を実行したり、特定のトランザクション遮断レベルで読み取りを行ったりできないようにします。

引用終わり。

select * from test where test_state=0 for update;
で、検索で特定されたレコード(test_state=0の全レコード)をロックしたが、
test_stateが1や2あるいは、Nullのレコードをロックしていない
ということでは?

参考にされている
http://d.hatena.ne.jp/drillbits/20120625/mysql_select_for_update
の9.は、全件が対象になっているので、すでに他でロックされているレコードが含まれているから
待たされているのであって、
「一意に特定できる条件を指定せずに SELECT FOR UPDATE した場合はすべての行をロックする」
(上記URLより引用)という話は、
一意に特定できる条件を指定せずに
⇒全レコードを対象とする条件として指定しているというだけのはず。

SQL Serverとかにあるロックエスカレーションでテーブルロックになる機能が
MySQLにあるのかどうか知りませんので、
テーブルロック状態になっている可能性がないわけではないですが。
(これはDB側が勝手に制御するので、一意に特定できる条件かどうかは関係ない。)
ま、個別のケースについては、
SELECT * from tableA FOR UPDATE
しておいて、他ので、
insert into tableA ・・・
して、そのレコードをUpdateできるかどうか?でわかりますけど。

投稿日時 - 2013-09-30 00:12:12

ANo.1

test テーブルのストレージエンジンは何でしょうか?
最近のMySQLのデフォルトのストレージエンジンは InnoDB ですが、InnoDB では LOCK TABLES を使わなければ、テーブルレベルのロックをしません。
http://dev.mysql.com/doc/refman/5.1/ja/table-locking.html

ストレージエンジンを確認するには「SHOW CREATE TABLE テーブル名」を実行して、 ENGINE=~~~ の部分を見れば分かります。

例) SHOW CREATE TABLE test;

http://dev.mysql.com/doc/refman/5.1/ja/show-create-table.html

投稿日時 - 2013-09-29 15:44:09

補足

ご回答ありがとうございます。InnoDBを使用しており、autocomitも無効にしてあります。MysqlはVer5.5.21
です。
↓にあるように一意に特定できない条件でselect for update
をしてテーブルのロックを試そうとしました。
http://d.hatena.ne.jp/drillbits/20120625/mysql_select_for_update

一意に特定できない条件のSQLなのに
select * from test where test_state=0 for update;
ではロックがかかり
select * from test where test_state is null for update;
ではロックがかからなかったのが不思議でしたので質問させて
いただきました。

投稿日時 - 2013-09-29 16:39:42