locking question around INSERT during UPDATE query

Question

Say I have a large table products

id, color, cost

and I want to run a query to update the cost of all 'red' items

UPDATE products
SET cost = cost + 1
WHERE color = 'red'

Let's pretend that there are so many rows that the query takes 5 minutes to finish.

What sort of lock will be placed on the table? Am I right in thinking that as the query executes, write locks will be acquired on all the rows where color = 'red', so these records cannot be updated until the UPDATE query finishes. They can still be read however (and reading them will always provide old values until the entire UPDATE transaction finishes)

What happens when the UPDATE query is started, and it is still working out which rows to lock as it's scanning the table for records with the color red? It would still allow updates to some records with the color red ?

But it won't lock the whole table, so other records can be written or updated whilst the UPDATE query is going on? What happens if a new record is inserted with a color of red. Does the price of that product get updated? surely not?

Answer 1

If there in an index on color then only the red colored rows will be locked. The rest of this answer assumes there is this index.

Another query looking to update the rows will either deadlock or lock wait, or success (with your query deadlocking or waiting). Depending on what locks are in place an in which order. Either way concurrent updates of the same row are prevented.

A new inserted red row will be delayed or deadlock under the concept of the isolation level being repeatable read (the default) and mysql having a gap lock covering red rows. A lower isolation level of read committed will allow an insert.

ref: isolation levels