Feature Request: option to optimize locking for lookup Vindexes

[maxenglander]

Description

DML statements involving lookup vindexes perform locking reads on rows in the lookup backing table. It would be nice if there were an option to optionally skip or relax this lock for cases where we are confident that the business logic will not cause those rows to be deleted or changed.

Details

When a lookup vindex is used in a query, a SELECT lookup query is sent to the backing table that looks like this:

SELECT [from_column], [to] FROM [backing_table] WHERE [from_column] IN (?)

When the query that triggered the lookup query is a DML statement occurring inside a transaction, the lookup query is turned into a locking read:

SELECT [from_column], [to] FROM [backing_table] WHERE [from_column] IN (?) FOR UPDATE

The locking read is was added as a way to prevent concurrent DML causing locking/hanging in some scenarios.

Use cases

This locking read is a great safety feature that comes with performance cost. If a Vitess user is sure that these sorts of concurrent DML scenarios (updates together with inserts in transactions) won't occur on the backing table, then that user could take advantage of this feature to gain some extra performance.