記一次mysql事務併發優化
背景
事情的情況大致是這樣的。一個扣減庫存的業務上線以後,隔幾天會報一次錯,錯誤內容如下:
ERROR - exception: UncategorizedSQLException,"detail":"org.springframework.jdbc.UncategorizedSQLException: ### Error updating database.Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLQueryInterruptedException: Query execution was interrupted ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: UPDATE a SET stock = stock - ? WHERE id = ? and stock >= ? ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLQueryInterruptedException: Query execution was interrupted ; uncategorized SQLException for SQL []; SQL state [70100]; error code [1317]; Query execution was interrupted; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLQueryInterruptedException: Query execution was interrupted at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) at com.sun.proxy.$Proxy64.update(null:-1) at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
這塊業務之前一直都是正常的,遷移以後做的唯一大的改動就是加了java事務註解,所以懷疑是否和事務有關。
排查過程
首先現尋找報錯的關鍵資訊,第一個看到的是UncategorizedSQLException。檢視這個類的原始碼,說明如下:
/** * Exception thrown when we can't classify a SQLException into * one of our generic data access exceptions. * * @author Rod Johnson * @author Juergen Hoeller */
可以看到,這個類是Spring無法歸類的一個SQL異常,所以從這個異常我們是看不出什麼內容的,繼續往下。
MySQLQueryInterruptedException: Query execution was interrupted
發現這個執行是超時被kill了。一般來講,我們公司dba設定的mysql超時時間是500ms。是否是因為資料量太大,沒有走到索引,才導致update操作執行時間太長被kill了呢?看一下update語句裡
UPDATE a SET stock = stock - ? WHERE id = ? and stock >= ?
馬上排除了這個可能性。因為where條件裡有id查詢,必定會走主鍵索引,不可能沒有走到索引。那會是什麼原因呢?
上面我們提到,這個業務操作裡是開啟了事務的,還原一下大體的執行情況。
start transaction; UPDATE a SET stock = stock - 1 WHERE id = 100 and stock >= 1; INSERT INTO a (num) values (1); commit;
查了一下當時的日誌,發現1秒內有大約200條請求對一條記錄做更新庫存的操作。
線索漸漸清晰起來了,事故現場大致應該是這樣的:
T1 | T2 |
---|---|
begin | begin |
UPDATE a SET stock = stock - 1 WHERE id = 100 and stock >= 1; | |
UPDATE a SET stock = stock - 1 WHERE id = 100 and stock >= 1; | |
INSERT INTO a (num) values (1); | |
T2執行完成,1 rows affected | |
T1 Query execution was interrupted |
結論
由於開啟了事務,在高併發地對一條記錄進行更新的情況下,多個請求會進入排隊系統。由於鎖的競爭是不公平的,當多個事務同時對一條記錄進行更新時,極端情況下,就可能會出現一個更新操作進去排隊系統以後,一直拿不到鎖,超過500ms被kill了。
細節分析
以上的業務操作,update會先申請行鎖,拿到行鎖以後進行更新,更新完以後會執行插入操作。那麼在插入操作的時候是否需要申請鎖呢?
答案是肯定的,不過這裡的插入操作使用的是自增鎖。那自增鎖是什麼級別的鎖呢?
如果存在自增欄位,MySQL會維護一個自增鎖,和自增鎖相關的一個引數為(5.1.22版本之後加入)
innodb_autoinc_lock_mode:可以設定3個值,0,1,2
0:traditonal (每次都會產生表鎖)
1:consecutive (會產生一個輕量鎖,simple insert會獲得批量的鎖,保證連續插入)
2:interleaved (不會鎖表,來一個處理一個,併發最高)
Myisam引擎均為traditional,InnoDB預設為1,輕量鎖。所以在InnoDB的情況下,這裡的insert操作的效能比update操作更高。
優化
有了以上結論以後,那如何優化呢?
最簡單的方案就是減少持有鎖的時間,處理方式非常簡單,將更新操作放到最後執行,從而縮短更新鎖的持有時間,避免類似的超時問題。
start transaction; INSERT INTO a (num) values (1); UPDATE a SET stock = stock - 1 WHERE id = 100 and stock >= 1; commit;