sql/opt: add implicit SELECT FOR SHARE support for FK checks #80683
Description
Similar to #50180 and #50181 we should add implicit SELECT FOR UPDATE locking to the FK checks (and other constraint checks) performed by mutation statements, in accordance with the enable_implicit_select_for_update session variable.
Here's a demonstration:
CREATE TABLE account (id INT PRIMARY KEY, name STRING);
CREATE TABLE transfer (
from_id INT,
to_id INT,
amount INT,
FOREIGN KEY (from_id) REFERENCES account (id),
FOREIGN KEY (to_id) REFERENCES account (id)
);
INSERT INTO account VALUES (1, 'Alice'), (2, 'Bob');
-- connection 1 (single explicit transaction)
BEGIN;
SELECT * FROM transfer FOR UPDATE;
-- connection 2 (two implicit transactions)
UPDATE account SET name = 'Carol' WHERE id = 2;
SELECT * FROM transfer;
-- connection 1 (resuming open transaction)
INSERT INTO transfer VALUES (1, 2, 100);
COMMIT;The FK checks performed by the INSERT do not use SELECT FOR UPDATE and so are at a timestamp before the update transaction in connection 2, and hence impossible to refresh.
demo@127.0.0.1:26257/defaultdb OPEN> COMMIT;
ERROR: restart transaction: TransactionRetryWithProtoRefreshError: TransactionRetryError: retry txn (RETRY_SERIALIZABLE - failed preemptive refresh due to a conflict: committed value on key /Table/104/1/2/0): "sql txn" meta={id=bc8069e8 key=/Table/105/1 pri=0.00422485 epo=0 ts=1651120066.267562000,2 min=1651120005.931353000,0 seq=1} lock=true stat=PENDING rts=1651120005.931353000,0 wto=false gul=1651120006.431353000,0
SQLSTATE: 40001
HINT: See: https://www.cockroachlabs.com/docs/v22.1/transaction-retry-error-reference.html#retry_serializable
Jira issue: CRDB-15550
Epic CRDB-25322
Metadata
Metadata
Assignees
Labels
Type
Projects
Status