[SQL] MySQL InnoDB v5.7 Deadlock Scenario

We have MySQL 5.7 and frequently end up with errors due to deadlock condition. The most frequent queries that error out involve a Select transaction on different set of rows and an Update operation of a different row, both on same table. both operation operate on different rows (marked by filter on a boolean column - column6)

Both transactions work in seconds otherwise. I am not able to understand how do we handle this deadlock condition, given the underlying rows are different for both queries.

Any guidance will be helpful to understand this further. Thanks in advance!


*** (1) TRANSACTION:
TRANSACTION 83925587920, ACTIVE 10 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 76 lock struct(s), heap size 1136, 152 row lock(s)
MySQL thread id 187166, OS thread handle 22897097680640, query id 1221507613 <ip> <user> updating
**UPDATE table1 SET column1=UTC_TIMESTAMP(), column2='KC_AUTO', column3 = 'some random value' WHERE column3 = 'xx' AND column4 = 'xx' AND column5 = 'xx' and column6 = 0**
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10942366 page no 1465547 n bits 0 index PRIMARY of table table1 trx id 83925587920 lock_mode X locks rec but not gap waiting
Record lock, heap no 21 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
0: len=30; bufptr=0x14c1a2eeed6c; hex= 36383266646332392d383532332d313165662d396163342d306136616632; asc 682fdc29-8523-11ef-9ac4-0a6af2; (total 36 bytes);
1: len=6; bufptr=0x14c1a2eeed90; hex= 00138821e22f; asc ! /;;
2: len=7; bufptr=0x14c1a2eeed96; hex= 5a003880050ae0; asc Z 8 ;;
3: len=30; bufptr=0x14c1a2eeed9d; hex= 62626239656130312d653237312d343665622d393966302d373534633637; asc bbb9ea01-e271-46eb-99f0-754c67; (total 36 bytes);
4: len=30; bufptr=0x14c1a2eeedc1; hex= 30613638376536312d353632662d346238382d396535622d303630383164; asc 0a687e61-562f-4b88-9e5b-06081d; (total 36 bytes);
5: len=30; bufptr=0x14c1a2eeede5; hex= 65303733353932362d333465652d346131322d623031352d623632336534; asc e0735926-34ee-4a12-b015-b623e4; (total 36 bytes);
6: len=30; bufptr=0x14c1a2eeee09; hex= 39396431383632372d336131372d346336372d386333312d303961396662; asc 99d18627-3a17-4c67-8c31-09a9fb; (total 36 bytes);
7: len=30; bufptr=0x14c1a2eeee2d; hex= 34383365633731362d616633662d346162632d626231322d353030623362; asc 483ec716-af3f-4abc-bb12-500b3b; (total 36 bytes);
8: len=30; bufptr=0x14c1a2eeee51; hex= 36383239326664392d383532332d313165662d396163342d306136616632; asc 68292fd9-8523-11ef-9ac4-0a6af2; (total 36 bytes);
9: len=30; bufptr=0x14c1a2eeee75; hex= 30323363303932362d333163312d343864612d393934352d363464363137; asc 023c0926-31c1-48da-9945-64d617; (total 36 bytes);
10: len=30; bufptr=0x14c1a2eeee99; hex= 62363333393138342d343739342d346631372d386334632d616337316139; asc b6339184-4794-4f17-8c4c-ac71a9; (total 36 bytes);
11: len=30; bufptr=0x14c1a2eeeebd; hex= 30303030303030302d303030302d303030302d303030302d303030303030; asc 00000000-0000-0000-0000-000000; (total 36 bytes);
12: SQL NULL;
13: SQL NULL;
14: SQL NULL;
15: SQL NULL;
16: SQL NULL;
17: len=1; bufptr=0x14c1a2eeeee1; hex= 00; asc ;;
18: len=4; bufptr=0x14c1a2eeeee2; hex= 80000007; asc ;;
19: len=4; bufptr=0x14c1a2eeeee6; hex= 80000002; asc ;;
20: len=4; bufptr=0x14c1a2eeeeea; hex= 7ffffffe; asc ;;
21: len=30; bufptr=0x14c1a2eeeeee; hex= 64663930633339342d393737652d343565352d623030362d333166633435; asc df90c394-977e-45e5-b006-31fc45; (total 36 bytes);
22: len=5; bufptr=0x14c1a2eeef12; hex= 99b4903393; asc 3 ;;
23: len=30; bufptr=0x14c1a2eeef17; hex= 62626239656130312d653237312d343665622d393966302d373534633637; asc bbb9ea01-e271-46eb-99f0-754c67; (total 36 bytes);
24: len=5; bufptr=0x14c1a2eeef3b; hex= 99b490370c; asc 7 ;;
25: len=30; bufptr=0x14c1a2eeef40; hex= 706c616e6b746f6e2d706970656c696e652f70726f642f31363930353638; asc plankton-pipeline/prod/1690568; (total 827 bytes);
26: len=1; bufptr=0x14c1a2eef27b; hex= 00; asc ;;

*** (2) TRANSACTION:
TRANSACTION 83925585187, ACTIVE 59 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 1016142 lock struct(s), heap size 1136, 3187291 row lock(s)
MySQL thread id 187097, OS thread handle 22860576274176, query id 1221445775 10.5.20.17 practicedbuser Sending data
**CREATE TEMPORARY TABLE t_ss (SELECT distinct column0 FROM table1 SS
JOIN table2 sa on SS.column3=sa.column3 and sa.column7=SS.column7 and SS.column4 = sa.column4
where sa.column1>=(upToDateTime - INTERVAL 3 DAY) AND column10 is not null
and SS.column6 = 1 limit RecordsLimit)**
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10942366 page no 1465547 n bits 0 index PRIMARY of table table1 trx id 83925585187 lock mode S locks rec but not gap
Record lock, heap no 21 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
0: len=30; bufptr=0x14c1a2eeed6c; hex= 36383266646332392d383532332d313165662d396163342d306136616632; asc 682fdc29-8523-11ef-9ac4-0a6af2; (total 36 bytes);
1: len=6; bufptr=0x14c1a2eeed90; hex= 00138821e22f; asc ! /;;
2: len=7; bufptr=0x14c1a2eeed96; hex= 5a003880050ae0; asc Z 8 ;;
3: len=30; bufptr=0x14c1a2eeed9d; hex= 62626239656130312d653237312d343665622d393966302d373534633637; asc bbb9ea01-e271-46eb-99f0-754c67; (total 36 bytes);
4: len=30; bufptr=0x14c1a2eeedc1; hex= 30613638376536312d353632662d346238382d396535622d303630383164; asc 0a687e61-562f-4b88-9e5b-06081d; (total 36 bytes);
5: len=30; bufptr=0x14c1a2eeede5; hex= 65303733353932362d333465652d346131322d623031352d623632336534; asc e0735926-34ee-4a12-b015-b623e4; (total 36 bytes);
6: len=30; bufptr=0x14c1a2eeee09; hex= 39396431383632372d336131372d346336372d386333312d303961396662; asc 99d18627-3a17-4c67-8c31-09a9fb; (total 36 bytes);
7: len=30; bufptr=0x14c1a2eeee2d; hex= 34383365633731362d616633662d346162632d626231322d353030623362; asc 483ec716-af3f-4abc-bb12-500b3b; (total 36 bytes);
8: len=30; bufptr=0x14c1a2eeee51; hex= 36383239326664392d383532332d313165662d396163342d306136616632; asc 68292fd9-8523-11ef-9ac4-0a6af2; (total 36 bytes);
9: len=30; bufptr=0x14c1a2eeee75; hex= 30323363303932362d333163312d343864612d393934352d363464363137; asc 023c0926-31c1-48da-9945-64d617; (total 36 bytes);
10: len=30; bufptr=0x14c1a2eeee99; hex= 62363333393138342d343739342d346631372d386334632d616337316139; asc b6339184-4794-4f17-8c4c-ac71a9; (total 36 bytes);
11: len=30; bufptr=0x14c1a2eeeebd; hex= 30303030303030302d303030302d303030302d303030302d303030303030; asc 00000000-0000-0000-0000-000000; (total 36 bytes);
12: SQL NULL;
13: SQL NULL;
14: SQL NULL;
15: SQL NULL;
16: SQL NULL;
17: len=1; bufptr=0x14c1a2eeeee1; hex= 00; asc ;;
18: len=4; bufptr=0x14c1a2eeeee2; hex= 80000007; asc ;;
19: len=4; bufptr=0x14c1a2eeeee6; hex= 80000002; asc ;;
20: len=4; bufptr=0x14c1a2eeeeea; hex= 7ffffffe; asc ;;
21: len=30; bufptr=0x14c1a2eeeeee; hex= 64663930633339342d393737652d343565352d623030362d333166633435; asc df90c394-977e-45e5-b006-31fc45; (total 36 bytes);
22: len=5; bufptr=0x14c1a2eeef12; hex= 99b4903393; asc 3 ;;
23: len=30; bufptr=0x14c1a2eeef17; hex= 62626239656130312d653237312d343665622d393966302d373534633637; asc bbb9ea01-e271-46eb-99f0-754c67; (total 36 bytes);
24: len=5; bufptr=0x14c1a2eeef3b; hex= 99b490370c; asc 7 ;;
25: len=30; bufptr=0x14c1a2eeef40; hex= 706c616e6b746f6e2d706970656c696e652f70726f642f31363930353638; asc plankton-pipeline/prod/1690568; (total 827 bytes);
26: len=1; bufptr=0x14c1a2eef27b; hex= 00; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10942366 page no 2330368 n bits 0 index PRIMARY of table table1 trx id 83925585187 lock mode S locks rec but not gap waiting
Record lock, heap no 19 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
0: len=30; bufptr=0x14b4822eeceb; hex= 32323562306663332d376332612d313165662d613032632d306563373037; asc 225b0fc3-7c2a-11ef-a02c-0ec707; (total 36 bytes);
1: len=6; bufptr=0x14b4822eed0f; hex= 0013895adf92; asc Z ;;
2: len=7; bufptr=0x14b4822eed15; hex= 7c00115c192c01; asc | \ , ;;
3: len=30; bufptr=0x14b4822eed1c; hex= 33636537393362382d316435662d343433362d393730652d613331366531; asc 3ce793b8-1d5f-4436-970e-a316e1; (total 36 bytes);
4: len=30; bufptr=0x14b4822eed40; hex= 37626166353264362d646639662d343333382d623864652d303830633066; asc 7baf52d6-df9f-4338-b8de-080c0f; (total 36 bytes);
5: len=30; bufptr=0x14b4822eed64; hex= 65303733353932362d333465652d346131322d623031352d623632336534; asc e0735926-34ee-4a12-b015-b623e4; (total 36 bytes);
6: len=30; bufptr=0x14b4822eed88; hex= 36343834666537642d326331352d346532632d383734642d376638393162; asc 6484fe7d-2c15-4e2c-874d-7f891b; (total 36 bytes);
7: len=30; bufptr=0x14b4822eedac; hex= 62373430336535302d383161312d343134312d393262332d343730383763; asc b7403e50-81a1-4141-92b3-47087c; (total 36 bytes);
8: len=30; bufptr=0x14b4822eedd0; hex= 63623630393566382d373963642d313165662d613032632d306563373037; asc cb6095f8-79cd-11ef-a02c-0ec707; (total 36 bytes);
9: len=30; bufptr=0x14b4822eedf4; hex= 39353435393538662d323462312d346134642d626330662d356164393937; asc 9545958f-24b1-4a4d-bc0f-5ad997; (total 36 bytes);
10: len=30; bufptr=0x14b4822eee18; hex= 63646465393031302d396330312d343632612d393230322d303066613030; asc cdde9010-9c01-462a-9202-00fa00; (total 36 bytes);
11: len=30; bufptr=0x14b4822eee3c; hex= 63616665643530642d383637632d343264612d383134392d336533306266; asc cafed50d-867c-42da-8149-3e30bf; (total 36 bytes);
12: len=30; bufptr=0x14b4822eee60; hex= 64396435363332352d356434322d346237322d613864302d633235636232; asc d9d56325-5d42-4b72-a8d0-c25cb2; (total 36 bytes);
13: len=4; bufptr=0x14b4822eee84; hex= 800a2f3a; asc /:;;
14: SQL NULL;
15: len=4; bufptr=0x14b4822eee88; hex= 800a2f3a; asc /:;;
16: SQL NULL;
17: len=1; bufptr=0x14b4822eee8c; hex= 00; asc ;;
18: len=4; bufptr=0x14b4822eee8d; hex= 80000002; asc ;;
19: len=4; bufptr=0x14b4822eee91; hex= 80000000; asc ;;
20: len=4; bufptr=0x14b4822eee95; hex= 80000002; asc ;;
21: len=30; bufptr=0x14b4822eee99; hex= 30366136313739302d383730612d346134322d623030342d363833653963; asc 06a61790-870a-4a42-b004-683e9c; (total 36 bytes);
22: len=5; bufptr=0x14b4822eeebd; hex= 99b475126f; asc u o;;
23: len=30; bufptr=0x14b4822eeec2; hex= 33636537393362382d316435662d343433362d393730652d613331366531; asc 3ce793b8-1d5f-4436-970e-a316e1; (total 36 bytes);
24: len=5; bufptr=0x14b4822eeee6; hex= 99b491132e; asc .;;
25: len=30; bufptr=0x14b4822eeeeb; hex= 544f55475a463034303830343434382c312c307c574a5a594e4937383633; asc TOUGZF040804448,1,0|WJZYNI7863; (total 39 bytes);
26: len=1; bufptr=0x14b4822eef12; hex= 00; asc ;;

*** WE ROLL BACK TRANSACTION (1)


Tuned the queries , so that they take less time but even that didn't help.

Continue reading...