I understand that deadlock occurs when two or more entities are blocking some sources, and none of them is able to finish, because their are blocking sources in a cyclic way.
Can deadlock happens for select statement.? Does select statement block the resource.? I believe inserting or updating will block the row but not very sure for select statement. Please advice.
With the InnoDB storage engine (the default), reads are non-blocking - so two selects cannot block each other. InnoDB is a versioning engine using MVCC (multi-version concurrency control) meaning that a transaction (A) will take a copy of the records of interest at a moment in time - if it modifies and then commits the data, any transaction starting after that commit will see the new copy, but transactions starting before that any transaction starting (B) after A starts but before A commits will see the copy that A saw. Deadlocks can only occur if one of the transactions modifies the data (locking occurs in InnoDB on a row-level basis).
For further information, see here and here. For MyISAM tables (about which I know very little!), check out the comparison here or just Google "MyISAM vs InnoDB" (129,000 hits.
