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


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/code-flow.club/qa-theme/donut-theme/qa-donut-layer.php on line 274
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 10, 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
22,674 users