sql: lease acquisition can repeatedly contend with DDL statement when closed_timestamp target_duration and side_transport_interval are very low

[stevendanna]

Describe the problem

A transaction with a DDL statement can contend with a lease-acquisition transaction when kv.closed_timestamp.target_duration and kv.closed_timestamp.side_transport_interval are set very low.

The following test, when run under stress, will typically hit a timeout if you set the per-test timeout to 3-5 minutes:

func TestMultipleGrantsLeaseStall(t *testing.T) {
	params := base.TestClusterArgs{}
	params.ServerArgs.Knobs = base.TestingKnobs{
		JobsTestingKnobs: jobs.NewTestingKnobsWithShortIntervals(),
	}
	settings := cluster.MakeTestingClusterSettings()
	closedts.TargetDuration.Override(context.Background(), &settings.SV, 10*time.Millisecond)
	closedts.SideTransportCloseInterval.Override(context.Background(), &settings.SV, 10*time.Millisecond)
	params.ServerArgs.Settings = settings
	tc := testcluster.StartTestCluster(t, 1, params)
	conn := tc.ServerConn(0)
	_, err := conn.Exec(`
CREATE USER user1;
CREATE USER testuser;
`)
	require.NoError(t, err)
	_, err = conn.Exec(`
CREATE DATABASE testdb;
USE testdb;
CREATE TYPE testdb.greeting_usage AS ENUM ('howdy');
CREATE TABLE testdb.testtable_simple (a int);
CREATE TABLE testdb.testtable_greeting_usage (a greeting_usage);
CREATE SCHEMA sc;
CREATE TABLE testdb.sc.othertable (a INT);
`)
	require.NoError(t, err)

	_, err = conn.Exec(`
GRANT ALL ON DATABASE testdb TO user1 WITH GRANT OPTION;
GRANT ALL ON SCHEMA public TO user1 WITH GRANT OPTION;
GRANT USAGE ON SCHEMA sc TO user1;
GRANT SELECT ON testdb.sc.othertable TO user1;
`)
	require.NoError(t, err)
}

When running:

GRANT ALL ON DATABASE testdb TO user1 WITH GRANT OPTION;
GRANT ALL ON SCHEMA public TO user1 WITH GRANT OPTION;
GRANT USAGE ON SCHEMA sc TO user1;
GRANT SELECT ON testdb.sc.othertable TO user1;

The first statement writes to the descriptor for testdb. The last statement results in a lease acquisition attempt on testdb. That least acquisition attempt runs in another txn that pushes the txn containing the DDL statements. But, it seems that it never pushes it far enough.

Jira issue: CRDB-20491

[ajwerner]

Hold on, the bug here is that the last statement acquires a lease on testdb. That shouldn't happen if we've already modified testdb in the current transaction. That's a severe bug.

[stevendanna]

@ajwerner Should this be a GA blocker then? I haven't tested it on other branches.

[postamar]

I'm unable to repro this. Putting this back in triage because I don't have time to look into this further right now.

[stevendanna]

@postamar I just tried this again, and for reasons that aren't clear to me, this is nearly impossible to reproduce for me on my macOS. But on a gceworker I can reproduce it with:

./dev test ./pkg/sql --filter TestMultipleGrantsLeaseStall --show-logs --verbose --stress --test-args='-test.timeout 3m' --stress-args '-p 3' 2>&1 | tee stresslog5.out

Note that the -test.timeout 3m is needed because I think often the condition clears eventually.

Anyway, I trust y'alls judgement on whether this is worth looking into, just mentioned this since I found it odd that I wasn't able to reproduce it for a bit either.

[ajwerner]

I wonder if this is caused by #91116

[stevendanna]

@ajwerner Well, it appears to be fixed on master, but git-bisect seems to be pointing at 1c11f2c as the place it was fixed which doesn't yet make a whole lot of sense to me.

[ajwerner]

That definitely surprises me. I'd expect it to be #91116.