1

I've two tables of innoDB type and I'm updating one table using batch statement from 10 thread (one batch statement per thread) but some time I'm getting dead lock.

java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
    at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:1046)
    at com.jolbox.bonecp.StatementHandle.executeBatch(StatementHandle.java:469)

Table 1 schema -

CREATE TABLE `FantasyMatchUserPlayersV2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `contestType` int(11) DEFAULT NULL,
  `lastUpdatedTime` bigint(20) NOT NULL,
  `matchId` int(11) NOT NULL,
  `matchIdPlayerIdContestType` varchar(255) DEFAULT NULL,
  `matchIdPlayerIdContestTypeUserBucket` varchar(255) DEFAULT NULL,
  `playerId` int(11) DEFAULT NULL,
  `score` int(11) NOT NULL,
  `userBucket` int(11) NOT NULL,
  `userId` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `matchId_playerId_contestType_userBucket` (`matchIdPlayerIdContestTypeUserBucket`),
  KEY `matchId_playerId_contestType` (`matchIdPlayerIdContestType`),
  KEY `userId` (`userId`),
  KEY `matchId` (`matchId`),
  KEY `playerId` (`playerId`)
) ENGINE=InnoDB

Table 2 schema -

CREATE TABLE `FantasyMatchContestsV2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `captainId` int(11) DEFAULT NULL,
  `completed` tinyint(1) DEFAULT NULL,
  `contestType` int(11) DEFAULT NULL,
  `createdTime` datetime DEFAULT NULL,
  `lastUpdatedTime` bigint(20) NOT NULL,
  `matchId` int(11) NOT NULL,
  `matchIdContestTypeUserBucket` varchar(255) DEFAULT NULL,
  `points` int(11) NOT NULL,
  `userBucket` int(11) NOT NULL,
  `userId` bigint(20) DEFAULT NULL,
  `viceCaptainId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `userId` (`userId`,`matchIdContestTypeUserBucket`),
  KEY `matchIdContestTypeUserBucket` (`matchIdContestTypeUserBucket`),
  KEY `captainId` (`captainId`),
  KEY `viceCaptainId` (`viceCaptainId`),
  KEY `contestType` (`contestType`),
  KEY `matchId` (`matchId`)
) ENGINE=InnoDB

Batch statements which are being executed by 10 threads, each thread will execute different contest type (from 1 to 10)

update FantasyMatchContestsV2 c inner join FantasyMatchUserPlayersV2 p on c.matchId = p.matchId AND  c.userId = p.userId AND c.contestType= p.contestType AND c.userBucket = p.userBucket set c.points = (c.points + 124), c.lastUpdatedTime=1555161797491  where p.playerId=127 AND p.matchId=40288 AND p.contestType=0 and p.userBucket=0, 
 update FantasyMatchContestsV2 c set points = (points + 248), lastUpdatedTime=1555161797491  where c.captainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=0, 
 update FantasyMatchContestsV2 c set points = (points + 124), lastUpdatedTime=1555161797491  where c.viceCaptainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=0, 
 update FantasyMatchContestsV2 c inner join FantasyMatchUserPlayersV2 p on c.matchId = p.matchId AND  c.userId = p.userId AND c.contestType= p.contestType AND c.userBucket = p.userBucket set c.points = (c.points + 124), c.lastUpdatedTime=1555161797491  where p.playerId=127 AND p.matchId=40288 AND p.contestType=0 and p.userBucket=1, 
 update FantasyMatchContestsV2 c set points = (points + 248), lastUpdatedTime=1555161797491  where c.captainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=1, 
 update FantasyMatchContestsV2 c set points = (points + 124), lastUpdatedTime=1555161797491  where c.viceCaptainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=1, 
 update FantasyMatchContestsV2 c inner join FantasyMatchUserPlayersV2 p on c.matchId = p.matchId AND  c.userId = p.userId AND c.contestType= p.contestType AND c.userBucket = p.userBucket set c.points = (c.points + 124), c.lastUpdatedTime=1555161797491  where p.playerId=127 AND p.matchId=40288 AND p.contestType=0 and p.userBucket=2, 
 update FantasyMatchContestsV2 c set points = (points + 248), lastUpdatedTime=1555161797491  where c.captainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=2, 
 update FantasyMatchContestsV2 c set points = (points + 124), lastUpdatedTime=1555161797491  where c.viceCaptainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=2, 
 update FantasyMatchContestsV2 c inner join FantasyMatchUserPlayersV2 p on c.matchId = p.matchId AND  c.userId = p.userId AND c.contestType= p.contestType AND c.userBucket = p.userBucket set c.points = (c.points + 124), c.lastUpdatedTime=1555161797491  where p.playerId=127 AND p.matchId=40288 AND p.contestType=0 and p.userBucket=3, 
 update FantasyMatchContestsV2 c set points = (points + 248), lastUpdatedTime=1555161797491  where c.captainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=3, 
 update FantasyMatchContestsV2 c set points = (points + 124), lastUpdatedTime=1555161797491  where c.viceCaptainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=3, 
 update FantasyMatchContestsV2 c inner join FantasyMatchUserPlayersV2 p on c.matchId = p.matchId AND  c.userId = p.userId AND c.contestType= p.contestType AND c.userBucket = p.userBucket set c.points = (c.points + 124), c.lastUpdatedTime=1555161797491  where p.playerId=127 AND p.matchId=40288 AND p.contestType=0 and p.userBucket=4, 
 update FantasyMatchContestsV2 c set points = (points + 248), lastUpdatedTime=1555161797491  where c.captainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=4, 
 update FantasyMatchContestsV2 c set points = (points + 124), lastUpdatedTime=1555161797491  where c.viceCaptainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=4, 
 update FantasyMatchContestsV2 c inner join FantasyMatchUserPlayersV2 p on c.matchId = p.matchId AND  c.userId = p.userId AND c.contestType= p.contestType AND c.userBucket = p.userBucket set c.points = (c.points + 124), c.lastUpdatedTime=1555161797491  where p.playerId=127 AND p.matchId=40288 AND p.contestType=0 and p.userBucket=5, 
 update FantasyMatchContestsV2 c set points = (points + 248), lastUpdatedTime=1555161797491  where c.captainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=5, 
 update FantasyMatchContestsV2 c set points = (points + 124), lastUpdatedTime=1555161797491  where c.viceCaptainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=5, 
 update FantasyMatchContestsV2 c inner join FantasyMatchUserPlayersV2 p on c.matchId = p.matchId AND  c.userId = p.userId AND c.contestType= p.contestType AND c.userBucket = p.userBucket set c.points = (c.points + 124), c.lastUpdatedTime=1555161797491  where p.playerId=127 AND p.matchId=40288 AND p.contestType=0 and p.userBucket=6, 
 update FantasyMatchContestsV2 c set points = (points + 248), lastUpdatedTime=1555161797491  where c.captainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=6, 
 update FantasyMatchContestsV2 c set points = (points + 124), lastUpdatedTime=1555161797491  where c.viceCaptainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=6, 
 update FantasyMatchContestsV2 c inner join FantasyMatchUserPlayersV2 p on c.matchId = p.matchId AND  c.userId = p.userId AND c.contestType= p.contestType AND c.userBucket = p.userBucket set c.points = (c.points + 124), c.lastUpdatedTime=1555161797491  where p.playerId=127 AND p.matchId=40288 AND p.contestType=0 and p.userBucket=7, 
 update FantasyMatchContestsV2 c set points = (points + 248), lastUpdatedTime=1555161797491  where c.captainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=7, 
 update FantasyMatchContestsV2 c set points = (points + 124), lastUpdatedTime=1555161797491  where c.viceCaptainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=7, 
 update FantasyMatchContestsV2 c inner join FantasyMatchUserPlayersV2 p on c.matchId = p.matchId AND  c.userId = p.userId AND c.contestType= p.contestType AND c.userBucket = p.userBucket set c.points = (c.points + 124), c.lastUpdatedTime=1555161797491  where p.playerId=127 AND p.matchId=40288 AND p.contestType=0 and p.userBucket=8, 
 update FantasyMatchContestsV2 c set points = (points + 248), lastUpdatedTime=1555161797491  where c.captainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=8, 
 update FantasyMatchContestsV2 c set points = (points + 124), lastUpdatedTime=1555161797491  where c.viceCaptainId=127 and c.matchId=40288 and c.contestType=0 and c.userBucket=8

UPDATE

Now I've changed the queries and implemented between as per the below recommendation but still getting dead lock.

Updated batch statements are -

update FantasyMatchContestsV2 c inner join FantasyMatchUserPlayersV2 p on c.matchId = p.matchId AND  c.userId = p.userId AND c.contestType= p.contestType AND c.userBucket = p.userBucket set c.points = (c.points + 124), c.lastUpdatedTime=1555471826690  where p.playerId=127 AND p.matchId=40288 AND p.contestType=3 and p.userBucket between 0 AND 618, 
 update FantasyMatchContestsV2 c set points = (points + 248), lastUpdatedTime=1555471826690  where c.captainId=127 and c.matchId=40288 and c.contestType=3 and c.userBucket between 0 AND 618, 
 update FantasyMatchContestsV2 c set points = (points + 124), lastUpdatedTime=1555471826690  where c.viceCaptainId=127 and c.matchId=40288 and c.contestType=3 and c.userBucket between 0 AND 618 

engine innoDB status -

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-04-17 09:00:30 0x7f03f902a700
*** (1) TRANSACTION:
TRANSACTION 435329, ACTIVE 0 sec starting index read
mysql tables in use 4, locked 4
LOCK WAIT 1151 lock struct(s), heap size 139472, 28369 row lock(s)
MySQL thread id 328, OS thread handle 139655045064448, query id 6576 localhost 127.0.0.1 root Sending data
update FantasyMatchContestsV2 c inner join FantasyMatchUserPlayersV2 p on c.matchId = p.matchId AND  c.userId = p.userId AND c.contestType= p.contestType AND c.userBucket = p.userBucket set c.points = (c.points + 1), c.lastUpdatedTime=1555471829786  where p.playerId=643 AND p.matchId=40288 AND p.contestType=9 and p.userBucket between 0 AND 0
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 207 page no 333 n bits 376 index userId of table `fantasy`.`FantasyMatchContestsV2` trx id 435329 lock_mode X waiting
Record lock, heap no 164 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8000000006181096; asc         ;;
 1: len 19; hex 34303238315f4348414c4c454e474552535f37; asc 40281_CHALLENGERS_7;;
 2: len 8; hex 8000000000003623; asc       6#;;

*** (2) TRANSACTION:
TRANSACTION 435330, ACTIVE 0 sec starting index read
mysql tables in use 4, locked 4
1028 lock struct(s), heap size 123088, 91820 row lock(s)
MySQL thread id 332, OS thread handle 139655039330048, query id 6572 localhost 127.0.0.1 root Sending data
update FantasyMatchContestsV2 c inner join FantasyMatchUserPlayersV2 p on c.matchId = p.matchId AND  c.userId = p.userId AND c.contestType= p.contestType AND c.userBucket = p.userBucket set c.points = (c.points + 1), c.lastUpdatedTime=1555471829786  where p.playerId=643 AND p.matchId=40288 AND p.contestType=7 and p.userBucket between 0 AND 22
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 207 page no 333 n bits 376 index userId of table `fantasy`.`FantasyMatchContestsV2` trx id 435330 lock_mode X
Record lock, heap no 164 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8000000006181096; asc         ;;
 1: len 19; hex 34303238315f4348414c4c454e474552535f37; asc 40281_CHALLENGERS_7;;
 2: len 8; hex 8000000000003623; asc       6#;;

Record lock, heap no 165 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8000000006181096; asc         ;;
 1: len 12; hex 34303238315f4d4547415f30; asc 40281_MEGA_0;;
 2: len 8; hex 80000000000035ff; asc       5 ;;

Query execution plan -

mysql> explain update FantasyMatchContestsV2 c inner join FantasyMatchUserPlayersV2 p on c.matchId = p.matchId AND  c.userId = p.userId AND c.contestType= p.contestType AND c.userBucket = p.userBucket set c.points = (c.points + 124), c.lastUpdatedTime=1555471826690  where p.playerId=127 AND p.matchId=40288 AND p.contestType=3 and p.userBucket between 0 AND 618;
+----+-------------+-------+------------+-------------+-------------------------+------------------+---------+------------------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys           | key              | key_len | ref              | rows | filtered | Extra                                          |
+----+-------------+-------+------------+-------------+-------------------------+------------------+---------+------------------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | p     | NULL       | index_merge | userId,matchId,playerId | playerId,matchId | 5,4     | NULL             | 5591 |     1.11 | Using intersect(playerId,matchId); Using where |
|  1 | UPDATE      | c     | NULL       | ref         | userId                  | userId           | 9       | fantasy.p.userId |    3 |     1.33 | Using where                                    |
+----+-------------+-------+------------+-------------+-------------------------+------------------+---------+------------------+------+----------+------------------------------------------------+
2 rows in set (0.00 sec)

mysql>  
mysql>  explain update FantasyMatchContestsV2 c set points = (points + 248), lastUpdatedTime=1555471826690  where c.captainId=127 and c.matchId=40288 and c.contestType=3 and c.userBucket between 0 AND 618;
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys      | key                | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+-------------------------+------+----------+-------------+
|  1 | UPDATE      | c     | NULL       | range | captMatchContUserB | captMatchContUserB | 18      | const,const,const,const |   51 |   100.00 | Using where |
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+-------------------------+------+----------+-------------+
1 row in set (0.00 sec)

mysql>  
mysql>  explain update FantasyMatchContestsV2 c set points = (points + 124), lastUpdatedTime=1555471826690  where c.viceCaptainId=127 and c.matchId=40288 and c.contestType=3 and c.userBucket between 0 AND 618;
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys       | key                 | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+-------------------------+------+----------+-------------+
|  1 | UPDATE      | c     | NULL       | range | vcaptMatchContUserB | vcaptMatchContUserB | 18      | const,const,const,const |  119 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+-------------------------+------+----------+-------------+
1 row in set (0.00 sec)
2

a) deadlock handling is a application responsibility.

b) Use multiple columns for indexes like:

  CREATE INDEX captMatchContUserB ON FantasyMatchContestsV2(captainId, matchId, contestType, userBucket)

  CREATE INDEX vcaptMatchContUserB ON FantasyMatchContestsV2(viceCaptainId, matchId, contestType, userBucket)

This will efficiently handle your queries. Use EXPLAIN {query} to example index use.

c) Why not p.userBucket BETWEEN 0 AND 8 in your WHERE clause?

d) And with the p.contestType BETWEEN 0 AND 10

When you're doing the same query in a loop there's usually a deadlock free way to do it in SQL directly.

To make the JOINed update more efficient and to give it a index to lock on:

CREATE INDEX playerMatchContestBucker ON 
 FantasyMatchUserPlayersV2(playerId,matchId,contestType,userBucket);

If this is unique use CREATE UNIQUE INDEX ...

In the EXPLAIN, the JOIN ON FantasyMatchContestsV2 resulted in the userId index being used. As userId and matchId where both the join criteria, there should be an index there on both.

 ALTER TABLE FantasyMatchContestsV2
   DROP INDEX userId,
   ADD INDEX userIDMatchId(userId, matchId);
| improve this answer | |
  • Thanks @Danblack, point C is reasonable but my main concern is to avoid dead lock. will it achieve by doing multiple column indexes ? – ankit.vishen Apr 16 '19 at 4:01
  • well-chosen indexes was a lock reduction mechanism but also just makes queries faster. You may not be able to always avoid it. Handling it is an important application development step. – danblack Apr 16 '19 at 4:49
  • have created the index and changed the queries as you recommended but still getting the lock. Please check the update section – ankit.vishen Apr 17 '19 at 3:45
  • Added FantasyMatchUserPlayersV2 index for efficient search and changed FantasyMatchContestsV2(userId) to anchor its lock point, though looking at the queries you have two queries updating the same FantasyMatchContestsV2(userId,matchId) so why wouldn't you expect a deadlock? Handle the error and rerun it. – danblack Apr 17 '19 at 6:22
0

Run these queries(for each contestType) and check if following select queries returns same row

select * from FantasyMatchContestsV2 c inner join FantasyMatchUserPlayersV2 p on c.matchId = p.matchId AND  c.userId = p.userId AND c.contestType= p.contestType AND c.userBucket = p.userBucket  where p.playerId=127 AND p.matchId=40288 AND p.contestType=7 and p.userBucket=0
select * from FantasyMatchContestsV2 c inner join FantasyMatchUserPlayersV2 p on c.matchId = p.matchId AND  c.userId = p.userId AND c.contestType= p.contestType AND c.userBucket = p.userBucket  where p.playerId=127 AND p.matchId=40288 AND p.contestType=9 and p.userBucket=0

If these queries returns same row which you are trying to update in different transaction, then definitely there will be deadlock. To avoid deadlock write your where clause in a way that for each thread it returns unique rows.

As you can see clearly in query logs deadlock is on contestType 7 and 9. It means both queries are trying to update same row in different transactions.

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.