Tidb: Cannot get auto-id in retry 'INSERT INTO ... SELECT FROM'

Created on 26 Oct 2020 · 5Comments · Source: pingcap/tidb

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Initialization:

set @@tidb_txn_mode=""; -- use optimistic transaction
create table t (id int not null auto_increment unique key, idx int unique key, c int);
create table src (a int);

Session1, Session2 execute for 100 times:

insert into t(idx, c) select 1 as idx, 1 as c from src on duplicate key update c = %d;

Session3 executes for 100 times:

insert into src values (null);

When these sessions run concurrently:

session 1 and session 2 conflict each other on the record (null, 1, %d). This triggers the transaction retry.
The auto_increment ids are allocated before conflicts. They are reused in the following retry.
Since the number of row records in table src keep increasing, the auto ids that allocated before conflicts are not enough in retry. As a result, an index-out-of-range error is converted to Cannot get auto-id in retry.

The complete test code

func (s *seqTestSuite) TestInsertFromSelectConflictRetryAutoID(c *C) {
        tk := testkit.NewTestKitWithInit(c, s.store)
        tk.MustExec("drop table if exists t;")
        tk.MustExec("create table t (id int not null auto_increment unique key, idx int unique key, c int);")
        tk.MustExec("create table src (a int);")
        concurrency := 2
        var wg sync.WaitGroup
        var err []error
        wgCount := concurrency + 1
        wg.Add(wgCount)
        err = make([]error, concurrency)
        for i := 0; i < concurrency; i++ {
                tk := testkit.NewTestKitWithInit(c, s.store)
                tk.MustExec("set autocommit = 1")
                go func(idx int) {
                        for i := 0; i < 10; i++ {
                                sql := fmt.Sprintf("insert into t(idx, c) select 1 as idx, 1 as c from src on duplicate key update c = %[1]d", i)
                                _, e := tk.Exec(sql)
                                if e != nil {
                                        err[idx] = e
                                        wg.Done()
                                        return
                                }
                        }
                        wg.Done()
                }(i)
        }
        var insertErr error
        go func() {
                tk := testkit.NewTestKitWithInit(c, s.store)
                tk.MustExec("set autocommit = 1")
                for i := 0; i < 10; i++ {
                        _, e := tk.Exec("insert into src values (null);")
                        if e != nil {
                                insertErr = e
                                wg.Done()
                                return
                        }
                }
                wg.Done()
        }()
        wg.Wait()
        for _, e := range err {
                c.Assert(e, IsNil)
        }
        c.Assert(insertErr, IsNil)
}

2. What did you expect to see? (Required)

All of these statements should encounter no error.

3. What did you see instead (Required)

release-3.0:

cannot get valid auto-increment id in retry

release-4.0/master

Cannot get a valid auto-ID when retrying the statement

4. What is your TiDB version? (Required)

All of the TiDB versions.

componenexecutor severitcritical siinfra statuWIP typbug

Source

tangenta

👍2

All 5 comments

The description is impressive.

bb7133 on 29 Oct 2020

Please edit this comment to complete the following information

Bug

1. Root Cause Analysis (RCA) (optional)

When executing INSERT statement, the allocated auto-increment ids are cached for the future retries. However, it is possible that the retrying INSERT statement populates a different number of rows. As a result, auto-increment IDs in cache are not enough. For details, see https://github.com/pingcap/tidb/issues/20629.