The lock if a transaction in mysql, how it works?

0 like 0 dislike
4 views
I have a table "images" with 3 columns: id, product_id and cover. 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:

BEGIN;

# 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;

COMMIT;



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?
by | 4 views

1 Answer

0 like 0 dislike
Depends on what mode the database. If MyISAM, it locks the entire table, and if InnoDB, then only the string.
by

Related questions

0 like 0 dislike
1 answer
asked Apr 9, 2019 by vetsmen
0 like 0 dislike
3 answers
0 like 0 dislike
2 answers
0 like 0 dislike
6 answers
0 like 0 dislike
2 answers
asked Apr 12, 2019 by RastikRus
110,608 questions
257,186 answers
0 comments
28,661 users