update 沒加索引會鎖全表?
當我們執行 update 語句時,實際上是會對記錄加獨占鎖(X 鎖)的,其他事務對持有獨占鎖的記錄進行修改時是會被阻塞的。另外,這個鎖並不是執行完 update 語句就會釋放的,而是會等事務結束時才會釋放。
在 InnoDB 事務中,對記錄加鎖的基本單位是 next-key 鎖,但是會因為一些條件會退化成間隙鎖,或者記錄鎖。加鎖的位置是在索引上而非行上。
舉個例子,有一張表 student
,id 為主鍵索引:
id | name | score |
---|---|---|
15 | Bob | 34 |
20 | Alex | 77 |
30 | Tom | 60 |
37 | John | 40 |
假設有兩個事務的執行順序如下:
事務 A | 事務 B |
---|---|
begin; | |
update student set socre = 100 where id = 1; | begin; |
update student set socre = 100 where id = 2; | |
commit; | |
commit; |
事務 A 的 update 語句中 where 是等值查詢,並且 id 是唯一索引,所以只會對 id = 1 這條記錄加鎖,因此,事務 B 的更新操作並不會阻塞。
但是,在 islation level 是 Repeatable Read 的情況下(MySQL 預設即為 Repeatable Read,PostgreSQL 預設是 Read Committed),update 語句的 where 條件若沒有使用索引,就會全表掃描,於是就會對所有記錄加上 next-key 鎖(記錄鎖 + 間隙鎖),相當於把整個表鎖住了(注意:Read Committed 不會鎖全表)。
Info
PostgreSQL 在 Repeatable Read 情況下也不會讓事務 B 阻塞
假設有兩個事務的執行順序如下:
事務 A | 事務 B |
---|---|
begin; | |
update student set socre = 100 where name = 'Bob'; | begin; |
update student set socre = 100 where id = 2; 阻塞中... | |
commit; | |
commit; |
事務 B 的 update 語句被阻塞是因為事務 A 的 update 語句中 where 條件沒有索引列,觸發了全表掃描,在掃描過程中會對索引加鎖,所以全表掃描的場景下,所有記錄都會被加鎖,也就是這條 update 語句產生了 4 個記錄鎖 15, 20 , 30, 37
和 5 個間隙鎖 (-∞, 15), (15, 20), (20, 30), (30, 37), (37, ∞)
,相當於鎖住了全表。
因此,當在資料量非常大的表執行 update 語句時,如果沒有使用索引,就會給全表加上 next-key 鎖,直到事務結束,而這期間除了 select ... from
語句,其他語句都會被鎖住不能執行。
那 update 語句的 where 帶上索引就能避免全表記錄加鎖了嗎?
並不是。
關鍵還得看這條語句在執行過程中,優化器最終選擇的是索引掃描,還是全表掃描,如果走了全表掃描,就會對全表的記錄加鎖了。
如何避免這種事故發生?
我們可以將 MySQL 裡的 sql_safe_updates
參數設置為 1,開啟安全更新模式。
當 sql_safe_updates 設置為 1 時, update 語句必須滿足如下條件之一才能執行成功:
- 使用 where,並且 where 條件中必須有索引列
- 使用 limit
- 同時使用 where 和 limit,此時 where 條件中可以沒有索引列
delete 語句必須滿足以下條件才能執行成功:
- 同時使用 where 和 limit,此時 where 條件中可以沒有索引列
如果 where 條件帶上了索引列,但是優化器最終掃描選擇的是全表,而不是索引的話,我們可以使用 force index([index_name])
告訴優化器使用哪個索引,以此避免鎖全表帶來的隱患。