search

camunda / camunda-bpm-platform

Flexible framework for workflow and decision automation with BPMN and DMN. Integration with Quarkus, Spring, Spring Boot, CDI.
https://camunda.com/
Apache License 2.0
4.11k stars 1.55k forks source link

CRDB Concurrency conflicts on unrelated data from the same table #2402

Open ThorbenLindhauer opened 4 years ago

ThorbenLindhauer commented 4 years ago

This issue was imported from JIRA:

Field Value
JIRA Link CAM-12480
Reporter @koevskinikola
Has restricted visibility comments true

// Given Two process instances in the execution table

// When The same operation is applied on each of the instances so that the row in the execution table is deleted (maybe it can be reproduced with updates as well). Example: Job execution that finishes the process instance.

// Then A concurrency error is reported by CockroachDB that leads to a CrdbTransactionRetryException in the process engine

// Expected No concurrency error is reported since the data is not related and the transactions are in any case serializable.

Understanding: CockroachDB needs to ensure that the foreign key constraint is not violated after the DELETE. It does that by performing a hash join on the foreign key index with a full scan after the delete was performed (see query plans in linked CRDB forum post).

Potentially related CockroachDB issue: https://github.com/cockroachdb/cockroach/issues/51648 (resolved in CRDB 20.1.5) Forum post: https://forum.cockroachlabs.com/t/understanding-a-transactionretryerror-with-a-foreign-key/3895

Links:

ThorbenLindhauer commented 4 years ago

This comment was imported from JIRA and written by user @ThorbenLindhauer

I wouldn't bet on that the github issue addresses our problem. I could still reproduce the original problem with CockroachDB 20.1.5 (both in Cockpit when executing jobs as well as with an artificial example) and the query plan still seems to be the same. See https://forum.cockroachlabs.com/t/understanding-a-transactionretryerror-with-a-foreign-key/3895 for details.

ThorbenLindhauer commented 4 years ago

This comment was imported from JIRA and written by user @ThorbenLindhauer

It's also not necessarily restricted to constraints in the same table.

ThorbenLindhauer commented 4 years ago

This comment was imported from JIRA and written by user @ThorbenLindhauer

According to https://forum.cockroachlabs.com/t/understanding-a-transactionretryerror-with-a-foreign-key/3895/6?u=thorben and https://github.com/cockroachdb/cockroach/issues/41701, in 20.1.5 the optimizer still uses the problematic hash join when the table has less than five rows. With 20.2, it is supposed to only do that only when the table has a single row.