Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-5675

Concurrent INSERT ... ON DUPLICATE KEY UPDATE statements could cause a failure with a unique index violation.

Details

    Description

      ##how to reproduce:

      test table definition:

      ```sql
      CREATE TABLE `extra` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `a` varchar(36) NOT NULL ,
      `b` varchar(128) NOT NULL ,
      `c` varchar(10240) NOT NULL ,
      PRIMARY KEY (`id`),
      UNIQUE KEY `uniq_idx` (`a`,`b`),
      ) ENGINE=ROCKSDB
      ```

      run several threads concurrently, each run the following logic loops for several times:

      ```sql
      A: insert into extra(a, b, c) values (?,'b2','c2'),(?,'b3','c3'),(?,'b4','tc4'),(?,'b5','c5') on duplicate key update c=values(c);

      B: insert into extra(a, b, c) values (?, 'b1', 'c1'), (?,'b2','c2'),(?,'b3','c3'),(?,'b4','tc4'),(?,'b5','c5') on duplicate key update c=values(c);
      ```

      The value of field a is generated in random among different threads, but same in sql A and sql B in one loop.

      Unexpected result:
      in some rows, the value of field c is not c1 when b = b1

        1. Dive into the code
          when sql B is running after sql A:(sql B has 5 rows to be inserted)
      • row 1 will be success
      • row 2,3,4,5 will be duplicated

      since rocksdb reserve only one increment value each call(see ha_rocksdb::get_auto_increment), row 1 maybe get auto incr value 100, row 2 maybe get auto inc value 110(values between 100 and 110 fetched by other threads).

      when row 2 is duplicated in sql_insert.cc:write_record,
      table->file->restore_auto_increment(prev_insert_id); will be called, and prev_insert_id = 100;

      then when row 3 fetch auto incr (handler::update_auto_increment), next_insert_id is 101, and auto_inc_interval_for_cur_row.maximum() is 110(since row 2 called), so the logic will be incorrenctly, 101 will be used by row 3.

      since 101 is used by other thread, row 3 using this auto inc val will be duplicated on the auto inc key incorrectly, and row 3 will update the row whose auto inc val is 101.

      this will lead to data updated incorrectly.

        1. innodb
          this case will not happen with innodb, because innodb will reserve the auto incr values needed, Since sql B has 5 rows, 100 ~ 104 will be reserved by this thread.

      Attachments

        Activity

          People

            george.lorch George Lorch (Inactive)
            chensi CHENSI
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Smart Checklist