【MySQL】デッドロックケースその6

はじめに
デッドロックは、実は面白くて挑戦的な技術問題で、DBAや一部の開発学生が仕事の過程で出会うかもしれません.デッドロックについては、デッドロックを知りたい人に役立つように、一連のケーススタディを書き続けます.
二例分析
2.1環境の説明
MySQL 5.6.24トランザクション独立性レベルRR

create table tx (
  id int not null primary key auto_increment ,
  c1 int not null default 0,
  c2 int not null default 0,
  key idx_c1(c1)
) engine=innodb ;
insert into tx values(24,3,4),(25,3,4),
(26,3,4),(30,5,8);

2.2試験用例
sess1
sess2
begin;
begin
T1
select * from tx where id=30 for update;
T2
update tx set c2=8 where c1=5; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
T3
delete from tx where id=30;
2.3デッドロックログ

----------------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-03-27 15:40:40 0x7f75cafce700
*** (1) TRANSACTION:
TRANSACTION 1850, ACTIVE 20 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 379040, OS thread handle 140143994337024, 
query id 1521958 localhost root updating
update tx set c2=8 where c1=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27 page no 3 n bits 72 index PRIMARY of table 
`test`.`tx` trx id 1850 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 1849, ACTIVE 32 sec updating or deleting, 
thread declared inside InnoDB 4999
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 379016, OS thread handle 140143893473024, 
query id 1521976 localhost root updating
delete from tx where id=30
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 27 page no 3 n bits 72 index PRIMARY of 
table `test`.`tx` trx id 1849 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27 page no 5 n bits 72 index idx_c1 of 
table `test`.`tx` trx id 1849 lock_mode X locks rec but not 
gap waiting
*** WE ROLL BACK TRANSACTION (1)

2.4デッドロックログの分析
まず、同じフィールドに対してロックを申請するには、キューが必要であることを理解してください.
次に、テーブルtyにおけるインデックスidx_c 1は非一意の一般インデックスであり、トランザクションが実行される時間順に基づいて説明するのが理解しやすい.
T 1:sess 2実行select for update操作は記録id=30の主キー行ロック:PRIMARY of table testを持つ.tx lock_mode X locks rec but not gap
T 2:sess 1文update通常インデックスidx_c 1 c 2を更新し、idx_を取得するc 1 c 1=5のXロックlock_mode X locks rec but not gapは、その後、対応するプライマリ・キーid=30の行ロックを申請するが、sess 2はすでにプライマリ・キーの行ロックを持っており、sess 1は待機する.
T 3:sess 2は、プライマリキーid=30によるレコードの削除を実行し、id=30の行ロックおよびc 1=5のインデックス行ロックを申請する必要がある.ただしsess 1およびそのロックを持っているためindex idx_が現れるc1 of table test.tx trx id 1849 lock_mode X locks rec but not gap waiting
sess 2(delete)はsess 1(update)、sess 1(update)はsess 2(select for update)がループして待機し、デッドロックをもたらす.
RDBMSシステムにデッドロックが発生した根本的な原因は、異なるトランザクションのロックの順序が異なるため、ループ待ちが発生し、デッドロックが発生することに要約される.
2.5解決方法
sess 1を修正したupdateは、プライマリキーに基づいて更新される、すなわちupdate tx set c 2=x where id=30となり、ロック方式を順次ロックに変更し、プライマリキーidのロックを申請し、クロスロックによって互いに相手が持っているロックを申請することを避ける.
三小結
上記の例でデッドロックが発生したのは、異なるセッションが通常のインデックスidx_に対してc 1とプライマリ・キーが競合すると、ループ待機によるデッドロックが発生する.本番中に同じローが同時に更新される場合は、異なるインデックスによる更新を回避し、デッドロックを回避することが考えられます.
おすすめ読書
デッドロック・ログの読み取り方法
デッドロック
デッドロックケースの1つ
デッドロックケース2
デッドロックケースの3
デッドロックケースの4
デッドロックケースの5
「ITPUBブログ」からのリンク:http://blog.itpub.net/22664653/viewspace-2152274/転載する必要がある場合は、出典を明記してください.そうしないと、法律責任を追及します.
転載先:http://blog.itpub.net/22664653/viewspace-2152274/