Understanding a TransactionRetryError with a foreign key

Hi,

I’m wondering about a case of concurrent transactions where one fails with a TransactionRetryError: retry txn (RETRY_SERIALIZABLE). I know how to handle these cases in application logic, but I would like to understand why it occurs in this situation, as it appears to me that the transactions should not be in conflict with each other.

CRDB version: 20.1.3

DDL:

CREATE TABLE IF NOT EXISTS accounts (id varchar(64) PRIMARY KEY, balance INT, self_id varchar(64));
ALTER TABLE accounts ADD CONSTRAINT accounts_fk FOREIGN KEY (self_id) REFERENCES accounts (id);

Setup:

INSERT INTO accounts (id, balance, self_id) VALUES (1, 1000, 1);
INSERT INTO accounts (id, balance, self_id) VALUES (2, 1000, 2);

TX 1:

TX begin
SELECT * FROM accounts WHERE self_id = '1'
DELETE FROM accounts WHERE id = '1'
commit

TX 2:

TX begin
SELECT * FROM accounts WHERE self_id = '2'
DELETE FROM accounts WHERE id = '2'
commit

Steps to reproduce:

  1. Run the transactions in parallel until before they submit the DELETE
  2. Let TX 1 run its DELETE statement and commit => success
  3. Let TX 2 run its DELETE statement and commit => commit fails with TransactionRetryError: retry txn (RETRY_SERIALIZABLE) (full error see below)

My understanding/assumptions:

  • Naively I would assume that the transactions are not in conflict because they read and modify separate data of the table :slight_smile:
  • It seems that the foreign key constraint is critical to reproduce this, and in particular the part of the DELETE statement that ensures the foreign key constraint is not violated
  • Generating query plans for the DELETE (see below), I observe two versions: initially, the planner uses a lookup-join on the foreign key index. After some time, it switches to a hash join that scans the entire index. From my observation, the problem only occurs with the hash join query plan.

My questions:

  • Is my understanding somewhat correct?
  • If yes, why does the hash join introduce a conflict between the transactions?

DELETE query plan with lookup-join:

|tree                                          |field         |description                                 |
|----------------------------------------------|--------------|--------------------------------------------|
|                                              |distributed   |false                                       |
|                                              |vectorized    |false                                       |
|root                                          |              |                                            |
| β”œβ”€β”€ count                                    |              |                                            |
| β”‚    └── delete                              |              |                                            |
| β”‚         β”‚                                  |from          |accounts                                    |
| β”‚         β”‚                                  |strategy      |deleter                                     |
| β”‚         └── buffer node                    |              |                                            |
| β”‚              β”‚                             |label         |buffer 1                                    |
| β”‚              └── scan                      |              |                                            |
| β”‚                                            |table         |accounts@primary                            |
| β”‚                                            |spans         |/"1"-/"1"/#                                 |
| └── postquery                                |              |                                            |
|      └── error if rows                       |              |                                            |
|           └── lookup-join                    |              |                                            |
|                β”‚                             |table         |accounts@accounts_auto_index_accounts_fk    |
|                β”‚                             |type          |semi                                        |
|                β”‚                             |equality      |(id) = (self_id)                            |
|                └── render                    |              |                                            |
|                     └── scan buffer node     |              |                                            |
|                                              |label         |buffer 1                                    |

DELETE query plan with hash join:

|tree                                          |field                |description                                |
|----------------------------------------------|---------------------|-------------------------------------------|
|                                              |distributed          |false                                      |
|                                              |vectorized           |false                                      |
|root                                          |                     |                                           |
| β”œβ”€β”€ count                                    |                     |                                           |
| β”‚    └── delete                              |                     |                                           |
| β”‚         β”‚                                  |from                 |accounts                                   |
| β”‚         β”‚                                  |strategy             |deleter                                    |
| β”‚         └── buffer node                    |                     |                                           |
| β”‚              β”‚                             |label                |buffer 1                                   |
| β”‚              └── scan                      |                     |                                           |
| β”‚                                            |table                |accounts@primary                           |
| β”‚                                            |spans                |/"1"-/"1"/#                                |
| └── postquery                                |                     |                                           |
|      └── error if rows                       |                     |                                           |
|           └── hash-join                      |                     |                                           |
|                β”‚                             |type                 |semi                                       |
|                β”‚                             |equality             |(id) = (self_id)                           |
|                β”‚                             |left cols are key    |                                           |
|                β”œβ”€β”€ render                    |                     |                                           |
|                β”‚    └── scan buffer node     |                     |                                           |
|                β”‚                             |label                |buffer 1                                   |
|                └── scan                      |                     |                                           |
|                                              |table                |accounts@accounts_auto_index_accounts_fk   |
|                                              |spans                |FULL SCAN                                  |

Full error message:

ERROR: restart transaction: TransactionRetryWithProtoRefreshError: TransactionRetryError: retry txn (RETRY_SERIALIZABLE): "sql txn" meta={id=175e0703 key=/Table/59/2/"1"/"1"/0 pri=0.00559818 epo=0 ts=1596027220.427040300,2 min=1596027150.719509900,0 seq=3} lock=true stat=PENDING rts=1596027150.719509900,0 wto=false max=1596027150.719509900,0

Thanks,
Thorben

Hi Thorben,

You’re right that the hash join plan causes the conflict. When the hash join fk check is planned, both transactions read the full table (I.e. the other key) and then delete a key the other txn read leading to a read/write conflict. This doesn’t happen with the lookup join plan, as the read write sets don’t actually overlap

Hi @vy-ton,

Thanks for your reponse. What influences the choice of the query plans in this case? To my untrained eye there seems to be no advantage in choosing the hash-join. Would it behave any different if the table had more data (as this would make a full scan more expensive I assume)?

Cheers,
Thorben

This is a bug, https://github.com/cockroachdb/cockroach/issues/51648, that is fixed in the 20.1.5 release

1 Like