mysql/InnoDBのデッドロック問題を簡単に理解
MySQLの3種類のロックのレベル:
行レベルロック:オーバーヘッドが大きく、ロックが遅い.デッドロックが発生します.ロック粒度が最小で、ロック競合が発生する確率が最も低く、同時性も最も高い.
表レベルのロック:オーバーヘッドが小さく、ロックが速い.デッドロックは発生しません.ロック粒度が大きく、ロック競合が発生する確率が最も高く、同時度が最も低い
ページロック:オーバーヘッドとロック時間はテーブルロックとローロックの間に境界があります.デッドロックが発生します.ロック粒度は表ロックと行ロックの間にあり、同時性は一般的です.
データベース・ストレージ・エンジン:
MyISAMとMEMORYストレージエンジンは、テーブルレベルロック(table-level locking)を採用しています.
BBBストレージエンジンは、ページロック(page-level locking)を採用しているが、テーブルレベルロックもサポートしている.
InnoDBストレージエンジンは、行レベルロック(row-level locking)もテーブルレベルロックもサポートしていますが、デフォルトでは行レベルロックが採用されています.
注意:InnoDB行レベルロックはインデックスに基づいており、SQLの条件フィールドにインデックスが追加されていない場合は、表レベルロックを使用します.
行レベルのロックとロックの順序:
SQL文がプライマリ・キー・インデックス(PRIMARY KEY)を操作している場合、mysqlはプライマリ・キー・インデックスをロックします.
SQL文がプライマリ・キー以外のインデックス(KEY)を操作している場合、mysqlはプライマリ・キー以外のインデックスをロックしてから、プライマリ・キー・インデックスをロックします.
プロジェクト内のデッドロックキーエラーメッセージ:
### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
InnoDBでは、以下のコマンドでロックの状況を観察できます.
mysql/InnoDBデッドロック発生例:
次のように構成されたtestテーブルを新規作成します.idはプライマリ・キー・インデックスであり、aaは通常のインデックスです.
デッドロックの例に基づいて分析します.
まずsql 1は、非プライマリ・キー・インデックスaaをロックし、プライマリ・キー・インデックスidをロックする必要があります.
sql 2はプライマリ・キー・インデックスidを直接ロックし、update文でsetはaaも使用するとともに、非プライマリ・キー・インデックスaaをロックする必要がある
そのため、2つのsqlではインデックスリソースの競合が発生し、デッドロックが発生します.
デッドロックのサンプルソリューション:
sql 1を分割し、条件に基づいてデータをクエリーし、データidに基づいてupdateを行う
行レベルロック:オーバーヘッドが大きく、ロックが遅い.デッドロックが発生します.ロック粒度が最小で、ロック競合が発生する確率が最も低く、同時性も最も高い.
表レベルのロック:オーバーヘッドが小さく、ロックが速い.デッドロックは発生しません.ロック粒度が大きく、ロック競合が発生する確率が最も高く、同時度が最も低い
ページロック:オーバーヘッドとロック時間はテーブルロックとローロックの間に境界があります.デッドロックが発生します.ロック粒度は表ロックと行ロックの間にあり、同時性は一般的です.
データベース・ストレージ・エンジン:
MyISAMとMEMORYストレージエンジンは、テーブルレベルロック(table-level locking)を採用しています.
BBBストレージエンジンは、ページロック(page-level locking)を採用しているが、テーブルレベルロックもサポートしている.
InnoDBストレージエンジンは、行レベルロック(row-level locking)もテーブルレベルロックもサポートしていますが、デフォルトでは行レベルロックが採用されています.
注意:InnoDB行レベルロックはインデックスに基づいており、SQLの条件フィールドにインデックスが追加されていない場合は、表レベルロックを使用します.
行レベルのロックとロックの順序:
SQL文がプライマリ・キー・インデックス(PRIMARY KEY)を操作している場合、mysqlはプライマリ・キー・インデックスをロックします.
SQL文がプライマリ・キー以外のインデックス(KEY)を操作している場合、mysqlはプライマリ・キー以外のインデックスをロックしてから、プライマリ・キー・インデックスをロックします.
プロジェクト内のデッドロックキーエラーメッセージ:
### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
InnoDBでは、以下のコマンドでロックの状況を観察できます.
select * from information_schema.INNODB_TRX; #
select * from information_schema.INNODB_LOCKS; #
select * from information_schema.INNODB_LOCK_WAITS; #
mysql/InnoDBデッドロック発生例:
次のように構成されたtestテーブルを新規作成します.idはプライマリ・キー・インデックスであり、aaは通常のインデックスです.
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`aa` varchar(50) DEFAULT NULL,
`bb` varchar(50) DEFAULT NULL,
`cc` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `aa` (`aa`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 --sql1:
update test set bb = '222' where aa = '11'
--sql2:
update test set aa = '111' where id = 1デッドロックの例に基づいて分析します.
まずsql 1は、非プライマリ・キー・インデックスaaをロックし、プライマリ・キー・インデックスidをロックする必要があります.
sql 2はプライマリ・キー・インデックスidを直接ロックし、update文でsetはaaも使用するとともに、非プライマリ・キー・インデックスaaをロックする必要がある
そのため、2つのsqlではインデックスリソースの競合が発生し、デッドロックが発生します.
デッドロックのサンプルソリューション:
sql 1を分割し、条件に基づいてデータをクエリーし、データidに基づいてupdateを行う
select * from test where aa = '11'
update test set bb = '222' where id in()