Mysql innodb table dead lock while batch update in multiple thread Announcing the arrival of...
What LEGO pieces have "real-world" functionality?
How to draw this diagram using TikZ package?
Is there a service that would inform me whenever a new direct route is scheduled from a given airport?
The logistics of corpse disposal
What is the correct way to use the pinch test for dehydration?
Withdrew £2800, but only £2000 shows as withdrawn on online banking; what are my obligations?
Do you forfeit tax refunds/credits if you aren't required to and don't file by April 15?
How to bypass password on Windows XP account?
ListPlot join points by nearest neighbor rather than order
Can Pao de Queijo, and similar foods, be kosher for Passover?
What causes the vertical darker bands in my photo?
How do I keep my slimes from escaping their pens?
Gastric acid as a weapon
Does surprise arrest existing movement?
Letter Boxed validator
Is there a concise way to say "all of the X, one of each"?
Is above average number of years spent on PhD considered a red flag in future academia or industry positions?
What are the pros and cons of Aerospike nosecones?
"Seemed to had" is it correct?
What do you call a plan that's an alternative plan in case your initial plan fails?
How to find all the available tools in macOS terminal?
How does a Death Domain cleric's Touch of Death feature work with Touch-range spells delivered by familiars?
How much radiation do nuclear physics experiments expose researchers to nowadays?
What is a Meta algorithm?
Mysql innodb table dead lock while batch update in multiple thread
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)Unexplained InnoDB timeoutsAre two indexes needed?Is a update-only-once-row table worth sharding?Optimizing a simple query on a large tableNeed help improving sql query performanceMySQL query taking too longslow queries on indexed columns (large datasets)Why does the Select statement stalls when executed directly in sequence?Joining mysql tablesWhat does 'innodb_buffer_pool_reads' and 'innodb_buffer_pool_read_requests' actually mean?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
0
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
mysql innodb update multi-thread batch-mode
asked 1 min ago
ankit.vishenankit.vishen
1011
New contributor
ankit.vishen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
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
mysql innodb update multi-thread batch-mode
asked 1 min ago
ankit.vishenankit.vishen
1011
New contributor
ankit.vishen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
0
0
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
mysql innodb update multi-thread batch-mode
asked 1 min ago
ankit.vishenankit.vishen
1011
New contributor
ankit.vishen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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
mysql innodb update multi-thread batch-mode
mysql innodb update multi-thread batch-mode
asked 1 min ago
ankit.vishenankit.vishen
1011
New contributor
ankit.vishen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 min ago
ankit.vishenankit.vishen
1011
New contributor
ankit.vishen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 min ago
ankit.vishenankit.vishen
1011
New contributor
ankit.vishen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 min ago
ankit.vishenankit.vishen
1011
asked 1 min ago
ankit.vishenankit.vishen
1011
1011
New contributor
ankit.vishen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
ankit.vishen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
ankit.vishen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
ankit.vishen is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234860%2fmysql-innodb-table-dead-lock-while-batch-update-in-multiple-thread%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
ankit.vishen is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
ankit.vishen is a new contributor. Be nice, and check out our Code of Conduct.
ankit.vishen is a new contributor. Be nice, and check out our Code of Conduct.
ankit.vishen is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234860%2fmysql-innodb-table-dead-lock-while-batch-update-in-multiple-thread%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
