Início / user-2043

RedBlueThing's questions

Martin Hope
RedBlueThing
Asked: 2011-06-08 17:39:18 +0800 CST
  • 11

Esta é quase certamente a causa da minha outra pergunta , mas achei que valia a pena separar as duas, pois tenho uma hipótese baseada no registro a seguir que adoraria ter falsificado ou verificado.

Minha hipótese é que o outro impasse é realmente resultado das seguintes consultas, com a consulta original oculta com base no meu entendimento de que o status do innodb mostra apenas as transações mais recentes (está correto?).

Com base no log, verifiquei nosso código e encontrei as duas consultas a seguir executadas em sequência:

db.Execute("UPDATE people SET iphone_device_id=NULL WHERE iphone_device_id=@0 AND people_id<>@1", DeviceID, m_User.people_id);
// I have hard coded this query in this snippet to simplify things
db.Execute("UPDATE people SET company_id = 444, name = 'Dad', password = '<pass>', temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@gmail.com', phone = NULL, mobile = NULL, iphone_device_id = 'iphone:<device_id_blah>', iphone_device_time = '2011-06-06 19:12:29', last_checkin = '2011-06-07 02:49:47', location_lat = <lat>, location_long = <lng>, gps_strength = 66, picture_blob_id = 1661, authority = 1, active = 1, date_created = '2011-03-20 19:18:34', last_login = '2011-06-07 11:15:01', panic_mode = 0, battery_level = 0.55, battery_state = 'unplugged' WHERE people_id = 666");

Onde db.Execute está basicamente fazendo um ExecuteNonQuery em um objeto System.Data DbCommand .

Então, essa sequência de consultas resultará em um impasse? Minha hipótese é que as duas ordens de campo diferentes nas duas consultas causam o problema? Posso agrupar as duas consultas em uma transação para resolver o impasse?

------------------------
LATEST DETECTED DEADLOCK
------------------------
110607 11:15:01
*** (1) TRANSACTION:
TRANSACTION 0 45674214, ACTIVE 0 sec, OS thread id 2584 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1216, 2 row lock(s)
    MySQL thread id 109, query id 4044915 localhost 127.0.0.1 famdev Searching rows for update
    UPDATE people SET iphone_device_id=NULL WHERE iphone_device_id='iphone:<device_id_blah>' AND people_id<>666
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 4611 n bits 152 index `PRIMARY` of table `family`.`people` trx id 0 45674214 lock_mode X locks rec but not gap waiting
    Record lock, heap no 7 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
    0: len 8; hex 8000000000000941; asc        A;; 1: len 6; hex 000002b8eedf; asc       ;; 2: len 7; hex 00000002801064; asc       d;; 3: len 8; hex 80000000000004c6; asc         ;; 4: len 3; hex 446164; asc Dad;; 5: len 30; hex <data0>; asc <data1>;...(truncated); 6: SQL NULL; 7: SQL NULL; 8: len 17; hex <data2>; asc <redacted>@gmail.com;; 9: SQL NULL; 10: SQL NULL; 11: len 30; hex <data3>; asc iphone:<data4>;...(truncated); 12: len 8; hex <data5>; asc    J]  };; 13: len 8; hex <data6>; asc    J]   ;; 14: len 8; hex <data7>; asc ~t  g C@;; 15: len 8; hex <data8>; asc G  I &S ;; 16: len 2; hex 8042; asc  B;; 17: len 8; hex <data9>; asc        };; 18: len 4; hex <data10>; asc     ;; 19: len 1; hex 81; asc  ;; 20: len 8; hex <data11>; asc    JL} Z;; 21: len 8; hex <data12>; asc    J]  M;; 22: len 1; hex 80; asc  ;; 23: len 8; hex <data13>; asc        ?;; 24: len 9; hex <data14>; asc unplugged;;

    *** (2) TRANSACTION:
    TRANSACTION 0 45674209, ACTIVE 0 sec, OS thread id 3804 starting index read, thread declared inside InnoDB 500
    mysql tables in use 1, locked 1
    5 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
    MySQL thread id 110, query id 4044916 localhost 127.0.0.1 famdev Updating
    UPDATE people SET company_id = 444, name = 'Dad', password = '<pass>', temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@gmail.com', phone = NULL, mobile = NULL, iphone_device_id = 'iphone:<device_id_blah>', iphone_device_time = '2011-06-06 19:12:29', last_checkin = '2011-06-07 02:49:47', location_lat = <lat>, location_long = <lng>, gps_strength = 66, picture_blob_id = 1661, authority = 1, active = 1, date_created = '2011-03-20 19:18:34', last_login = '2011-06-07 11:15:01', panic_mode = 0, battery_level = 0.55, battery_state = 'unplugged' WHERE people_id = 666
    *** (2) HOLDS THE LOCK(S):
        RECORD LOCKS space id 0 page no 4611 n bits 152 index `PRIMARY` of table `family`.`people` trx id 0 45674209 lock mode S locks rec but not gap
        Record lock, heap no 7 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
        0: len 8; hex 8000000000000941; asc        A;; 1: len 6; hex 000002b8eedf; asc       ;; 2: len 7; hex 00000002801064; asc       d;; 3: len 8; hex 80000000000004c6; asc         ;; 4: len 3; hex 446164; asc Dad;; 5: len 30; hex <data0>; asc <data1>;...(truncated); 6: SQL NULL; 7: SQL NULL; 8: len 17; hex <data2>; asc <redacted>@gmail.com;; 9: SQL NULL; 10: SQL NULL; 11: len 30; hex <data3>; asc iphone:<data4>;...(truncated); 12: len 8; hex <data5>; asc    J]  };; 13: len 8; hex <data6>; asc    J]   ;; 14: len 8; hex <data7>; asc ~t  g C@;; 15: len 8; hex <data8>; asc G  I &S ;; 16: len 2; hex 8042; asc  B;; 17: len 8; hex <data9>; asc        };; 18: len 4; hex <data10>; asc     ;; 19: len 1; hex 81; asc  ;; 20: len 8; hex <data11>; asc    JL} Z;; 21: len 8; hex <data12>; asc    J]  M;; 22: len 1; hex 80; asc  ;; 23: len 8; hex <data13>; asc        ?;; 24: len 9; hex <data14>; asc unplugged;;

        *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
        RECORD LOCKS space id 0 page no 4611 n bits 152 index `PRIMARY` of table `family`.`people` trx id 0 45674209 lock_mode X locks rec but not gap waiting
        Record lock, heap no 7 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
        0: len 8; hex 8000000000000941; asc        A;; 1: len 6; hex 000002b8eedf; asc       ;; 2: len 7; hex 00000002801064; asc       d;; 3: len 8; hex 80000000000004c6; asc         ;; 4: len 3; hex 446164; asc Dad;; 5: len 30; hex <data0>; asc <data1>;...(truncated); 6: SQL NULL; 7: SQL NULL; 8: len 17; hex <data2>; asc <redacted>@gmail.com;; 9: SQL NULL; 10: SQL NULL; 11: len 30; hex <data3>; asc iphone:<data4>;...(truncated); 12: len 8; hex <data5>; asc    J]  };; 13: len 8; hex <data6>; asc    J]   ;; 14: len 8; hex <data7>; asc ~t  g C@;; 15: len 8; hex <data8>; asc G  I &S ;; 16: len 2; hex 8042; asc  B;; 17: len 8; hex <data9>; asc        };; 18: len 4; hex <data10>; asc     ;; 19: len 1; hex 81; asc  ;; 20: len 8; hex <data11>; asc    JL} Z;; 21: len 8; hex <data12>; asc    J]  M;; 22: len 1; hex 80; asc  ;; 23: len 8; hex <data13>; asc        ?;; 24: len 9; hex <data14>; asc unplugged;;
mysql innodb
Martin Hope
RedBlueThing
Asked: 2011-06-06 22:54:51 +0800 CST
  • 16

Estamos acessando o MySQL a partir do conector Microsoft ADO.NET.

Ocasionalmente, vemos o seguinte impasse em nosso status innodb e não conseguimos identificar a causa do problema. Parece que a transação (2) está esperando e segurando o mesmo bloqueio?

------------------------
LATEST DETECTED DEADLOCK
------------------------
110606  5:35:09
*** (1) TRANSACTION:
TRANSACTION 0 45321452, ACTIVE 0 sec, OS thread id 3804 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368, 1 row lock(s)
    MySQL thread id 84, query id 3265713 localhost 127.0.0.1 famdev Updating
    UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>', temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com', phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>', iphone_device_time = '2011-06-06 05:35:09', last_checkin = '2011-06-06 05:24:42', location_lat = <lat>, location_long = -<lng>, gps_strength = 3296, picture_blob_id = 1190, authority = 1, active = 1, date_created = '2011-04-13 20:21:20', last_login = '2011-06-06 05:35:09', panic_mode = 0, battery_level = NULL, battery_state = NULL WHERE people_id = 3125
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 11144 n bits 152 index `PRIMARY` of table `family`.`people` trx id 0 45321452 lock_mode X locks rec but not gap waiting
    Record lock, heap no 12 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
    0: len 8; hex 8000000000000c35; asc        5;; 1: len 6; hex 000002b38ce6; asc       ;; 2: len 7; hex 00000002801f89; asc        ;; 3: len 8; hex 800000000000064a; asc        J;; 4: len 4; hex <data>; asc <name>;; 5: len 30; hex <data>; asc <data>;...(truncated); 6: SQL NULL; 7: SQL NULL; 8: len 16; hex <data>; asc <redacted>@yahoo.com;; 9: SQL NULL; 10: len 10; hex <data>; asc <phone>;; 11: len 30; hex <data>; asc android:<id>;...(truncated); 12: len 8; hex <data>; asc    J]  Z;; 13: len 8; hex <data>; asc    J]  Z;; 14: len 8; hex a39410acaa9b4340; asc       C@;; 15: len 8; hex <data>; asc     m S ;; 16: len 2; hex 8ce0; asc   ;; 17: len 8; hex 80000000000004a6; asc         ;; 18: len 4; hex 80000001; asc     ;; 19: len 1; hex 81; asc  ;; 20: len 8; hex <data>; asc    JR   ;; 21: len 8; hex <data>; asc    J]   ;; 22: len 1; hex 80; asc  ;; 23: SQL NULL; 24: SQL NULL;

    *** (2) TRANSACTION:
    TRANSACTION 0 45321448, ACTIVE 0 sec, OS thread id 3176 starting index read, thread declared inside InnoDB 500
    mysql tables in use 1, locked 1
    5 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
    MySQL thread id 85, query id 3265714 localhost 127.0.0.1 famdev Updating
    UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>', temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com', phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>-<id>-<id>', iphone_device_time = '2011-06-06 05:24:42', last_checkin = '2011-06-06 05:35:07', location_lat = <lat>, location_long = -<lng>, gps_strength = 3296, picture_blob_id = 1190, authority = 1, active = 1, date_created = '2011-04-13 20:21:20', last_login = '2011-06-06 05:35:09', panic_mode = 0, battery_level = NULL, battery_state = NULL WHERE people_id = 3125
    *** (2) HOLDS THE LOCK(S):
        RECORD LOCKS space id 0 page no 11144 n bits 152 index `PRIMARY` of table `family`.`people` trx id 0 45321448 lock mode S locks rec but not gap
        Record lock, heap no 12 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
        0: len 8; hex 8000000000000c35; asc        5;; 1: len 6; hex 000002b38ce6; asc       ;; 2: len 7; hex 00000002801f89; asc        ;; 3: len 8; hex 800000000000064a; asc        J;; 4: len 4; hex <data>; asc <name>;; 5: len 30; hex <data>; asc <data>;...(truncated); 6: SQL NULL; 7: SQL NULL; 8: len 16; hex <data>; asc <redacted>@yahoo.com;; 9: SQL NULL; 10: len 10; hex <data>; asc <phone>;; 11: len 30; hex <data>; asc android:<id>;...(truncated); 12: len 8; hex <data>; asc    J]  Z;; 13: len 8; hex <data>; asc    J]  Z;; 14: len 8; hex a39410acaa9b4340; asc       C@;; 15: len 8; hex <data>; asc     m S ;; 16: len 2; hex 8ce0; asc   ;; 17: len 8; hex 80000000000004a6; asc         ;; 18: len 4; hex 80000001; asc     ;; 19: len 1; hex 81; asc  ;; 20: len 8; hex <data>; asc    JR   ;; 21: len 8; hex <data>; asc    J]   ;; 22: len 1; hex 80; asc  ;; 23: SQL NULL; 24: SQL NULL;

        *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
        RECORD LOCKS space id 0 page no 11144 n bits 152 index `PRIMARY` of table `family`.`people` trx id 0 45321448 lock_mode X locks rec but not gap waiting
        Record lock, heap no 12 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
        0: len 8; hex 8000000000000c35; asc        5;; 1: len 6; hex 000002b38ce6; asc       ;; 2: len 7; hex 00000002801f89; asc        ;; 3: len 8; hex 800000000000064a; asc        J;; 4: len 4; hex <data>; asc <name>;; 5: len 30; hex <data>; asc <data>;...(truncated); 6: SQL NULL; 7: SQL NULL; 8: len 16; hex <data>; asc <redacted>@yahoo.com;; 9: SQL NULL; 10: len 10; hex <data>; asc <phone>;; 11: len 30; hex <data>; asc android:<id>;...(truncated); 12: len 8; hex <data>; asc    J]  Z;; 13: len 8; hex <data>; asc    J]  Z;; 14: len 8; hex a39410acaa9b4340; asc       C@;; 15: len 8; hex <data>; asc     m S ;; 16: len 2; hex 8ce0; asc   ;; 17: len 8; hex 80000000000004a6; asc         ;; 18: len 4; hex 80000001; asc     ;; 19: len 1; hex 81; asc  ;; 20: len 8; hex <data>; asc    JR   ;; 21: len 8; hex <data>; asc    J]   ;; 22: len 1; hex 80; asc  ;; 23: SQL NULL; 24: SQL NULL;

        *** WE ROLL BACK TRANSACTION (1)

Lemos este artigo sobre o próximo bloqueio de chave, mas não parece se aplicar a nós porque não estamos fazendo seleções para atualização.

Atualizar

Esta manhã, descobri uma assinatura de impasse ligeiramente diferente, que pode ser a causa raiz desse impasse. Publiquei esse impasse como uma pergunta separada para manter as coisas simples. Atualizarei aqui se puder confirmar que a outra pergunta é a causa.

mysql innodb