MySQL Innodb Table Dead Lock during batch update in multiple threads

I have two innoDB tables and I'm updating a table with a batch of 10 threads (one batch per thread), but some time I get dead-lock.

java.sql.BatchUpdateException: Deadlock encountered while trying to get a lock; Try restarting the transaction
at com.mysql.jdbc.StatementImpl.executeBatch (StatementImpl.java:1046)
at com.jolbox.bonecp.StatementHandle.executeBatch (StatementHandle.java:469)

Table 1 Scheme –

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 Scheme –

CREATE TABLE `FantasyMatchContestsV2` (
`id` bigint (20) NOT NULL AUTO_INCREMENT,
`captainId` int (11) DEFAULT NULL,
`complete` 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 executed by 10 threads, each thread executes a different type of contest (from 1 to 10)

update FantasyMatchContestsV2 c inner Join FantasyMatchUserPlayersV2 p to 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-setpoints = (points + 248), lastUpdatedTime = 1555161797491 where c.captainId = 127 and c.matchId = 40288 and c.contestType = 0 and c.userBucket = 0.
Update FantasyMatchContestsV2 c-setpoints = (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 to 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,
Updating FantasyMatchContestsV2 c-setpoints = (points + 248), lastUpdatedTime = 1555161797491 where c.captainId = 127 and c.matchId = 40288 and c.contestType = 0 and c.userBucket = 1.
Updating FantasyMatchContestsV2 c-setpoints = (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 to 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-setpoints = (points + 248), lastUpdatedTime = 1555161797491 where c.captainId = 127 and c.matchId = 40288 and c.contestType = 0 and c.userBucket = 2.
Update FantasyMatchContestsV2 c-setpoints = (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 to 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-setpoints = (points + 248), lastUpdatedTime = 1555161797491 where c.captainId = 127 and c.matchId = 40288 and c.contestType = 0 and c.userBucket = 3.
Update FantasyMatchContestsV2 c-setpoints = (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 to 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-setpoints = (points + 248), lastUpdatedTime = 1555161797491 where c.captainId = 127 and c.matchId = 40288 and c.contestType = 0 and c.userBucket = 4,
Update FantasyMatchContestsV2 c-setpoints = (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 to 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-setpoints = (points + 248), lastUpdatedTime = 1555161797491 where c.captainId = 127 and c.matchId = 40288 and c.contestType = 0 and c.userBucket = 5,
Update FantasyMatchContestsV2 c-setpoints = (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 to 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-setpoints = (points + 248), lastUpdatedTime = 1555161797491 where c.captainId = 127 and c.matchId = 40288 and c.contestType = 0 and c.userBucket = 6,
Update FantasyMatchContestsV2 c-setpoints = (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 to 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,
Updating FantasyMatchContestsV2 c-setpoints = (points + 248), lastUpdatedTime = 1555161797491 where c.captainId = 127 and c.matchId = 40288 and c.contestType = 0 and c.userBucket = 7,
Update FantasyMatchContestsV2 c-setpoints = (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 to 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-setpoints = (points + 248), lastUpdatedTime = 1555161797491 where c.captainId = 127 and c.matchId = 40288 and c.contestType = 0 and c.userBucket = 8,
Update the c-points of FantasyMatchContestsV2 = (points + 124), lastUpdatedTime = 1555161797491, where c.viceCaptainId = 127 and c.matchId = 40288 and c.contestType = 0 and c.userBucket = 8

dnd 5e – Can you end your move in the same room as an unconscious or dead creature?

The rules say:

Whether a creature is a friend or an enemy, you can not voluntarily end your movement in your area.

What happens if this creature was either knocked unconscious or killed? Are you still prevented from voluntarily ending your move?

I remember a wise piece of advice that says dead creatures are no longer creatures. Common sense suggests that a dead dragon is still an obstacle.

Dead sales?

Hello Amazonians, Shopifier and other warriors out there,

Glad to be here! I want to help the sales community here, maybe you can help me again.

I hate bullshit, so this is exactly what the product I built can do for you.

Hami helps sellers internationalize their listings and increase sales in local markets.

Product lists are translated by professional translators. We have over 1k certified professionals ready. After translation, the products in the native language receive separate listings for the countries. The sale in Japan has a product description in Japanese.
SEMrush

Dead sales? Christmas should not be the only time of year when you achieve 300% sales. The product may also hyper-locate your entries in response to events such as national holidays and think black fridays.
Coupon codes will be generated for the event. The product list contains the corresponding promo message.

For example, Germany celebrates Pentecost. In the product description, the following text is added to the list at the top (or bottom).
(Read it in your head) Happy Pentecost! Use the promo code Pentecost 5 for a 5% discount. Hurry up, less than 4 hours left.
The coupons only work in the local Amazon store. The Whitsun 5 code is only available in the Amazon.com store.

You just have to click on the buttons to make this possible.

The testing of this system has resulted in a turnover of + 300%. We currently offer it for free, but plan to spend $ 90 a month. If you are interested in a 3-month free license, please complete this form: https://hamiconnect.typeform.com/to/RD78oZ

Any feedback would help me enormously at this point! What do you think about this? Would you use Hami?

GDPR – Website is dead, there is no contact data – Is the hosting company responsible?

Good evening everyone. My first post here. Hopefully someone can help me? …

I am based in the UK. A publicly available website has my personal phone number that I would like to remove. The company behind the website is dissolved and the small website is dead, with no contact details maintained.

I contacted the hosting company. The data protection officer answered:

—–

We write regarding your data deletion request.

The data will be displayed on a public web site managed by a third party with OVH services. Therefore, OVH is not responsible for the processing of this data in accordance with the provisions of the GDPR. Therefore, we can not process your request.

Please contact the publisher of the website concerned to exercise your cancellation right.

——

Are they correct? Surely the company is not paying anymore for the hosting, so I thought they'd like to know that and remove the site from their servers.

Any guidance would be very grateful.

Centex hosting: dead? | Web Hosting Talk

I have a dedicated server at Centex Hosting that stopped pinging almost 5 hours ago. The Centex website is inactive, emails do not work, and calls are forwarded directly to voicemail. I tried to contact the owner, Brady Huddleston, but did not get an answer.

After searching for ASN from Centex I found out that all servers are hosted on Psychz.net. I turned to Psychz and got the following answer on my ticket:

We have checked and yes, the service is banned because of invoicing.

We have informed the customer and as soon as he is paid, we will resume his services.

On this basis, it seems that our services are offline because Centex is lagging behind in payments with Psychz. Centex apparently has all the hardware, so Psychz can not help us in any way. I phoned psychz a few hours ago and asked what would happen to our data if Centex went out of business tomorrow … The agent told me that our data would be lost and that they would not help us with anything restore our server there.

I wanted to post this here because I'm sure many others have problems. Has anyone been able to reach Brady? I want to recover my data and migrate this server if possible.

[ Politics ] Open Question: Can there be anything more cowardly than a draft evading a president who attacks a dead senator who served his country?

Trump said, among other things, "I never got any thanks for giving him the funeral he wanted, because as president, I approved that."

What the hell is wrong with this idiot, is he really that low and selfish ??? ,

Is Trump a wretched little man who attacked a dead American war hero like John McCain?

Report abuse

more details