MySQL在建立索引之前一定要想到的事情

MySQL在5.5.3版本引入了metadata lock

他的本意是解決之前版本事務隔離特性的幾個bug,但是引入的問題也不小.

先說說MySQL的事務吧.

Oracle的事務指的是需要配置設定復原段的SQL語句,也就是說select并不是oracle事務的一部分.

比如運作一個查詢,然後在另外一個會話查詢v$transaction,并不會有任何相關的資訊.直到事務中出現insert,update,delete。

而innodb的事務包括select查詢.

無論事務隔離級别是可重複讀,還是讀送出,隻要有查詢,事務就開始了

下圖證明了在5.6.15,設定了autocommit=0之後,運作一個查詢就可以開啟一個事務.

第一個會話運作查詢.

第二個會話,運作 show engine innodb status\G 檢視事務情況

可以看到id為1的線程,已經開始了一個事務.

為什麼Oracle的事務僅包括insert,update和delete的語句,而innodb的事務包括所有的語句呢?

我覺得這個和廠商支援的隔離級别有很大的關系.

衆所周知,Oracle僅僅支援讀送出和串行化兩種事務隔離級别,而讀送出是絕大多數資料庫的選擇.

讀送出意味着可以出現幻讀和不可重複讀,那麼從實作原理的角度,Oracle可以在語句(Statement級别)開始的時候,記錄SCN然後應用MVCC查詢.每個查詢隻需要記錄自己開始的SCN即可.而語句開始的SCN和事務并沒有關系.是以Oracle的事務,并不包括查詢.

而innodb支援可重複讀隔離級别,也就是說在一個事務中,無論運作多少次查詢,結果都必須是一緻的.

(innodb不僅支援可重複讀,并且使用間隙鎖在可重複讀級别避免了幻讀,當然這也帶來了很多問題..)

是以它記錄的不是每個查詢語句的LSN,而是事務第一個語句發生時的LSN,無論第一個語句是查詢,還是修改.

innodb在可重複讀的級别下,查詢用事務開始時的LSN應用MVCC,與Oracle不同的是,innodb查詢復原段中小于事務開始的LSN的資料版本,

而oracle查詢復原段中小于語句SCN的資料版本.

也就是說,同樣都是MVCC,oracle是語句級的,innodb是事務級的

這裡有一個問題,按說事務包括查詢是因為可重複讀隔離級别的需要,但是innodb讀送出隔離級别同樣也将查詢作為了事務的一部分.

可能是因為架構或者代碼實作層面的問題吧.

不管怎麼樣,Innodb就是這麼做了.

然後再說說metadata lock

在5.5.3之前,metadata lock是語句級的,這實際上破壞了事務的一緻性.

比如一個事務,在可重複讀隔離級别,運作兩次查詢,居然結果不一緻.

這正是因為metadata lock是語句級造成的問題,

在兩個查詢的間隔,另外一個會話執行了truncate table.

是以再次運作查詢,沒有任何結果.

MySQL為了解決這個問題,在5.5.3将metadata lock提升為事務級别的鎖.

任何DDL都需要先獲得metadata lock,但是這個鎖需要等事務結束的時候釋放.

同樣的實驗,在5.6.13就變成這樣的了.

第一個會話的事務沒有結束,那麼第二個會話的DDL就被阻塞

使用show processlist可以看到DDL語句在等待第一個會話事務的metadata lock

通過這種方式,就保證了可重複讀隔離級别下,事務的一緻性.

和之前提到的查詢也作為事務的一部分一樣,innodb并沒有為讀送出量身定制一些東西,

比如讀送出并不需要查詢作為事務的一部分

和讀送出并不需要事務級别的metadata lock.

可能是出于架構層面的問題,很多可重複讀的特性強加在了讀送出上,

是以一旦這些特性出現問題,即使将隔離級别降為讀送出也不能避免.

接下來問題來了,

剛才的DDL被metadata lock阻塞,這個DDL還會進一步阻塞其他的事務.甚至是查詢(查詢是innodb事務的一部分.)

這就有點抓狂了,因為這個時候,系統其實已經Hung了.

假設id為1的線程持有metadata lock 沒有送出,

id為2的線程進行DDL,然後被阻塞線上程1的metadata鎖上,

這時,資料庫依次來了8個查詢,他們都阻塞在了線程2上.

假如線程1的事務不結束,其他的線程都被阻塞.

即使線程1的事務結束了..也是後面8個事務依次獲得metadata鎖,與此同時,這個DDL可能又阻塞了80個事務..

這時候,系統的并發為1,這個DDL可能永遠不能執行.并且這種情況不在死鎖檢測的範圍内.

它的鎖逾時時間,由lock_wait_timeout參數控制,預設是31536000(一年,坑爹吧)

MySQL雖然保證了事務的一緻性,避免了bug,但是引入的問題卻可能讓我這樣的初級dba丢了飯碗..

最後梳理一下可能引發metadata lock連環阻塞的情況

1.在有其他事務運作的時候,進行DDL操作(alter table;truncate;)

2.在mysqldump運作的時候,進行DDL操作.(想想就覺得坑爹)

3.在Master-Slave複制環境,在Slave運作查詢,會導緻Master傳過來的DDL阻塞.導緻複制延遲增大.

4.建立索引(...)

作為初級dba來說,為了保住飯碗,可以有兩個動作

1.将lock_wait_timeout參數調低

2.在運作DDL之前,檢視事務是否頻繁,在運作DDL之後,開啟另外一個會話,使用show processlist檢視是否被metadata lock阻塞.

一旦阻塞,先Kill ddl的操作.