MySQL之鎖 for update鎖表的範圍 和Innodb建議使用與業務無關的自增ID作為主鍵

MySQL中select * for update鎖表的問題

由于InnoDB預設是Row-Level Lock，是以隻有「明确」的指定主鍵，MySQL才會執行Row lock (隻鎖住被選取的資料例) ，否則MySQL将會執行Table

Lock (将整個資料表單給鎖住)。 舉個例子: 假設有個表單products ，裡面有id跟name二個欄位，id是主鍵。

例1:(明确指定主鍵，并且有此筆資料，rowlock)

SELECT * FROM products WHERE id='3' FOR UPDATE;

SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

例2: (明确指定主鍵，若查無此筆資料，無lock)

SELECT * FROM products WHERE id='-1' FOR UPDATE;

例2: (無主鍵，table lock)

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例3: (主鍵不明确，table lock)

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

例4: (主鍵不明确，table lock)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1: FOR UPDATE僅适用于InnoDB，且必須在交易區塊(BEGIN/COMMIT)中才能生效。

注2: 要測試鎖定的狀況，可以利用MySQL的Command Mode ，開二個視窗來做測試。

在MySql 5.0中測試确實是這樣的

另外：MyAsim 隻支援表級鎖，InnerDB支援行級鎖 添加了(行級鎖/表級鎖)鎖的資料不能被其它事務再鎖定，也不被其它事務修改

（修改、删除） 。是表級鎖時，不管是否查詢到記錄，都會鎖定表。

無特殊需求下Innodb建議使用與業務無關的自增ID作為主鍵

在無特殊需求的情況下，建議使用與業務無關的自增ID作為主鍵（針對innodb）

InnoDB引擎使用聚集索引，資料記錄本身被存于主索引（一顆B+Tree）的葉子節點上。這就要求同一個葉子節點内（大小為一個記憶體頁或磁盤頁）的各條資料記錄按主鍵順序存放，是以每當有一條新的記錄插入時，MySQL會根據其主鍵将其插入适當的節點和位置，如果頁面達到裝載因子（InnoDB預設為15/16），則開辟一個新的頁（節點）。

www.2cto.com 如果表使用自增主鍵，那麼每次插入新的記錄，記錄就會順序添加到目前索引節點的後續位置，當一頁寫滿，就會自動開辟一個新的頁。如下圖所示：

這樣就會形成一個緊湊的索引結構，近似順序填滿。由于每次插入時也不需要移動已有資料，是以效率很高，也不會增加很多開銷在維護索引上。 如果使用非自增主鍵（如果身份證号或學号等），由于每次插入主鍵的值近似于随機，是以每次新紀錄都要被插到現有索引頁得中間某個位置：

www.2cto.com 此時MySQL不得不為了将新記錄插到合适位置而移動資料，甚至目标頁面可能已經被回寫到磁盤上而從緩存中清掉，此時又要從磁盤上讀回來，這增加了很多開銷，同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，後續不得不通過OPTIMIZETABLE來重建表并優化填充頁面。 在使用InnoDB存儲引擎時，如果沒有特别的需要，請永遠使用一個與業務無關的自增字段作為主鍵。 mysql在頻繁的更新、删除操作，會産生碎片。而含碎片比較大的表，查詢效率會降低。此時需對表進行優化，這樣才會使查詢變得更有效率。