search

weblab-tw / ddia-study-group

Designing Data-Intensive Applications Study Group
36 stars 4 forks source link

第七章：行排他鎖退化成表鎖 - Kyle #82

Open kylemocode opened 2 years ago

kylemocode commented 2 years ago

在 MySQL 的 innoDB 中，SELECT ... FOR UPDATE 或是 update & delete 會在行加上排他鎖 ( exclusive lock)，不過馬克大的文章中，提到「會對有用到『 索引 』的『 行 』加『 排他鎖 』，不然會退化成『 表 』鎖」。

這段比較不理解 🥲 有查詢到更多資訊會補上來

kylemocode commented 2 years ago

表鎖的特性是開銷比較小，加鎖比較快，不會出現 Deadlock，不過併發性就比較低，看起來是因為性能考量🤔？

jxiu0129 commented 2 years ago
marklin-latte commented 2 years ago

Hi ~ 我是馬克 ~ 主要是根據 mysql 官網文件這份得來的資訊 ~ 在前面三段有提到這點 ~ 主要就是 update & delete 上的索是 record lock 也就是會在索引上鎖，而如果沒有索引就會退化成 table lock。

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

A locking read, an UPDATE, or a DELETE generally set record locks on every index record 
that is scanned in the processing of an SQL statement

...

If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, 
every row of the table becomes locked, which in turn blocks all inserts by other users to the table.
It is important to create good indexes so that your queries do not scan more rows than necessary.