CREATE TABLE test_table
(
id
bigint(20) NOT NULL AUTO_INCREMENT,
test_date
datetime NOT NULL COMMENT '追加日時',
insert_date
datetime NOT NULL COMMENT '登録日時',
update_date
timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日時',
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=utf8 COMMENT='テスト';
上記テーブルに下記アップデートを実行した場合、UPDATE文は行ロックになりますか?
UPDATE test_table SET test_date = NOW() WHERE id IN ( SELECT id FROM (SELECT id FROM test_table WHERE test_date > NOW() ) as temp )
Update文をSelectに変えたQueryのexplainを追記
mysql> explain select id from test_table WHERE id IN ( SELECT id FROM (SELECT id FROM test_table WHERE test_date > NOW() ) as temp );
+----+-------------+------------+-------+---------------+-------------+---------+---------------------------------+------+-------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+-------------+---------+---------------------------------+------+-------------------------------------+
| 1 | PRIMARY | test_table | index | PRIMARY | PRIMARY | 8 | NULL | 1 | Using index |
| 1 | PRIMARY | <derived3> | ref | <auto_key0> | <auto_key0> | 8 | development_web01.test_table.id | 2 | Using index; FirstMatch(test_table) |
| 3 | DERIVED | test_table | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+------------+-------+---------------+-------------+---------+---------------------------------+------+-------------------------------------+
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。