1

I am having trouble to understand the increase in DEADLOCKS for Update transactions on Versioned TABLES. The problem, was not present earlier and then we enabled the versioning and after a while we started to receive many DEADLOCK exceptions.

Here is the TABLE:

CREATE TABLE `sample_table` (
  `id` bigint(20) unsigned NOT NULL WITHOUT SYSTEM VERSIONING,
  `amount` bigint(20) NOT NULL DEFAULT 0,
  `release_date` date DEFAULT NULL,
  `details` VARCHAR(64) DEAFULT NULL, 
  `changed_by_user_id` int(10) unsigned DEFAULT NULL,
  `last_changed_at` timestamp NOT NULL DEFAULT current_timestamp() WITHOUT SYSTEM VERSIONING,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME LIMIT 100000
(PARTITION `p0` HISTORY ENGINE = InnoDB,
 PARTITION `p1` HISTORY ENGINE = InnoDB,
 PARTITION `p2` HISTORY ENGINE = InnoDB,
 PARTITION `p3` HISTORY ENGINE = InnoDB,
 PARTITION `p4` HISTORY ENGINE = InnoDB,
 PARTITION `pcur` CURRENT ENGINE = InnoDB)

I read documents on MariaDB/SQL still cannot pin point on whats causing the deadlock. Here is the log

Deadlock Info : update sample_table set changed_by_user_id=8127,details='sample' where id=815977 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 10235669 page no 94 n bits 192 index PRIMARY of table ebs.sample_table /* Partition p0 */ trx id 1903402610 lock_mode X locks rec but not gap waiting

I am suspecting that its due to HISTORY using INNODB and its causing deadlocks (self)?

  • File a bug with MariaDB. You may be the only person using this feature; hence this forum may be of any help. – Rick James Jan 25 at 16:21

Your Answer

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

Browse other questions tagged or ask your own question.