I have a table "images"
with 3 columns: id, product_id
. Maybe we can put a lot of entries are constantly read. Ie I would not want to table block.
The table has 100 records (product_id=777), one of which cover=1 and the rest 99 cover=0. Then suddenly I wanted to install the sign cover to another picture and to reset the current, and written requests:
# First shed cover product No. 777
UPDATE images SET cover=0 WHERE product_id=777 AND cover=1 LIMIT 1;
# Set the new cover for the item (AND product_id=777 here for clarity)
UPDATE images SET cover=1 WHERE id=5000 AND product_id=777 LIMIT 1;
So, does that mean that if this transaction is only blocked all the entries of the product with product_id=777 and not the entire table?