Select statement blocks the read/write operation on the InnoDB table

Question

I have a Select query which executes on a transactional table having more than 4 million records. Whenever I execute this query , I observe that all write and update operations on that particular transactional table become suspended and we start getting exceptions from java side that lock wait timeout exceeds , try restarting transaction. Currently lock wait timeout is set to 200 seconds. I am unable to understand that why a select statement can create such locks on the table and block all insert/update statements. The table storage engine is InnoDb and primary key is auto-increment key. The MySQL Version is 5.1.40. Also I m not starting any transaction before executing select statement.

Any Idea?

Answer 1

So, yes, your SELECT in one transaction read-locks the records of that table and write operations which touch the same records will have to wait until read transaction completes (if it follows two phase locking).

This document may help understanding innodb locks model