MariaDBでFOR UPDATE、IGNORE、READ COMMITTED、innodb_lock_wait_timeoutを活用する
Mariadb SELECT not failing on lock
MariaDBでSELECTクエリを実行時にロックが発生しても、エラーが発生せず、処理を継続する方法について解説します。
問題
MariaDBでSELECTクエリを実行時に、他のセッションがテーブルをロックしている場合、SELECTクエリはエラーが発生して実行が止まってしまいます。
解決策
以下の方法で、SELECTクエリがロックによってエラーが発生せずに処理を継続するように設定できます。
方法
FOR UPDATE オプションを使用する
SELECT クエリに FOR UPDATE オプションを追加することで、SELECTクエリが実行時にロックを獲得し、他のセッションがテーブルを更新できないようにします。
SELECT * FROM table_name FOR UPDATE;
IGNORE オプションを使用する
SELECT クエリに IGNORE オプションを追加することで、ロックによってエラーが発生しても、エラーを無視して処理を継続します。
SELECT * FROM table_name IGNORE;
READ COMMITTED 隔離レベルを使用する
MariaDBのデフォルトの隔離レベルは REPEATABLE READ です。この隔離レベルでは、他のセッションがコミットしていない変更も読み込むことができます。
READ COMMITTED 隔離レベルを設定することで、他のセッションがコミットしていない変更は読み込まなくなり、ロックによってエラーが発生する可能性が低くなります。
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
innodb_lock_wait_timeout 設定は、ロックを獲得するまで待機する時間（秒単位）を指定します。
デフォルトの値は50秒です。この値を大きくすることで、ロックを獲得するまで待機する時間を長くすることができます。
SET GLOBAL innodb_lock_wait_timeout = 100;
注意事項
FOR UPDATEオプションを使用すると、他のセッションがテーブルを更新できなくなるため、注意が必要です。IGNOREオプションを使用すると、ロックによってデータが不整合になる可能性があります。innodb_lock_wait_timeout設定を大きくすると、ロックを獲得するまで時間がかかり、パフォーマンスが低下する可能性があります。
FOR UPDATE オプションを使用する
# テーブル `users` のすべてのレコードを取得し、ロックを獲得する
SELECT * FROM users FOR UPDATE;
# テーブル `users` の `id` が 1 のレコードを更新する
UPDATE users SET name = 'John Doe' WHERE id = 1;
# ロックを解放する
COMMIT;
IGNORE オプションを使用する
# テーブル `users` のすべてのレコードを取得する
SELECT * FROM users IGNORE;
# テーブル `users` がロックされている場合でも、エラーを無視して処理を継続する
READ COMMITTED 隔離レベルを使用する
# セッションの隔離レベルを `READ COMMITTED` に設定する
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
# テーブル `users` のすべてのレコードを取得する
SELECT * FROM users;
# 他のセッションがコミットしていない変更は読み込まない
innodb_lock_wait_timeout 設定を変更する
# ロックを獲得するまで待機する時間を 100 秒に設定する
SET GLOBAL innodb_lock_wait_timeout = 100;
# テーブル `users` のすべてのレコードを取得する
SELECT * FROM users;
# ロックを獲得するまで最大 100 秒待機する
上記のサンプルコードはあくまでも例です。実際の使用例に合わせてコードを変更する必要があります。
他の方法
GET_LOCK() 関数は、名前付きのロックを取得する関数です。
# ロックを取得する
SELECT GET_LOCK('my_lock');
# テーブル `users` のすべてのレコードを取得する
SELECT * FROM users;
# ロックを解放する
SELECT RELEASE_LOCK('my_lock');
Advisory ロックは、アプリケーション間で協調排他制御を実現するためのロックです。
# Advisory ロックを取得する
SELECT GET_LOCK('my_lock', 100);
# テーブル `users` のすべてのレコードを取得する
SELECT * FROM users;
# ロックを解放する
SELECT RELEASE_LOCK('my_lock');
ミドルウェアを使用する
MySQLdb や pymysql などのミドルウェアは、自動的にロックを取得して解放する機能を提供している場合があります。
ミドルウェアを使用することで、コードを記述せずに、MariaDB SELECT not failing on lock を実現することができます。
上記の方法は、MariaDB 10.2 以降で使用できます。
- 简单的なロックには、
FOR UPDATEオプションまたはIGNOREオプションを使用するのが最も簡単です。 - より複雑なロックには、
GET_LOCK()関数または Advisory ロックを使用する必要があります。
mariadb
