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

Deadlock when inserting NULL column value in column with UNIQUE index

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.33a-galera, 5.5.34-galera
    • 5.5.36-galera
    • None

    Description

      There is a problem that is consistently causing deadlocks when trying to INSERT or DELETE a row with a NULL value in a column that 1) can be NULL and 2) has a UNIQUE index.

      I've tested this on MariaDB 5.5.34 and it has this problem. On MySQL 5.5.34 it does NOT seem to have the problem. I narrowed this down to a very simple table setup to reproduce. Simply create a table with the following:

      CREATE TABLE `deadlock_test` (
        `buggy_column` int(11) DEFAULT NULL,
        UNIQUE KEY `buggy_column` (`buggy_column`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

      Note that the column type does not matter. It can be VARCHAR or whatever else, so long as it can be NULL and it has a UNIQUE index.

      Observations:
      1) If you try to insert a row with the column value set to NULL it will deadlock.
      2) If you insert a row with a value in the column it works. You can then UPDATE the column to NULL and it works fine.
      3) If, while the column is NULL you try to DELETE the row, it deadlocks. You can, however, update it to have a value and then DELETE without a problem.

      Attachments

        1. conf.d.tar
          10 kB
          Justin Warkentin
        2. config_dump
          14 kB
          Justin Warkentin
        3. my.cnf
          5 kB
          Justin Warkentin

        Activity

          People

            nirbhay_c Nirbhay Choubey (Inactive)
            jwarkentin Justin Warkentin
            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.