背景

こんにちは。エンジニアのKennieです。
以前、SQLを学習中にトランザクションについて初めて知りました。その際に注意すべきデッドロックについて学んだので、ここにまとめてみます。

トランザクションとテーブルロック

データベースは、データの整合性を保つため、トランザクション中のデータ操作にロックをかけます。

テーブルロック:
- トランザクションが特定のテーブル（例えば、customersテーブル）に対して書き込みを行う際、そのテーブルがロックされます。
ロックの効果:
- テーブルがロックされている間、他のトランザクションはそのテーブルに対して書き込みを行うことができません。
- 例えば、トランザクションAがcustomersテーブルをロックしている場合、トランザクションBはそのテーブルにデータを追加したり、更新したりすることができません。
トランザクションの終了:
- トランザクションが正常に終了（コミット）または異常終了（ロールバック）すると、ロックは解除され、他のトランザクションがそのテーブルに対して操作を行えるようになります。

テーブルロックの取得順序を誤ると、デッドロックが発生します。

以下に、簡単なデッドロックのシナリオを示します。

デッドロックの例

操作	トランザクションA	トランザクションB
1	`UPDATE customers SET age = 42 WHERE id = 1;` (customersテーブルをロック)
2		`UPDATE users SET age = 12 WHERE id = 1;` (usersテーブルをロック)
3	`UPDATE users SET age = 12 WHERE id = 1;` (usersテーブルのロック待ち)
4		`UPDATE customers SET age = 40 WHERE id = 1;` (customersテーブルのロック待ち)

トランザクションAはcustomersテーブル、トランザクションBはusersテーブルをロックした状態で、互いに相手のロックしているテーブルのロック待ち状態となり、デッドロックが発生します。

一貫したロック順序の確立:
- トランザクションがアクセスするリソースに対して優先順位を設定し、すべてのトランザクションがその優先順位に従ってロックを取得するようにします。
- たとえば、customersテーブルがusersテーブルよりも優先順位が高い場合、すべてのトランザクションはcustomersテーブルを先にロックします。

トランザクションがアクセスするリソース（テーブルや行など）のロック順序を統一することで、デッドロックの発生を防ぐことができます。

これまでSQLでは主にINSERTやSELECTなどの基本的な操作しか行っていなかったため、実際のプロダクション環境でのロックの利用や、それに伴うリスクについて知ることができたのは役立ちました。今後プロダクションでデータベースを触ることがあれば気を付けて書きます！