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
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
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)?