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

Getting DEADLOCKS frequently on Versioned TABLE

    XMLWordPrintable

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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.