sql: transactions that enter the "aborted" state due to SQL-level errors should relinquish locks

[mgartner]

Since 719b702, which added support for savepoints, SQL-level errors that cause a transaction to enter the "aborted" state do not cause the transaction to end or rollback. As a result, any locks acquired in the transaction prior to the error remain held, blocking concurrent queries.

Here's an example:

CREATE TABLE t (k INT PRIMARY KEY, v INT);
-- CREATE

INSERT INTO t VALUES (1, 0);
-- INSERT 1

-- Session 1
BEGIN;
-- BEGIN

-- Session 1
UPDATE t SET v = 10 WHERE k = 1;
-- UPDATE 1

-- Session 1
SELECT 1/0;
-- ERROR: division by zero
-- SQLSTATE: 22012

-- Session 2
UPDATE t SET v = 100 WHERE k = 1;
-- Blocked by the "aborted" txn above.

-- Session 1
SELECT 1;
-- ERROR: current transaction is aborted, commands ignored until end of transaction block
-- SQLSTATE: 25P02

Once the transaction enters the "aborted" state, I believe it can never successfully commit, so holding the locks has no benefit and increases the chance of contention. This may only be true for transactions without savepoints—we should think through all possible cases here.

This creates some major rough edges with the transaction_timeout session setting. The docs state about this setting:

Aborts an explicit transaction when it runs longer than the configured duration. Stored in milliseconds; can be expressed in milliseconds or as an INTERVAL.

The name of the setting and this description make it sound like transaction_timeout is the perfect safeguard against a misbehaving application that fails to commit or rollback a transaction. However, due to the behavior described above, it actually provides no additional safety—while the transaction will be automatically aborted, any locks acquired will remain held and will not be released until the connection is closed.

Here's an example:

CREATE TABLE t (k INT PRIMARY KEY, v INT);
-- CREATE

-- Session 1
SET transaction_timeout = '8s';
-- SET

-- Session 1
BEGIN;
-- BEGIN

-- Session 1
UPDATE t SET v = 33 WHERE k =3;
-- UPDATE 1

-- Session 2
UPDATE t SET v = 333 WHERE k =3;
-- Hangs until the transaction in Session 1 is manually COMMITed or ABORTed.

-- Session 1
SELECT pg_sleep(100);
-- ERROR: query execution canceled due to transaction timeout
-- SQLSTATE: 57014

-- Session 1
SELECT 1;
-- ERROR: current transaction is aborted, commands ignored until end of transaction block

Jira issue: CRDB-47066

[blathers-crl]

This issue has multiple T-eam labels. Please make sure it only has one, or else issue synchronization will not work correctly.

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

[blathers-crl]

This issue has multiple T-eam labels. Please make sure it only has one, or else issue synchronization will not work correctly.

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

[mgartner]

#140160 is a prototype of a fix for this.

See this internal Slack thread for more discussion: https://cockroachlabs.slack.com/archives/C0KB9Q03D/p1738249767226979

[mgartner]

Related to cockroachdb/docs#19330.