mysqlトランザクションの同時最適化を記録する
4214 ワード
背景
事の状況は大体こうだ.在庫を差し引いた業務がオンラインになった後、数日おきに間違いを報告します.間違いの内容は以下の通りです.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タイムアウト時間は500 msです.データ量が大きすぎてインデックスに行かなかったため、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件のリクエストが1件のレコードに在庫更新の操作をしていた.手がかりはだんだんはっきりしてきた.事故現場は大体そうだったはずだ.
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);
T 2実行完了、1 rows affected
T1 Query execution was interrupted
結論
トランザクションがオープンしているため、1つのレコードを高同時に更新する場合、複数のリクエストがキュー・システムに入ります.ロックの競合は不公平であるため、複数のトランザクションが同時に1つのレコードを更新する場合、極端な場合、更新操作がキューシステムに入ってから、ロックが取れず、500 ms以上がkillされる可能性があります.
詳細分析
以上の業務操作ではupdateが先にロックを申請し、ロックを取得してから更新し、更新が完了すると挿入操作を実行します.では、挿入操作の際にロックを申請する必要がありますか?答えは肯定的ですが、ここの挿入操作はオートロックを使用しています.では、自己ロックはどんなレベルのロックですか?自己増加フィールドが存在する場合、MySQLは自己増加ロックを維持します.自己増加ロックに関連するパラメータは(5.1.22以降に追加)innodb_です.autoinc_lock_mode:3つの値を設定できます.0,1,2
0:traditonal(毎回テーブルロックが発生する)1:consecutive(軽量ロックが発生し、simple insertは一括ロックが得られ、連続挿入が保証される)2:interleaved(テーブルをロックせず、1つ処理し、同時に最高)
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;
参考資料:業務最適化事例mysql同時insertデッドロック問題--gap、挿入意向ロック衝突InnoDB同時挿入、意外にも意向ロックを使用しますか?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)
まずエラーを報告する重要な情報を探して、最初に見たのは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タイムアウト時間は500 msです.データ量が大きすぎてインデックスに行かなかったため、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件のリクエストが1件のレコードに在庫更新の操作をしていた.手がかりはだんだんはっきりしてきた.事故現場は大体そうだったはずだ.
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);
T 2実行完了、1 rows affected
T1 Query execution was interrupted
結論
トランザクションがオープンしているため、1つのレコードを高同時に更新する場合、複数のリクエストがキュー・システムに入ります.ロックの競合は不公平であるため、複数のトランザクションが同時に1つのレコードを更新する場合、極端な場合、更新操作がキューシステムに入ってから、ロックが取れず、500 ms以上がkillされる可能性があります.
詳細分析
以上の業務操作ではupdateが先にロックを申請し、ロックを取得してから更新し、更新が完了すると挿入操作を実行します.では、挿入操作の際にロックを申請する必要がありますか?答えは肯定的ですが、ここの挿入操作はオートロックを使用しています.では、自己ロックはどんなレベルのロックですか?自己増加フィールドが存在する場合、MySQLは自己増加ロックを維持します.自己増加ロックに関連するパラメータは(5.1.22以降に追加)innodb_です.autoinc_lock_mode:3つの値を設定できます.0,1,2
0:traditonal(毎回テーブルロックが発生する)1:consecutive(軽量ロックが発生し、simple insertは一括ロックが得られ、連続挿入が保証される)2:interleaved(テーブルをロックせず、1つ処理し、同時に最高)
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;
参考資料:業務最適化事例mysql同時insertデッドロック問題--gap、挿入意向ロック衝突InnoDB同時挿入、意外にも意向ロックを使用しますか?MySQLオートロック
以上の業務操作ではupdateが先にロックを申請し、ロックを取得してから更新し、更新が完了すると挿入操作を実行します.では、挿入操作の際にロックを申請する必要がありますか?答えは肯定的ですが、ここの挿入操作はオートロックを使用しています.では、自己ロックはどんなレベルのロックですか?自己増加フィールドが存在する場合、MySQLは自己増加ロックを維持します.自己増加ロックに関連するパラメータは(5.1.22以降に追加)innodb_です.autoinc_lock_mode:3つの値を設定できます.0,1,2
0:traditonal(毎回テーブルロックが発生する)1:consecutive(軽量ロックが発生し、simple insertは一括ロックが得られ、連続挿入が保証される)2:interleaved(テーブルをロックせず、1つ処理し、同時に最高)
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;
参考資料:業務最適化事例mysql同時insertデッドロック問題--gap、挿入意向ロック衝突InnoDB同時挿入、意外にも意向ロックを使用しますか?MySQLオートロック
start transaction;
INSERT INTO a (num) values (1);
UPDATE a SET stock = stock - 1 WHERE id = 100 and stock >= 1;
commit;