MySQLとデッドロックの話

Symfony meetup #16

Transcript

1. .Z42-ͱσουϩοΫͷ
   ͸ͳ͠
   
   !IBOBIJSP@B[F
   
2. ͳ·͑ɿՖҪɹ޺ߦ
   
   UXJUUFSɿ!IBOBIJSP@B[F
   ॴଐɿIJUPNFEJB
   4ZNGPOZྺɿ
   ೥ 99
   
3. MySQL使ってる方？
4. デッドロック知ってる方
5. MySQLでデッドロックを おこしたことがある方
6. MySQLでデッドロックが起きた時に 調査したこと
7. 経緯 デッドロックは突然に。 [2017-0X-XX 10:35:04] myapp.ERROR: An exception occurred while executing 'UPDATE 〜", ”xxx_10076_20170104_112948_ab993b9c3f"]: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction {”〜"} [] ！？ いつでも、SHOW ENGINE INNODB STATUS コマンドを発行して、最近のデッドロックの原因を特 定してください。これは、デッドロックが回避されるよ うにアプリケーションを調整する際に役立ちます。 https://dev.mysql.com/doc/refman/5.6/ja/innodb-deadlocks.html
8. ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2017- XX-XX 20:18:12 7f86ab18a700 *** (1) TRANSACTION: TRANSACTION 11500361, ACTIVE 1 sec fetching rows mysql tables in use 3, locked 3 LOCK WAIT 80 lock struct(s), heap size 13864, 801 row lock(s), undo log entries 1 MySQL thread id 159300, 〜 UPDATE xxxx SET xxx *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 443 page no 172 n bits 176 index〜 Record lock, heap no 49 PHYSICAL RECORD: n_fields 11; compact format; info bits 0 〜 *** (2) TRANSACTION: TRANSACTION 11547333, ACTIVE 1 sec starting index read mysql tables in use 3, locked 3 6 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 5 MySQL thread id 1590063, OS thread handle 0x7fa60f28a700, 〜 UPDATE xxx SET xxx= '2', updated_at = '2017-XX-XX 20:18:12’ *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 443 page no 172 n bits 176 index 〜 Record lock, heap no 39 PHYSICAL RECORD: n_fields 11; compact format; info bits 0 〜 *** WE ROLL BACK TRANSACTION (2)
9. ------------------------ LATEST DETECTED DEADLOCK ------------------------ *** (1) TRANSACTION: UPDATE xxxx SET xxx *** (2) TRANSACTION: UPDATE xxx SET xxx= '2', updated_at = '2017-XX-XX 20:18:12’ *** (2) HOLDS THE LOCK(S): *** WE ROLL BACK TRANSACTION (2) SHOW ENGINE INNODB STATUS ぶつかったSQL 負けたほう。
10. 対策 -  デッドロックに対する対処 indexの追加。 -  デッドロック検知の設定 デッドロックの情報をエラーログに出すようにした。 エラーログの見直し。 mysql> set global innodb_print_all_deadlocks=ON; https://gist.github.com/koudaiii/44e465ec83f5d932ec52 http://blog.livedoor.jp/sasata299/archives/51345903.html
11. デッドロックを無理矢理起こす mysql> CREATE DATABASE deadlock_test; mysql> USE deadlock_test mysql> CREATE TABLE test(col int) ENGINE=InnoDB; mysql> INSERT INTO test VALUES(1); mysql> INSERT INTO test VALUES(2); mysql> ALTER TABLE `test` ADD UNIQUE (`col`); mysql1> begin; mysql1> select * from test where col = 1 for update; mysql2> begin; mysql2> select * from test where col = 2 for update; mysql1> select * from test where col = 2 for update; mysql2> select * from deadlock_test where col = 1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction - 準備 - コンソール２台で実行
12. 教訓 -  ログ。ちゃんとしよう。 -  Slow Queryとか、SHOW ENGINE INNODB STATUS とか、開発中から気にしよう。 -  データストレージ気にしよう。 https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.html
13. 参考 MySQLのInnoDBストレージエンジンを使用したテーブルで意図的にデッドロックを発生させる http://bit.ly/2l6FyxA 14.2.11 デッドロックの対処方法 https://dev.mysql.com/doc/refman/5.6/ja/innodb-deadlocks.html MYSQL5.6 デッドロックの情報をエラーログ出力 https://gist.github.com/koudaiii/44e465ec83f5d932ec52 14.2.6 InnoDB のレコード、ギャップ、およびネクストキーロック https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.html