sql/catalog/lease: high priority reads in lease acquisition of OFFLINE descs may starve bulk operations

[ajwerner]

Describe the problem

In (#46170, #46384) we made changes to use high priority read transaction in lease acquisitions in order to break deadlocks. This solution isn't wonderful: if users use PRIORITY HIGH on their DDL transactions, they can still hit these deadlocks. Given people tend not to do that, this proved to be a pretty good and pragmatic solution to a very serious problem.

When code attempts to resolve descriptors it goes through the leasing mechanism. This is true even if the descriptor does not exist. This is made much worse in a case where you've got a multi-region cluster.

The scenario that causes specific pain is when you've got an application running and trying to hit some tables but you're in the process of restoring those tables. As part of the restore, we need to update the descriptors. If the restore job is far from the descriptor table leaseholder, then there's a long period of time for interceding queries to push the intent. After getting pushed, the query will need to refresh. If the pushing is happening more frequently than the refresh's duration, we'll get stuck in livelock.

For what it's worth, all of this is modestly speculative. We saw the pushing but I don't know that we've captured enough data.

To Reproduce

In theory, start a restore in a global cluster and get the leaseholder on the system config span in a different region than the restore coordinator. Then spam the database being restored and see if you can hold off the restore.

Some ideas

• We could lease and cache the offline descriptor. This is probably the most compelling choice. It's not too hard.
• We could make the lease acquisition low priority and fall back to reading from the store (probably not a good idea).
• We could get rid of the high priority hack altogether with some work involving child transactions (see kv: child transactions #54633).

Expected behavior

The restore finishes.

[pbardea]

As an update, a reproduction was confirmed following the steps above. The command create table foo (a int primary key references bar(a)) was also able to get stuck on

cockroach/pkg/sql/schema_changer.go

Line 391 in c9dd3b1

return sc.txn(ctx, func(ctx context.Context, txn *kv.Txn, descsCol *descs.Collection) error {

while a distant node was being spammed with selects on foo.

[ajwerner]

The short term fix which we'll backport will be to lease descriptors in the Adding state. This isn't a panacea but I think it will fix the immediate problem.