通過show processlist 查看到在alter table語句執行卡住過程中,累計了大量狀態為 Waiting for table metadata lock 的記錄
然後查看當前的事務狀態 執行 select * from information_schema.innodb_trx\G
發現了其中壹條已經運行了很久的事務,我懷疑跟這個運行很久的而且沒有提交的事務有關。
在本地mysql開多個終端測試
session 1: 開啟事務,執行select 語句,但不提交事務
session 2:執行增加字段sql
執行被阻塞了
可以看到alter table語句的狀態為Waiting for table metadata lock
session 3 : 再次查詢t1表
也被阻塞了
select * from t1 再次查詢t1表也是 Waiting for table metadata lock狀態,說明由於 metadata lock的存在,會導致後面正常的查詢都會因為等待鎖而阻塞
再查看當前事務運行狀態:
可以看到,session1的事務由於還沒提交,所以這裏能看到它的狀態還是running
這時我們commit session1的事務,看看效果
session 1:
session 2:
session 3:
可以看到session1的事務提交後,session2 和session3 都正常執行了, 他們完成的時間分別是30秒和7秒
通過上面的還原測試,可以知道是由於事務沒有提交而給表加了鎖,導致後面alter語句因為等待鎖而阻塞,從而影響後面的正常請求。
那說明我們的項目是默認開啟了事務嗎?
繼續排查,項目是使用flask-sqlchemy的插件來管理mysql接入,然後查了下文檔
在實例化sqlchemy的時候,會創建壹個用於跟Mysql交互的session對象,看看源碼
從 SignallingSession類的定義看來,autocommit=False,說明默認都給所有的sql執行開啟事務,也就是說,哪怕是純select語句,不需要加鎖的select,我們的項目默認也需要開啟事務,這對於Mysql MVCC的版本控制來說,是沒必要的。
解決辦法:就是在實例化SQLAlchemy的時候,給壹個參數,修改的session的autocommit=True:
來自官網的介紹:
意思就是為了保證事務的串行執行,而啟用的壹個鎖,這個鎖只會在事務結束的時候釋放,因此在事務提交或回滾錢,任何對這個表做的DDL操作,都是會阻塞的
這個 Metadata lock 是MySQL在5.5.3版本後引入了,為的是防止5.5.3以前的壹個bug的出現:
當壹個會話在主庫執行DML操作還沒提交時,另壹個會話對同壹個對象執行了DDL操作如drop table,而由於MySQL的binlog是基於事務提交的先後順序進行記錄的,因此在從庫上應用時,就出現Q了先drop table,然後再向table中insert的情況,導致從庫應用出錯。