Make Innodb Only Lock The Row Currently Being Updated?

Building on the query in this answer (please note/assume that the GROUP_CONCATs are now also held in user defined variables), when and what will InnoDB lock on table1? I'd prefer t

Solution 1:

The lock is held for the entire transaction (as the operation is atomic, this means that either all of the rows are updated or no rows) and you can't change that (without changing the storage engine). However it does not block reads (unless you are in SEIALIZABLE isolation level), so SELECT queries will be executed, but they will read the old values. Only SELECT FOR UPDATE and SELECT...LOCK IN SHARE MODE will be blocked by an update.