reproduce select statement blocking in InnoDB

[Nishant Deshpande]

Hi,

I am using MySQL (with InnoDB tables) for a fairly high utilization web application.

I use a master for my modify operations, and replicate out to my web server nodes. The nodes use the db as read-only.

I understand that modify queries will get exclusive row locks and/or index row locks as necessary, and therefore select statements that need to read those rows will block.

I want to make sure I understand exactly what types of statements will block reads on my slave nodes.

I am also sure I have seen this read blocking behaviour, but I cannot reproduce a block on a read. My updates do not use table locks, indeed they do not explicitly set locks of any kind.

Can someone give me an example of a blocked select (without explicitly locking the table of course)?

I am using the default innodb transaction isolation levels (consistent read).

Thanks