transactionNesting

Diagnostic Category: lint/safety/transactionNesting

Since: vnext

Sources: - Inspired from: squawk/transaction-nesting

Description

Detects problematic transaction nesting that could lead to unexpected behavior.

Transaction nesting issues occur when trying to start a transaction within an existing transaction, or trying to commit/rollback when not in a transaction. This can lead to unexpected behavior or errors in database migrations.

Examples

Invalid

BEGIN;
-- Migration tools already manage transactions
SELECT 1;

code-block.sql:1:1 lint/safety/transactionNesting ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ! Transaction already managed by migration tool.

  > 1 │ BEGIN;
      │ ^^^^^^
    2 │ -- Migration tools already manage transactions
    3 │ SELECT 1;

  i Migration tools manage transactions automatically. Remove explicit transaction control.

  i Put migration statements in separate files to have them be in separate transactions.

SELECT 1;
COMMIT; -- No transaction to commit

code-block.sql:2:1 lint/safety/transactionNesting ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ! Attempting to end transaction managed by migration tool.

    1 │ SELECT 1;
  > 2 │ COMMIT; -- No transaction to commit
      │ ^^^^^^^
    3 │ 

  i Migration tools manage transactions automatically. Remove explicit transaction control.

  i Put migration statements in separate files to have them be in separate transactions.

How to configure

{
  "linter": {
    "rules": {
      "safety": {
        "transactionNesting": "error"
      }
    }
  }
}