I think this should be implemented in steps. How about defining the first step as follows:

If
1. this is a DML statement (which does Locking Reads in InnODB)
2. and the query is doing a full table scan without row filtering
then:
3. Use a table-level lock.

InnoDB already knows about whether #1 is true.
The only issue is #2.

#2 can be implemented as follows:
before (or right after) the handler->rnd_init(scan=false) call, the SQL layer will make an extra call to inform the InnoDB storage engine what condition will be attached to the table.

(TODO: check if this can be unified with Table Condition Pushdown).

Implementation suggestion is below. Need to think this through.

== Use Table Condition Pushdown for WHERE ==

There is already handler::cond_push() call which informs the storage engine
about the condition. InnoDB could implement it and this way see whether
a condition is being present.

Possible issues:
Issue 1:
setting optimizer_switch='table_condition_pushdown=off' will cause
table-level locks to be acquired! Is this ok?
It is a common practice among support/etc to disable optimizer switch
flags to see what happens...

Issue 2:
InnoDB will take a note about whether there is a pushed condition, but it
will not check the condition.

That is, we'll have:

  COND *ha_innobase::cond_push(COND *arg) {

     ...

     return arg;

  }

what should be in handler::pushed_cond, then? The code checks this member and seeing NULL interprets it as "nothing is pushed". Is this ok? If it is not, should we put "Item(1)" there?

== Introduce push_limit ==

To inform the storage engine about the LIMIT clause, we introduce

void handler::push_limit(ha_rows soft_limit)

Which serves as a hint to the storage engine that the SQL layer is not going to read more than soft_limit rows in a scan.

The idea is that this is generally useful for storage engines that do batching - they should not batch much more than soft_limit rows.

Marko informed me that this is the same bug:

CREATE TABLE IF NOT EXISTS v0 ( v1 VARCHAR ( 82 ) ) ;

INSERT INTO v0 ( v1 ) VALUES ( 25 ) , ( ( SELECT -1 WHERE ( v1 NOT IN ( v1 NOT IN ( SELECT v1 ) ) AND v1 NOT IN ( 80 ) ) ) ) , ( 89215105.000000 ) , ( 0 ) , ( 2147483647 ) , ( 127 ) , ( 74 ) , ( -128 ) ;

INSERT INTO v0 ( v1 ) SELECT v1 FROM ( SELECT 'x' FROM v0 AS v10 , v0 AS v9 NATURAL JOIN v0 AS v8 , v0 ) AS v2 , ( SELECT 40561621.000000 FROM v0 AS v7 , v0 AS v6 , v0 AS v5 JOIN v0 ) AS v3 JOIN v0 AS v4 NATURAL JOIN v0 ORDER BY v1 ;

Leads to:

10.11.0-opt>CREATE TABLE IF NOT EXISTS v0 ( v1 VARCHAR ( 82 ) ) ;

Query OK, 0 rows affected (0.015 sec)

10.11.0-opt> INSERT INTO v0 ( v1 ) VALUES ( 25 ) , ( ( SELECT -1 WHERE ( v1 NOT IN ( v1 NOT IN ( SELECT v1 ) ) AND v1 NOT IN ( 80 ) ) ) ) , ( 89215105.000000 ) , ( 0 ) , ( 2147483647 ) , ( 127 ) , ( 74 ) , ( -128 ) ;

Query OK, 8 rows affected (0.002 sec)

Records: 8  Duplicates: 0  Warnings: 0

10.11.0-opt> INSERT INTO v0 ( v1 ) SELECT v1 FROM ( SELECT 'x' FROM v0 AS v10 , v0 AS v9 NATURAL JOIN v0 AS v8 , v0 ) AS v2 , ( SELECT 40561621.000000 FROM v0 AS v7 , v0 AS v6 , v0 AS v5 JOIN v0 ) AS v3 JOIN v0 AS v4 NATURAL JOIN v0 ORDER BY v1 ;

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

And

2022-08-30  8:43:42 0 [Note] /test/MD190822-mariadb-10.11.0-linux-x86_64-opt/bin/mysqld: ready for connections.

Version: '10.11.0-MariaDB'  socket: '/test/MD190822-mariadb-10.11.0-linux-x86_64-opt/socket.sock'  port: 10942  MariaDB Server

2022-08-30  8:44:35 4 [Warning] InnoDB: Over 67 percent of the buffer pool is occupied by lock heaps or the adaptive hash index! Check that your transactions do not set too many row locks. innodb_buffer_pool_size=126M. Starting the InnoDB Monitor to print diagnostics.

=====================================

2022-08-30 08:44:35 0x1466fd360700 INNODB MONITOR OUTPUT

...

If it would help, I can reduce this testcase further.

May be InnoDB should automatically upgrade row locks to a table lock, when there're too many of them?
This is a fairly standard behavior of many database engines.