Lock wait timeouts/delays for some concurrent update/insert operations involving lookup vindexes

[aquarapid]

Scenario:

• From current master (10706d3)
• Setup local examples with both sharded and unsharded keyspaces, e.g.:

$ cd examples/local/
$ for i in `ls -1 [1-3]*.sh` ; do echo $i ; ./$i ; sleep 5 ; done
$ vtctlclient -server localhost:15999 ListAllTablets
zone1-0000000100 commerce 0 master localhost:15100 localhost:17100 [] 2020-08-20T23:38:46Z
zone1-0000000101 commerce 0 replica localhost:15101 localhost:17101 [] <null>
zone1-0000000102 commerce 0 rdonly localhost:15102 localhost:17102 [] <null>
zone1-0000000300 customer -80 master localhost:15300 localhost:17300 [] 2020-08-20T23:41:33Z
zone1-0000000301 customer -80 replica localhost:15301 localhost:17301 [] <null>
zone1-0000000302 customer -80 rdonly localhost:15302 localhost:17302 [] <null>
zone1-0000000400 customer 80- master localhost:15400 localhost:17400 [] 2020-08-20T23:41:34Z
zone1-0000000401 customer 80- replica localhost:15401 localhost:17401 [] <null>
zone1-0000000402 customer 80- rdonly localhost:15402 localhost:17402 [] <null>

• Apply the following SQL schema for the unsharded keyspace (commerce):

$ cat schema_seq.sql 
CREATE TABLE t1_seq (
    id INT, next_id BIGINT, cache BIGINT, PRIMARY KEY(id)
) comment 'vitess_sequence';

INSERT INTO t1_seq (id, next_id, cache) values(0, 1, 1000);

$ vtctlclient -server localhost:15999 ApplySchema -sql "$(cat schema_seq.sql)" commerce

• Apply the following SQL schema for the sharded keyspace (customer):

$ cat schema.sql
CREATE TABLE t1 (
    c1 BIGINT NOT NULL,
    c2 BIGINT NOT NULL,
    c3 BIGINT,
    PRIMARY KEY (c1),
    UNIQUE KEY (c2),
    UNIQUE KEY (c3)
) ENGINE=Innodb;

CREATE TABLE lookup_t1 (
    c2 BIGINT NOT NULL,
    keyspace_id BINARY(8),
    primary key (c2)
);

CREATE TABLE lookup_t2 (
    c3 BIGINT NOT NULL,
    keyspace_id BINARY(8),
    primary key (c3)
);

$ vtctlclient -server localhost:15999 ApplySchema -sql "$(cat schema.sql)" customer

• Apply the VSchema for the sharded keyspace:

$ cat vschema.json 
{
    "sharded": true,
    "vindexes": {
        "xxhash": {
            "type": "xxhash"
        },
        "lookup_c2": {
            "type": "consistent_lookup_unique",
            "params": {
                "table": "lookup_t1",
                "from": "c2",
                "to": "keyspace_id",
                "ignore_nulls": "true"
            },
            "owner": "t1"
        },
        "lookup_c3": {
            "type": "consistent_lookup_unique",
            "params": {
                "table": "lookup_t2",
                "from": "c3",
                "to": "keyspace_id",
                "ignore_nulls": "true"
            },
            "owner": "t1"
        }
    },
    "tables": {
        "t1": {
            "columnVindexes": [
                {
                    "column": "c1",
                    "name": "xxhash"
                },
                {
                    "column": "c2",
                    "name": "lookup_c2"
                },
                {
                    "column": "c3",
                    "name": "lookup_c3"
                }
            ],
            "columns": [
                {
                    "name": "c1",
                    "type": "INT64"
                },
                {
                    "name": "c2",
                    "type": "INT64"
                },
                {
                    "name": "c3",
                    "type": "INT64"
                }
            ],
            "autoIncrement": {
                "column": "c1",
                "sequence": "commerce.t1_seq"
            },
            "columnListAuthoritative": true
        },
        "lookup_t1": {
            "columnVindexes": [
                {
                    "column": "c2",
                    "name": "xxhash"
                }
            ]
        },
        "lookup_t2": {
            "columnVindexes": [
                {
                    "column": "c3",
                    "name": "xxhash"
                }
            ]
        }
    }
}

$ vtctlclient -server localhost:15999  ApplyVSchema -vschema="$(cat ./vschema.json)" customer
.
.
.

• Now, open two different MySQL CLI sessions to vtgate via:

$ mysql  -u root -P 15306 -h 127.0.0.1 customer

• Make sure you have both sessions ready before continuing, you need to perform the following sequence in quick succession... In session 1, execute:

mysql> insert into t1 values (300,100,300);
Query OK, 1 row affected (0.04 sec)

mysql> start transaction ; UPDATE t1 SET c3 = 400 WHERE c2 = 100;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.02 sec)

• Now, quickly, execute in session 2 (this will hang):

mysql> insert into t1 values (400,100,400);

• Now, quickly, execute in session 1 (this will hang for a while, and then complete):

mysql> insert ignore into t1 values (200,100,200); commit;
Query OK, 0 rows affected (15.84 sec)

Query OK, 0 rows affected (0.05 sec)

• Note the 15+ seconds the second insert in session 1 took. It only completed when session 2's insert failed with a lock wait timeout:

ERROR 1205 (HY000): vtgate: http://localhost:15001/: execInsertSharded: getInsertShardedRoute: target: customer.80-.master, used tablet: zone1-400 (localhost): vttablet: Lock wait timeout exceeded; try restarting transaction (errno 1205) (sqlstate HY000) (CallerID: userData1): Sql: "select c2 from t1 where c2 = :c2 lock in share mode", BindVars: {#maxLimit: "type:INT64 value:\"10001\" "c2: "type:INT64 value:\"100\" "keyspace_id: "type:VARBINARY value:\"\\223y\\236,\\232\\3724\\232\" "}

• If perform the same sequence against MySQL directly (no lookup vindexes involved, of course), the behavior is:

Session 1 (note, no hang after the insert ignore):

mysql> insert into t1 values (300,100,300);
Query OK, 1 row affected (0.01 sec)

mysql> start transaction ; UPDATE t1 SET c3 = 400 WHERE c2 = 100;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert ignore into t1 values (200,100,200); commit; 
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Session 2 (fails with duplicate entry as look as the insert ignore runs):

mysql> insert into t1 values (400,100,400);
ERROR 1062 (23000): Duplicate entry '100' for key 't1.c2'

As some context, looking at the underlying MySQL instance and the InnoDB engine status output, the lock that Vitess seems to be waiting on after the insert ignore / commit is:

---TRANSACTION 2245, ACTIVE 10 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 185, OS thread handle 140718670382848, query id 760 localhost vt_app update
insert ignore into lookup_t1(c2, keyspace_id) values (100, '|̋ZTnC%')
Trx read view will not see trx with id >= 2244, sees < 2244
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 33 page no 4 n bits 72 index PRIMARY of table `vt_customer`.`lookup_t1` trx id 2245 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 8000000000000064; asc        d;;
 1: len 6; hex 0000000008bd; asc       ;;
 2: len 7; hex 82000001360110; asc     6  ;;
 3: len 8; hex b5cb9d7ddd20a421; asc    }   !;;

[harshit-gangal]

Re-open due to fix added in #6736

[harshit-gangal]

There are 3 session with VTGate open when consistent lookup is involved.

1. Pre Session
2. Normal Session
3. Post Session

Pre and Post session are used by lookup queries and normal session is used by the original query sent from the client to VTGate.

If an insert query is received, insert on Consistent lookup will happen through Pre session and actual query insert will happen through Normal Session, So, when commit happen it happens on Pre first and if it succeeds then commit happens on post session.

Similarly when an update/delete query is received, post session is used to do the update/delete on consistent lookup and normal session for original query. Commit order is normal session first if that succeeds then is executed on post session.

Anytime there is consistent lookup involved in the query received, a lock needs to be taken so that is not available for other session to be modified.

In order to do that we have to select the right session in the beginning.
For an insert query, PRE Session is used to send select... for update query.

For an update/delete query, POST Session is used to send select.. for update query.

Due to this the current limitation with consistent lookup is that it cannot support insert followed by update/delete or otherwise when the consistent lookup column has same value involved.