1

In my application, sometimes there will be multiple transactions trying to update the same entry simultaneously. And I got deadlock below. I don't quite understand why. The queries have same where clause on id, they should scan the index in the same order. Moreover, they are both 'UPDATE' transaction, why acquires S lock?

Any help is appreciated.


LATEST DETECTED DEADLOCK

2017-05-28 06:50:41 7f2f7ea56700

*** (1) TRANSACTION:

TRANSACTION 50342747, ACTIVE 0 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)

MySQL thread id 7149, OS thread handle 0x7f2f75052700, query id 79951872 139.219.195.240 root updating

update device set alert_num=0, build_id=null, code=''AD-046'', create_date=''2017-05-20 09:35:07'', creator=null, type_id=70, device_version_id=null, enable=1, floor_id=null, last_activity_time=''2017-05-28 06:50:39'', last_red_alert_time=''1970-01-01 00:00:00'', last_yellow_alert_time=''2017-05-28 06:50:08'' where id=284

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 183 page no 3 n bits 152 index PRIMARY of table inspect.device trx id 50342747 lock_mode X locks rec but not gap waiting Record lock, heap no 76 PHYSICAL RECORD: n_fields 30; compact format; info bits 0

0: len 4; hex 8000011c; asc ;;

1: len 6; hex 0000030026e6; asc & ;;

2: len 7; hex 04000017f708dd; asc ;;

3: len 6; hex 41442d303436; asc AD-046;;

4: len 17; hex 4162737472616374446576696365303436; asc AbstractDevice046;;

5: len 4; hex 80000046; asc F;;

6: len 5; hex 999ca898c7; asc ;;

7: SQL NULL;

8: SQL NULL;

9: SQL NULL;

10: len 4; hex 8000004a; asc J;;

11: len 4; hex 80000000; asc ;; 12: SQL NULL; 13: SQL NULL; 14: SQL NULL; 15: len 4; hex 800000d8; asc ;; 16: SQL NULL; 17: len 4; hex 00000000; asc ;; 18: len 4; hex 00000000; asc ;; 19: len 12; hex e7a681e6ada2e68ea8e98081; asc ;; 20: len 4; hex 8000001e; asc ;; 21: SQL NULL; 22: SQL NULL; 23: len 4; hex 00004042; asc @B;; 24: len 4; hex 80000001; asc ;; 25: len 4; hex 80000001; asc ;; 26: SQL NULL; 27: len 5; hex 999cb86ca6; asc l ;; 28: len 5; hex 999cb86c88; asc l ;; 29: len 5; hex 9902c20000; asc ;;

*** (2) TRANSACTION:

TRANSACTION 50342683, ACTIVE 0 sec starting index read

mysql tables in use 1, locked 1

7 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1

MySQL thread id 7153, OS thread handle 0x7f2f7ea56700, query id 79951946 139.219.195.240 root updating

update device set alert_num=0, build_id=null, code=''AD-046'', create_date=''2017-05-20 09:35:07'', creator=null, type_id=70, device_version_id=null, enable=1, floor_id=null, last_activity_time=''2017-05-28 06:50:38'', last_red_alert_time=''1970-01-01 00:00:00'', last_yellow_alert_time=''2017-05-28 06:50:38'' where id=284

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 183 page no 3 n bits 152 index PRIMARY of table inspect.device trx id 50342683 lock mode S locks rec but not gap Record lock, heap no 76 PHYSICAL RECORD: n_fields 30; compact format; info bits 0

0: len 4; hex 8000011c; asc ;;

1: len 6; hex 0000030026e6; asc & ;;

2: len 7; hex 04000017f708dd; asc ;;

3: len 6; hex 41442d303436; asc AD-046;;

4: len 17; hex 4162737472616374446576696365303436; asc AbstractDevice046;;

5: len 4; hex 80000046; asc F;;

6: len 5; hex 999ca898c7; asc ;;

7: SQL NULL;

8: SQL NULL;

9: SQL NULL;

10: len 4; hex 8000004a; asc J;;

11: len 4; hex 80000000; asc ;;

12: SQL NULL; 13: SQL NULL; 14: SQL NULL; 15: len 4; hex 800000d8; asc ;; 16: SQL NULL; 17: len 4; hex 00000000; asc ;; 18: len 4; hex 00000000; asc ;; 19: len 12; hex e7a681e6ada2e68ea8e98081; asc ;; 20: len 4; hex 8000001e; asc ;; 21: SQL NULL; 22: SQL NULL; 23: len 4; hex 00004042; asc @B;; 24: len 4; hex 80000001; asc ;; 25: len 4; hex 80000001; asc ;; 26: SQL NULL; 27: len 5; hex 999cb86ca6; asc l ;; 28: len 5; hex 999cb86c88; asc l ;; 29: len 5; hex 9902c20000; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 183 page no 3 n bits 152 index PRIMARY of table inspect.device trx id 50342683 lock_mode X locks rec but not gap waiting Record lock, heap no 76 PHYSICAL RECORD: n_fields 30; compact format; info bits 0

0: len 4; hex 8000011c; asc ;;

1: len 6; hex 0000030026e6; asc & ;;

2: len 7; hex 04000017f708dd; asc ;;

3: len 6; hex 41442d303436; asc AD-046;;

4: len 17; hex 4162737472616374446576696365303436; asc AbstractDevice046;;

5: len 4; hex 80000046; asc F;;

6: len 5; hex 999ca898c7; asc ;;

7: SQL NULL;

8: SQL NULL;

9: SQL NULL;

10: len 4; hex 8000004a; asc J;;

11: len 4; hex 80000000; asc ;; 12: SQL NULL; 13: SQL NULL; 14: SQL NULL; 15: len 4; hex 800000d8; asc ;; 16: SQL NULL; 17: len 4; hex 00000000; asc ;; 18: len 4; hex 00000000; asc ;; 19: len 12; hex e7a681e6ada2e68ea8e98081; asc ;; 20: len 4; hex 8000001e; asc ;; 21: SQL NULL; 22: SQL NULL; 23: len 4; hex 00004042; asc @B;; 24: len 4; hex 80000001; asc ;; 25: len 4; hex 80000001; asc ;; 26: SQL NULL; 27: len 5; hex 999cb86ca6; asc l ;; 28: len 5; hex 999cb86c88; asc l ;; 29: len 5; hex 9902c20000; asc ;;

*** WE ROLL BACK TRANSACTION (1)

  • Is id the PRIMARY KEY? Let's see the entire transaction -- from BEGIN thru COMMIT. Or if there no begin..commit, what is the value of autocommit? – Rick James May 31 '17 at 23:40
  • id is the primary key. The original code is in JAVA. A single a call of CrudRepository interface .save(). Thus, I think the transaction is simple, like Begin; update device set alert_num=0 ...... where id=284; Commit; – Toby Xu Jun 1 '17 at 4:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.