kv: intent resolution for high priority pushes should not wait for batching

[kocoten1992]

Describe the problem

When select for update a row, other query running into it will significantly increase cost, example below:

To Reproduce

1. start fresh ./cockroach start-single-node --insecure

CREATE TABLE test_table (
  id uuid NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO test_table (id) values ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa');
INSERT INTO test_table (id) values ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb');

3. open two cockroach shells:

# shell 1
begin;
root@:26257/test_database  OPEN> select * from test_table where id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa' for update;
                   id
----------------------------------------
  aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
(1 row)

Time: 766.309µs

# shell 2
begin priority high;
root@:26257/default  OPEN> select * from test_table where id = 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb' for update;
                   id
----------------------------------------
  bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb
(1 row)

Time: 1.265911ms

root@:26257/default  OPEN> select * from test_table where id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa' for update;
                   id
----------------------------------------
  aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
(1 row)

Time: 14.729765ms

Notice that, the select time are significantly higher.

I don't know how to get around this ? Maybe we could have a select priority read and skip all of the checking

Because this make our select for update kinda work - but does not truly.

Environment:

• CockroachDB version: v20.1.1

P/s: another example

# shell 1
begin priority low;
root@:26257/default  OPEN> select * from test_table where id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa' for update;
                   id
----------------------------------------
  aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
(1 row)

Time: 1.081118ms

# shell 2
# no need transaction here
root@:26257/default> select * from test_table where id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';           
                   id
----------------------------------------
  aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
(1 row)

Time: 19.77373ms

Jira issue: CRDB-4129

Epic CRDB-25218

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @ricardocrdb (member of the technical support engineering team)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[yuzefovich]

cc @nvanbenschoten

[kocoten1992]

I forgot to mention, begin read only; does not help, maybe it make sense to read only ?

[riking]

This looks like the intended behavior of SELECT .. FOR UPDATE? The entire point of the statement is to take a write lock on the row, because you're about to update it. If other reads didn't wait for you to finish, then they'd have aborted transactions.

And yes, SELECT .. FOR UPDATE should be rejected during a read-only transaction.

[kocoten1992]

For some peculiar flow control, what you really want is really both select .. for update and normal select working together.

With postgresql, select .. for update (and even select .. for share) don't interfere with normal select (we could achieve maximum query throughput with normal select).

With cockroachdb, our select .. for update does interfere with normal select, but instead we have something better than postgres, priority transaction. Unfortunately, as current state, normal select would be affected in performance.

It's all about query throughput, I hope I made myself clear.

[riking]

Right, so the delay is because the read only client needs to find the transaction record of the FOR UPDATE client, see that it hasn't committed yet, bump the transaction time of the other transaction so it will happen after the read, and return.

I think this means that the SELECT FOR UPDATE is causing read only transactions to perform a write.

The good news is that any other concurrent reads for the same key should be satisfied by the same timestamp bump?

[kocoten1992]

The good news is that any other concurrent reads for the same key should be satisfied by the same timestamp bump?

Are you sure about that ? @riking

Here is my testing, I open 3 cockroach client:
shell1. begin with normal priority, select for update
shell2. begin high priority, hiccup
shell3. begin high priority, still hiccup

If we are talking about calling the same select on the same transaction, that is not what I am after.

[nvanbenschoten]

Hi @kocoten1992, thanks for the report and the reproduction steps. Like @riking mentioned, the delay is partly because the high priority transaction needs to perform a write to the other transaction's record. In my testing on my Mac, this was responsible for about 8ms. Do you mind measuring the duration of an INSERT statement in your test environment? As an aside, this 8ms was slower than I was expecting until I was reminded of golang/go#26650.

In practice, you will only hit this delay if the first transaction has heartbeat its transaction record, which begins after the transaction has been alive for 1s (see DefaultTxnHeartbeatInterval). If not, the push is a fully in-memory operation which does not require a disk write and should take less than 1ms. Are you holding transactions open for longer than this in your application?

The other ~5m appears to be coming from a delay in during intent resolution. CockroachDB attempts to batch intent resolution across multiple transactions, which is effective in avoiding redundant pushes and reducing the aggregate number of disk writes due to contention handling. In practice, this is a latency/throughput trade-off. However, I think there's a strong argument that high-priority transactions should not wait for this delay. So we should be able to remove this remaining 5ms. Do you mind if I repurpose this issue to track that change?

[kocoten1992]

I don't mind at all, and thank you!

p/s: my testing method was wrong (I open cockroachdb client and let transaction live for longer than 1s), thanks for letting me know.

p/s: hi, what about transaction select read only, should we skip everything, read immediately without checking row lock ?