0

I have this table:

CREATE TABLE `ClusterDescription` (
  `ClusterDescriptionId` int(11) NOT NULL AUTO_INCREMENT,
  `ClusterId` int(11) NOT NULL,
  `RightBarDateTime` datetime NOT NULL,
  `InstrumentId` int(11) NOT NULL,
  `RowCreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ClusterDescriptionId`),
  UNIQUE KEY `ClusterInstrumentId` (`ClusterId`,`InstrumentId`),
  KEY `InstrumentId` (`InstrumentId`) USING BTREE,
  KEY `OrderDesc` (`RightBarDateTime` DESC,`ClusterId` DESC)
) ENGINE=InnoDB AUTO_INCREMENT=3055989 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci 

The table has more than 3m rows.

I receive regularly this error:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

for this command:

INSERT INTO ClusterDescription (ClusterId, RightBarDateTime, InstrumentId)
 SELECT IFNULL(MAX(ClusterId) + 1, 1), :rightBarDateTime, :instrumentId
 FROM ClusterDescription WHERE InstrumentId = :instrumentId
  • There can be up to 50 above INSERT at almost the same time.
  • :instrumentId is never the same in any of the parallel commands.
  • Checking the process list I see lot of these commands waiting to be executed with a Time value > 30 seconds.
  • I set innodb_lock_wait_timeout to 360 seconds which improved the situation. Before I received lot of the above mentioned error and this error:

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

My questions:

  • What is wrong with this table definition?

  • Why do need the parallel INSERT commands more than 50 seconds to be executed?

  • Why do I receive a dead lock error when only 1 table is affected?

Running on MySQL 8.0.13

I need the InnoDB engine because of the descending index.

1

Both of these improve the efficiency of the SELECT MAX... in the INSERT..SELECT:

Plan A

UNIQUE KEY `ClusterInstrumentId` (`ClusterId`,`InstrumentId`),
KEY `InstrumentId` (`InstrumentId`, ClusterId) USING BTREE,

This assumes you need to look up things by ClusterId.

Plan B

UNIQUE (`InstrumentId`, ClusterId)

This covers the Uniqueness constraint

  • I applied PLAN B. I will check the log files tomorrow morning and let you know/accept this answer. That far it looks very good what I see in the few minutes it is running with the new index. THX! – Al Bundy Jan 7 at 20:58
  • Yes, now the INSERT command is executed instantly. The Dead Lock error message and all other don't appear anymore. Enjoy your Banana Latte! – Al Bundy Jan 8 at 10:02

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.