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
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?