A deadlock occurs when multiple mysql transactions update the same record.

** The following is the deadlock information viewed using showinnodbstatus. all records are updated using the primary key index userId. No other indexes have any impact and should not cause deadlocks. Please help us to analyze the cause. ** Table indexes are as follows (... PRIMARYKEY (userId), UNIQUEKEYuserId_UNIQUE (userId), UNIQUEKEYuserName_UNIQUEUSINGBTREE (userName) ENGINEInnoDBAUTO_INCREMENT1DEFAULT... mysql

The following is the deadlock information viewed using show innodb status. all records are updated using the primary key index userId. No other indexes have any impact and should not cause deadlocks. Please help us to analyze the cause.

The table index is as follows:
(....
Primary key (userId ),
Unique key userId_UNIQUE (userId ),
Unique key userName_UNIQUE using btree (userName)

) ENGINE = InnoDB AUTO_INCREMENT = 1 default charset = utf8; LATEST DETECTED DEADLOCK

11:12:09 140626
* ** (1) TRANSACTION:
TRANSACTION 0 168550, ACTIVE 13 sec, process no 21006, OS thread id 139721036994304 starting index read
Mysql tables in use 1, locked 1
Lock wait 6 lock struct (s), heap size 1216, 2 row lock (s), undo log entries 2
MySQL thread id 168698, query id 1710850 localhost 127.0.0.1 root Updating
Update users set imei = 'a00000455a4cfe ', last_address = 'no. 303, lat = 31.254289, lon = 121.46208, last_login = '2017-06-26 11:11:56 ', userStatus = 11, deviceId = '2b54f8edd4a2dc4150bc5d8a4e0fb340 ', platform = 'Android', updated = '2017-06-26 11:11:56' where userId = 15
* ** (1) waiting for this lock to be granted:
Record locks space id 0 page no 382 n bits 120 indexPRIMARYOf tablemicrobiz_new.usersTrx id 0 168550 lock_mode X locks rec but not gap waiting
Record lock, heap no 51 physical record: n_fields 35; compact format; info bits 0
0: len 4; hex 8000000f; asc; 1: len 6; hex 000000029264; asc d; 2: len 7; hex 2017100002d2a40; asc-* @; 3: len 10; hex kernel; asc zhouzijian; 4: len 9; hex e591a8e5ad90e5bbba; asc; 5: SQL null; 6: SQL NULL; 7: len 30; hex 653130616463333934396261353961626265353665303537663230663838; asc e10adc3949ba59abbe56e057f20f88 ;... (truncated); 8: len 4; hex 80000004; asc; 9: len 11; hex 3138363538313533393030; asc 18658153900; 10: len 14; hex 4130303030303435354134434645; asc A00000455A4CFE ;; 11: SQL NULL; 12: len 19; hex serial; asc zzhou@plinkchat.com; 13: len 12; hex e4b88ae6b5b7e5bca0e6b19f; asc; 14: len 30; hex serial; asc http://115.28.160.71:8080/micr ;... (Truncated); 15: SQL null; 16: SQL null; 17: SQL null; 18: SQL null; 19: SQL null; 20: SQL null; 21: SQL NULL; 22: len 1; hex 5a; asc Z; 23: len 30; hex serial; asc 303 ;... (truncated); 24: len 8; hex 88bb7a1519413f40; asc z? @; 25: len 8; hex af08feb7925d5e40; asc] ^ @; 26: len 8; hex 800012515ab079e1; asc QZ y; 27: len 8; hex 800012515add6aa0; asc QZ j; 28: len 8; hex 800012515add6aa0; asc QZ j; 29: len 4; hex 80000001; asc; 30: len 4; hex 8001_ B; asc ;; 31: len 4; hex 80000001; asc; 32: len 4; hex 80000003; asc; 33: len 30; hex 324235344638454444344132444334313530424335443841344530464233; asc 2B54F8EDD4A2DC4150BC5D8A4E0FB3 ;... (truncated); 34: len 7; hex 616e64726f6964; asc android ;;

* ** (2) TRANSACTION:
TRANSACTION 0 168551, ACTIVE 1 sec, process no 21006, OS thread id 139720929810176 starting index read, thread declared inside InnoDB 500
Mysql tables in use 1, locked 1
6 lock struct (s), heap size 1216, 2 row lock (s), undo log entries 2
MySQL thread id 168699, query id 1710851 localhost 127.0.0.1 root Updating
Update users set imei = 'a00000455a4cfe ', last_address = 'no. 303, lat = 31.254289, lon = 121.46208, last_login = '2017-06-26 11:12:08 ', userStatus = 11, deviceId = '2b54f8edd4a2dc4150bc5d8a4e0fb340 ', platform = 'Android', updated = '2017-06-26 11:12:08' where userId = 15
* ** (2) holds the lock (S ):
Record locks space id 0 page no 382 n bits 120 indexPRIMARYOf tablemicrobiz_new.usersTrx id 0 168551 lock mode S locks rec but not gap
Record lock, heap no 51 physical record: n_fields 35; compact format; info bits 0
0: len 4; hex 8000000f; asc; 1: len 6; hex 000000029264; asc d; 2: len 7; hex 2017100002d2a40; asc-* @; 3: len 10; hex kernel; asc zhouzijian; 4: len 9; hex e591a8e5ad90e5bbba; asc; 5: SQL null; 6: SQL NULL; 7: len 30; hex 653130616463333934396261353961626265353665303537663230663838; asc e10adc3949ba59abbe56e057f20f88 ;... (truncated); 8: len 4; hex 80000004; asc; 9: len 11; hex 3138363538313533393030; asc 18658153900; 10: len 14; hex 4130303030303435354134434645; asc A00000455A4CFE ;; 11: SQL NULL; 12: len 19; hex serial; asc zzhou@plinkchat.com; 13: len 12; hex e4b88ae6b5b7e5bca0e6b19f; asc; 14: len 30; hex serial; asc http://115.28.160.71:8080/micr ;... (Truncated); 15: SQL null; 16: SQL null; 17: SQL null; 18: SQL null; 19: SQL null; 20: SQL null; 21: SQL NULL; 22: len 1; hex 5a; asc Z; 23: len 30; hex serial; asc 303 ;... (truncated); 24: len 8; hex 88bb7a1519413f40; asc z? @; 25: len 8; hex af08feb7925d5e40; asc] ^ @; 26: len 8; hex 800012515ab079e1; asc QZ y; 27: len 8; hex 800012515add6aa0; asc QZ j; 28: len 8; hex 800012515add6aa0; asc QZ j; 29: len 4; hex 80000001; asc; 30: len 4; hex 8001_ B; asc ;; 31: len 4; hex 80000001; asc; 32: len 4; hex 80000003; asc; 33: len 30; hex 324235344638454444344132444334313530424335443841344530464233; asc 2B54F8EDD4A2DC4150BC5D8A4E0FB3 ;... (truncated); 34: len 7; hex 616e64726f6964; asc android ;;

* ** (2) waiting for this lock to be granted:
Record locks space id 0 page no 382 n bits 120 indexPRIMARYOf tablemicrobiz_new.usersTrx id 0 168551 lock_mode X locks rec but not gap waiting
Record lock, heap no 51 physical record: n_fields 35; compact format; info bits 0
0: len 4; hex 8000000f; asc; 1: len 6; hex 000000029264; asc d; 2: len 7; hex 2017100002d2a40; asc-* @; 3: len 10; hex kernel; asc zhouzijian; 4: len 9; hex e591a8e5ad90e5bbba; asc; 5: SQL null; 6: SQL NULL; 7: len 30; hex 653130616463333934396261353961626265353665303537663230663838; asc e10adc3949ba59abbe56e057f20f88 ;... (truncated); 8: len 4; hex 80000004; asc; 9: len 11; hex 3138363538313533393030; asc 18658153900; 10: len 14; hex 4130303030303435354134434645; asc A00000455A4CFE ;; 11: SQL NULL; 12: len 19; hex serial; asc zzhou@plinkchat.com; 13: len 12; hex e4b88ae6b5b7e5bca0e6b19f; asc; 14: len 30; hex serial; asc http://115.28.160.71:8080/micr ;... (Truncated); 15: SQL null; 16: SQL null; 17: SQL null; 18: SQL null; 19: SQL null; 20: SQL null; 21: SQL NULL; 22: len 1; hex 5a; asc Z; 23: len 30; hex serial; asc 303 ;... (truncated); 24: len 8; hex 88bb7a1519413f40; asc z? @; 25: len 8; hex af08feb7925d5e40; asc] ^ @; 26: len 8; hex 800012515ab079e1; asc QZ y; 27: len 8; hex 800012515add6aa0; asc QZ j; 28: len 8; hex 800012515add6aa0; asc QZ j; 29: len 4; hex 80000001; asc; 30: len 4; hex 8001_ B; asc ;; 31: len 4; hex 80000001; asc; 32: len 4; hex 80000003; asc; 33: len 30; hex 324235344638454444344132444334313530424335443841344530464233; asc 2B54F8EDD4A2DC4150BC5D8A4E0FB3 ;... (truncated); 34: len 7; hex 616e64726f6964; asc android ;;

* ** We roll back transaction (2)