Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-18392

Getting DEADLOCKS frequently on Versioned TABLE

    Details

      Description

      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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                midenok Aleksey Midenkov
                Reporter:
                viveksachidananda Vivek SACHIDANANDA
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: