Additional blocking is needed to realize serializability in TiDB's pessimistic isolation levels

## Feature Request

I'd like a locking read to be blocked by a concurrent insertion in your Read-Committed and Repeatable-Read isolation levels, so that I can mount a serializability implementation on top of them at second tier. For example, consider creating a table t with two columns: id, value. And we insert two rows (1, 1) and (3, 3) into it. Then we execute statements in two transactions T1 and T2 in the following specific order in two prompts under either isolation level:

T1 T2

start transaction;

insert into t values (2,2);

start transaction;

select * from t where id>0 and id<4 for update;

I expect T1's locking read would block for the write lock T2 is holding. But instead in TiDB, T1 doesn't hang.

Notice it doesn't require gap lock to realize this feature. In NDB Cluster, this locking read acquires a short write lock(released after execution of the statement) for each scanned row, and only escalates it to be a long write locks if it is a match(in this case, long locks will be held on (1, 1) and (3, 3)). But a short write lock on (2, 2) requested by T1 will block for T2's and give us the desired behavior. This is a key feature I've used to realize serializability in NDB Cluster's Read-Committed isolation level at second tier.

Right now, I can only mount a serializability implementation to TiDB's Snapshot Isolation at second tier. This missing feature is the only missing piece of the puzzle that would enable me to achieve the same goal at the other two isolation levels for TiDB. Please refer to the section for TiDB in Appendix D in my work for more information: https://github.com/creamyfish/conflict_serializability

Sincerely
Alex Ou

No implementation files or tests are identified in the issue. Start by reproducing the two-transaction sequence under Read Committed and Repeatable Read, then trace the locking-read behavior; done means the locking read blocks on the concurrent insertion as described, with coverage for both isolation levels.

Written by the indexing model from the issue text.