Use Stored Procedures in CockroachDB (2026)

CockroachDB’s stored procedures aren’t just SQL functions; they’re stateful transactions that can hold locks across multiple statements, making them a powerful tool for complex, atomic operations.

Let’s see one in action. Imagine we need to transfer funds between two accounts. This isn’t a single UPDATE; it’s a sequence of operations that must succeed or fail together.

-- Create the accounts
CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    balance DECIMAL(10, 2) NOT NULL
);

INSERT INTO accounts (account_id, balance) VALUES (101, 1000.00), (102, 500.00);

-- Create the stored procedure
CREATE OR REPLACE PROCEDURE transfer_funds(
    from_account_id INT,
    to_account_id INT,
    amount DECIMAL(10, 2)
)
LANGUAGE SQL
AS $$
DECLARE
    current_balance DECIMAL(10, 2);
BEGIN
    -- Check sender's balance
    SELECT balance INTO current_balance FROM accounts WHERE account_id = from_account_id FOR UPDATE;

    IF current_balance < amount THEN
        RAISE EXCEPTION 'Insufficient funds in account %', from_account_id;
    END IF;

    -- Debit sender
    UPDATE accounts
    SET balance = balance - amount
    WHERE account_id = from_account_id;

    -- Credit receiver
    UPDATE accounts
    SET balance = balance + amount
    WHERE account_id = to_account_id;

    -- Log the transaction (optional, but good practice)
    -- INSERT INTO transaction_log (from_account, to_account, amount, timestamp)
    -- VALUES (from_account_id, to_account_id, amount, NOW());

END;
$$;

-- Execute the procedure
CALL transfer_funds(101, 102, 200.00);

-- Verify the balances
SELECT * FROM accounts ORDER BY account_id;

This transfer_funds procedure encapsulates a business logic that requires atomicity. When CALL transfer_funds(101, 102, 200.00); is executed, CockroachDB starts a transaction. The FOR UPDATE clause on the SELECT statement is crucial here: it acquires row-level locks on the from_account_id row, preventing other transactions from modifying it until the procedure (and thus the transaction) completes. If the balance is insufficient, the RAISE EXCEPTION command aborts the transaction. Otherwise, the UPDATE statements debit and credit the accounts. If either UPDATE fails for any reason (e.g., network interruption, another constraint violation), the entire transaction is rolled back, ensuring that no money is lost or created out of thin air.

The core problem stored procedures solve is managing complex, multi-step operations that must maintain data integrity. Without them, you’d have to orchestrate these steps in your application code, which is error-prone. You’d need to manage transaction start/commit/rollback logic, acquire necessary locks, and handle potential failures at each step, all while ensuring consistency across potentially distributed nodes. Stored procedures push this logic down to the database, where it can be managed more reliably and efficiently. The LANGUAGE SQL part means it’s written using standard SQL and CockroachDB’s procedural extensions.

The FOR UPDATE clause is key. In a distributed system like CockroachDB, simply reading a balance and then updating it is a race condition waiting to happen. Another transaction could modify the balance between your SELECT and UPDATE. By using FOR UPDATE, you’re telling CockroachDB, "I need to read this row, and I intend to modify it. Please lock it for me until I’m done with this transaction, so no one else can sneak in and change it out from under me." This guarantees that the balance check and the subsequent debit/credit operations are performed on a consistent, uncontested view of the data.

One aspect that catches people off guard is how stored procedures interact with CockroachDB’s transaction retries. If a stored procedure encounters a retryable error (like a 40001 serialization error due to contention), CockroachDB will automatically attempt to re-execute the entire procedure from the beginning. This is usually a good thing, as it can resolve transient conflicts without application intervention. However, it means that any side effects outside the atomic transaction boundaries of the procedure (e.g., logging to a separate table after the procedure’s END statement, or making an external API call) might be executed multiple times if the procedure is retried. Therefore, it’s best to keep the procedure’s logic purely within the scope of the transactional operations it performs.

Understanding how FOR UPDATE and automatic transaction retries interact is crucial for writing robust stored procedures in CockroachDB.