So, from my reading of the MySQL 5.5 documentation and some experiments I did with colleagues, I understand that the following is the case:
- With InnoDB tables, a simple
SELECTstatement (one that does not use
LOCK IN SHARE MODE) will not grab any row locks on the tables that the
SELECTstatement reads. This is true at all transaction isolation levels.
- However, if your transaction isolation level is
REPEATABLE READor higher, an
INSERT ... SELECTor
CREATE TABLE AS SELECTstatement will place row locks on tables that it reads from.
If I understand this correctly (and correct me if I don't), then I'm puzzled by this difference. Why does reading a table require lock rows in the one case but not the other, when the transaction isolation level is the same? I'd like to understand the reason for this.