Confusion regarding INNODB locking

Question

When i run 'Select * from table' on an INNODB table, does the table get locked implicity? Does it mean that during the time MySQL takes to return the result set, i cannot issue an update statement on the table?

From what i have understood, the whole table would be locked in shared mode until the result set is returned from the server. Only then could the update command be executed.

Haven't tried. Just asking for the sake of knowledge. – Kanishk Dudeja Apr 22 '14 at 12:11 — Kanishk Dudeja, Apr 22 '14 at 12:11
Have edited the question. – Kanishk Dudeja Apr 22 '14 at 12:18 — Kanishk Dudeja, Apr 22 '14 at 12:18

score 2 · Accepted Answer · answered Apr 22 '14 at 16:05

InnoDB uses a feature called Multi-version concurrency control.

Locking in shared mode is not required, since MVCC will retain earlier versions of the row for your SELECT statement to be able to read if required.

So the answer is 'no', running a SELECT statement will not need to lock any rows while updating. That is, unless it is a special SELECT statement like SELECT .. FOR UPDATE.

Confusion regarding INNODB locking

1 Answers1

Linked