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.