テーブル例
下記のようなテーブルがあるとします。
このテーブルはidカラムのみインデックスが貼られています。
testテーブル
| id(indexあり) | code | date | status |
|---|---|---|---|
| 1 | 100 | 2022-01-01 | 0 |
| 2 | 200 | 2022-02-01 | 0 |
| 3 | 300 | 2022-03-01 | 0 |
テーブルロックありパターン
MySQLの仕様として、インデックスを使ったクエリでないと行ロックを取得できません。
こちらのブログで詳しく書かれています。
簡単に書くと、、、
-- 接続①
mysql> BEGIN; -- トランザクション開始
mysql> UPDATE test SET status = '1' WHERE code = '100';
-- Query OK, 1 row affected (0.00 sec)
-- Rows matched: 1 Changed: 1 Warnings: 0 <-この時点でテーブルロックが掛かっている
-- 接続②
mysql> BEGIN; -- トランザクション開始
mysql> UPDATE test SET status = '1' WHERE code = '200';
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction <- 既にテーブルがロックされているのでロック待ちエラーが出る
このパターンだと、接続①、接続②ともに検索条件に指定しているのはcodeで、このカラムにはインデックスが貼られていません。
従って、接続①の段階でテーブルロックがかかり、接続②でロック待ちタイムアウトが出現します。
これはブログの記事を読んですぐに理解できました。
私が勘違いしていたパターン
-- 接続①
mysql> BEGIN; -- トランザクション開始
mysql> UPDATE test SET status = '1' WHERE id = '1';
-- Query OK, 1 row affected (0.00 sec)
-- Rows matched: 1 Changed: 1 Warnings: 0
-- 接続②
mysql> BEGIN; -- トランザクション開始
mysql> UPDATE test SET status = '1' WHERE code = '200';
-- ????
このパターンだと、接続②の結果はどうなると思いますか・・・？
私の考えでは、、、
接続①では、検索条件に指定しているidにはインデックスが貼られている
つまり、この時点ではテーブルロックは掛かっておらず、行ロックのみ。
接続②では、codeというインデックスが貼られていないカラムを検索条件として使用しています。
しかし、検索している行自体は、接続①で検索した行とは異なるのでロック待ちタイムアウトは出ない想定でした。
しかし、実際は最初のパターンと同じようにロック待ちタイムアウトが出ます。
最後に
恥ずかしながら勝手な思い込みで、接続①、接続②のいずれかで、インデックスが貼られているカラムを検索条件に使っていれば
ロック待ちタイムアウトは出ないものだと思っていました。。
実際は、インデックスが貼られていないカラムを検索条件に使った段階で、テーブルロックからのロック待ちタイムアウトが出るようです。
今回のパターンでLock wait timeout exceeded; try restarting transactionが出た場合は下記２点を確認してみてください。
- 適切なインデックスが貼られているか
- 既に貼られているインデックスで検索条件を組み変えることができないか
まだまだSQL勉強中ですので、間違っている箇所や補足などありましたら、コメントにて教えていただけますと幸いです。