MariaDB 10.5でREAD UNCOMMITTEDトランザクションがインデックス付きテーブルを更新できない？原因と解決策
MariaDB 10.5において、インデックス付きテーブルでREAD UNCOMMITTEDトランザクションを実行する場合、更新ロックを取得できない問題が発生することがあります。この問題は、トランザクション分離レベルとインデックスの使用が複雑に絡み合った結果発生します。
問題点
READ UNCOMMITTEDトランザクションは、コミットされていない変更も含めて最新のデータを読み込むことができます。しかし、インデックスを使用すると、InnoDBストレージエンジンはロックを使用してデータの一貫性を保ちます。このロックメカニズムが、READ UNCOMMITTEDトランザクションによる更新ロックの取得を妨げてしまうのです。
具体例
以下の例を想定します。
-- テーブル定義
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL
);
-- インデックス作成
CREATE INDEX idx_name ON users (name);
そして、以下の操作を2つのトランザクションで実行します。
トランザクション A
START TRANSACTION;SELECT * FROM users WHERE name = 'Taro';(この時点で、まだコミットされていない変更は反映されない)UPDATE users SET name = 'Hana' WHERE id = 1;(ここで更新ロックを取得しようとする)COMMIT;
START TRANSACTION READ UNCOMMITTED;
この場合、トランザクション B は UPDATE ステートメントを実行できずにエラーが発生する可能性があります。これは、トランザクション A がインデックス idx_name にロックをかけているため、READ UNCOMMITTEDトランザクションであるトランザクション B は更新ロックを取得できないからです。
解決策
この問題を解決するには、以下の方法があります。
トランザクション分離レベルを変更する
START TRANSACTION READ COMMITTED;ただし、インデックスを使用しない場合は、データの検索パフォーマンスが低下する可能性があることに注意が必要です。
補足
- この問題は、MariaDB 10.2以前のバージョnでは発生しませんでした。
- MySQL 8.0でも同様の問題が発生する可能性があります。
-- テーブル定義
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL
);
-- インデックス作成
CREATE INDEX idx_name ON users (name);
-- トランザクション A
START TRANSACTION;
SELECT * FROM users WHERE name = 'Taro';
UPDATE users SET name = 'Hana' WHERE id = 1;
COMMIT;
-- トランザクション B
START TRANSACTION READ UNCOMMITTED;
SELECT * FROM users WHERE name = 'Taro';
COMMIT;
このコードを実行すると、トランザクション B は UPDATE ステートメントを実行できずにエラーが発生する可能性があります。
エラーメッセージ
Error: Deadlock found when trying to lock table 'users'
説明
トランザクション A は、インデックス idx_name にロックをかけて UPDATE ステートメントを実行します。一方、トランザクション B は READ UNCOMMITTED トランザクションであるため、コミットされていない変更も含めて最新のデータを読み込むことができます。しかし、インデックス idx_name がロックされているため、トランザクション B は更新ロックを取得できず、デッドロックが発生します。
- トランザクション分離レベルを
READ COMMITTEDに変更する - インデックスを使用しない
この問題は、MariaDB 10.5のみで発生する問題です。MySQL 8.0以前のバージョnでは、この問題は発生しません。
MariaDB 10.5におけるインデックスとREAD UNCOMMITTEDトランザクションにおける更新ロック問題の解決方法
ロックヒントを使用すると、トランザクションが取得するロックの種類を制御できます。READ UNCOMMITTEDトランザクションで更新ロックを取得するには、以下のロックヒントを使用できます。
START TRANSACTION READ UNCOMMITTED
WITH (READ_HIGH_NO_WAIT_LOCK);
このロックヒントを使用すると、トランザクションは更新ロックを取得できない場合、エラーを発生させるのではなく、待機します。
非同期更新を使用すると、更新を別のスレッドで実行できます。これにより、READ UNCOMMITTEDトランザクションが更新ロックを取得する必要がなくなり、デッドロックを回避できます。
シャーディングを使用すると、データを複数のデータベースに分散できます。これにより、READ UNCOMMITTEDトランザクションが異なるデータベースで実行されるため、デッドロックが発生する可能性が低くなります。
最適な解決策は、個々のアプリケーションの要件によって異なります。
mariadb mariadb-10.5
