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

[tangenta]

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.

[bb7133]

The description is impressive.

[ti-srebot]

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 #20629.

2. Symptom (optional)

TiDB reports Cannot get auto-id in retry 'INSERT INTO ... SELECT FROM' when inserting rows.

3. All Trigger Conditions (optional)

• Concurrent INSERT .. ON DUPLICATE UPDATE triggers statements retry.
• Another changing table is the source of the INSERT statement.

4. Workaround (optional)

Retry the statement in application.

5. Affected versions

[v3.0.0:v3.0.19], [v4.0.0:v4.0.8]

6. Fixed versions

v4.0.9

[ti-srebot]

The values in ( FixedVersions ) fields are incorrect.

[ti-srebot]

The values in ( FixedVersions ) fields are incorrect.

[zz-jason]

@tangenta please cherry-pick the bugfix to the release branches.