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)
  • Compress similar queries (there is 3 packs of queries which differs in last and c.userBucket=X). – Akina Apr 16 at 6:52
  • Thanks Akina! will do the same. But I don't understand why I'm getting the deadlock. – ankit.vishen Apr 16 at 7:15
  • Maybe because you execute these queries as one single transacion? Compress this query pack into 3 queries. Execute each of them as a single transacion. See InnoDB status while deadlock - who and what locks and waits. – Akina Apr 16 at 7:36
  • Hey @Akina, please check the update section. I've made the changes but still getting the lock. – ankit.vishen Apr 17 at 3:48
  • I've made the changes but still getting the lock. Last 2 single-table queries are safe for deadlock. The problem is the first 2-table query. Try to use SELECT ... FOR UPDATE to get lock, then UPDATE to alter selected records. – Akina Apr 17 at 5:56
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);
  • 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 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 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 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 at 6:22
0

You have to check first the explain plan of the query, then you can decide which column need to index.

Also please check the time taking of executing per query.

  • The question is not about performance, but about deadlock. – Akina Apr 16 at 7:34
  • The deadlock is because the update query is taking time and not release the lock. share the explain plan. – Deepesh Uniyal Apr 16 at 7:44
  • because the update query is taking time and not release the lock Longer than innodb_lock_wait_timeout? I doubt... share the explain plan And what the plan can say about deadlock, or about locks at least? – Akina Apr 16 at 7:57
  • @DeepeshUniyal default time is 50 sec and if it happens bcz of time then I would get Lock wait timeout exceeded; try restarting transaction, so time is not an issue here. Have added the explain plan – ankit.vishen Apr 17 at 3:56
  • Hi, In the First query you are locking both tables as the mysql locks all table using in join no matter data is updating of that table or not. so this FantasyMatchUserPlayersV2 has been locked. and then before committing this if you update on FantasyMatchUserPlayersV2 table from another thread it will throw the timeout. It seems you have done auto-commit false at application label (@Transactional). You have to change application design Instead of join you can select the data of that table so this table will not get locked. or you can set auto-commit true. – Deepesh Uniyal Apr 18 at 6:42
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.

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.