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?