Database Cursors — When Unclosed Cursors Drain Your Pool

Most SQL you write is declarative — you describe the shape of the data you want, and the database engine figures out how to get it in one shot. That works beautifully for 95% of use cases. But sometimes you genuinely need to process rows one at a time, carry state between rows, or perform logic that depends on the result of the previous row before you can compute the next one. That's the moment cursors enter the room, and if you don't know how they actually work, they'll silently destroy your application's performance at scale.

Cursors exist to solve a fundamental mismatch: relational databases think in sets, but procedural code — and many real business problems — think in sequences. Consider calculating a running bank balance, applying tiered commission rules where each row's rate depends on the cumulative total so far, or processing a feed of CDC events in strict order. These problems resist pure set-based solutions. Cursors provide a controlled, stateful way to walk through a result set row by row while the database engine manages memory, locking, and position tracking behind the scenes.

By the end of this article you'll understand exactly what happens inside the database engine when you declare a cursor, how the different cursor types (static, keyset, dynamic, forward-only) affect locks, memory, and consistency, how to write production-safe cursor code in both T-SQL and PL/pgSQL, and — critically — how to recognize when a cursor is the wrong tool and what to replace it with. You'll also walk away knowing the three cursor mistakes that silently corrupt data or cripple query performance in prod.

What Is a Database Cursor?

A database cursor is a pointer to a result set that allows you to fetch rows sequentially. The database engine creates a working table (often in tempdb or a temporary segment) that holds the current position and, depending on the cursor type, may store the entire result set or just a keyset.

You can think of it as an iterator over a query result. But unlike a typical programming language iterator, the cursor lives on the database server side and its lifecycle is managed through explicit DECLARE, OPEN, FETCH, CLOSE, and DEALLOCATE commands.

Cursors come in four main flavors, each with different trade-offs between consistency, memory, and freshness:

• Static cursor: Takes a snapshot of the result set at open time. No updates from other sessions are visible. Entire result set is materialized into a temporary table. Safe and consistent, but expensive on large datasets.
• Keyset cursor: Materializes only the unique row identifiers (keys) and fetches each row on demand from the underlying tables. Sees updates to non-key columns but not inserts/deletes from other sessions. Cheaper than static when row size is large.
• Dynamic cursor: Sees all changes (inserts, updates, deletes) made by other sessions after the cursor is opened. No materialization — just a pointer to the actual rows. Most expensive in terms of lock contention and server overhead.
• Forward-only cursor: Can only move forward through the result set. Default in most databases. Does not support SCROLL or FETCH PRIOR.

-- PL/SQL block nested in package IO_THECODEFORGE.CURSOR_DEMO
DECLARE
  CURSOR emp_cursor IS
    SELECT employee_id, salary FROM employees WHERE department_id = 50;
  v_emp_id   employees.employee_id%TYPE;
  v_salary   employees.salary%TYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO v_emp_id, v_salary;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee ' || v_emp_id || ' earns ' || v_salary);
  END LOOP;
  CLOSE emp_cursor;
END;
/

Cursor Internals: What Happens When You OPEN a Cursor

When the database executes OPEN cursor_name, a series of operations happen that many developers never consider:

1. The SQL associated with the cursor is parsed and optimized again (unless it's already in the shared pool).
2. Depending on the cursor type:
3. - Static: The database performs a full table or index scan according to the query plan and writes the result rows into a temporary segment (e.g., Oracle's temp tablespace, PostgreSQL's temp_buffers).
4. - Keyset: Only the unique key columns of the result set are stored in a temporary structure. At fetch time, each row is looked up by its key from the actual table.
5. - Dynamic: No materialization at all. The cursor simply holds a bookmark pointing to the next row based on the current state of the underlying tables.
6. The database sets up a cursor context in the session memory, which includes the current position, the statement handle, and any locks acquired.

This initialization cost is typically in the range of 0.1–1 ms per cursor open on modern hardware, but it can spike to seconds if the query is complex or the temp tablespace is slow.

-- Simulate cursor open overhead with different types
SET TIMING ON;

-- Static cursor (materializes full result)
DECLARE
  CURSOR c1 IS SELECT * FROM large_table;
  r large_table%ROWTYPE;
BEGIN
  OPEN c1;
  FETCH c1 INTO r;
  CLOSE c1;
END;
/

-- Forward-only cursor (no temp table)
DECLARE
  CURSOR c2 IS SELECT /*+ PARALLEL */ * FROM large_table;
  r large_table%ROWTYPE;
BEGIN
  OPEN c2;
  FETCH c2 INTO r;
  CLOSE c2;
END;
/

Row-by-Row vs Bulk Fetch: The Hidden Performance Trap

The biggest performance killer in cursor-based code is the overhead of fetching one row at a time inside a loop, especially when each fetch triggers a round-trip between the application and the database. Even with server-side cursors, each FETCH NEXT incurs context switching and cursor maintenance overhead.

Bulk fetching reduces the number of round trips from \(N\) (one per row) to \(N / batch_size\). The recommended batch size is typically between 100 and 1000, depending on column width and network latency.

DECLARE
  TYPE emp_tab_type IS TABLE OF employees%ROWTYPE;
  emp_tab emp_tab_type;
  CURSOR emp_cur IS
    SELECT employee_id, salary FROM employees WHERE department_id = 50;
  k_batch CONSTANT PLS_INTEGER := 100;
BEGIN
  OPEN emp_cur;
  LOOP
    FETCH emp_cur BULK COLLECT INTO emp_tab LIMIT k_batch;
    FOR i IN 1..emp_tab.COUNT LOOP
      -- Process each row (stateful logic here)
      DBMS_OUTPUT.PUT_LINE(emp_tab(i).employee_id || ' => ' || emp_tab(i).salary);
    END LOOP;
    EXIT WHEN emp_tab.COUNT < k_batch;
  END LOOP;
  CLOSE emp_cur;
END;
/

When Cursors Hurt: The Three Anti-Patterns to Avoid

Most cursor overhead in production comes from using them where a set-based solution would do the job better. Here are three concrete anti-patterns that silently degrade performance:

Anti-pattern 1: Running aggregate computations inside a cursor loop. Example: iterating over orders to calculate a running total when a window function SUM(amount) OVER (ORDER BY order_date) would do it in one pass.

Anti-pattern 2: Updating the same table you are reading from with a cursor. This often leads to deadlocks or consistent-read violations. Prefer MERGE or multi-table UPDATE with subqueries.

Anti-pattern 3: Cursors inside triggers. Row-level triggers already operate row-by-row. Adding a cursor inside a trigger multiplies context switching and can cause mutating-table errors in Oracle.

The common root: developers fall back to cursor-based thinking because it resembles the procedural loops they know from traditional programming languages.

-- Anti-pattern 1: Cursor with running total (slow)
DECLARE
  CURSOR c1 IS SELECT order_id, amount FROM orders ORDER BY order_date;
  v_running NUMBER := 0;
  v_ord orders%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO v_ord;
    EXIT WHEN c1%NOTFOUND;
    v_running := v_running + v_ord.amount;
    DBMS_OUTPUT.PUT_LINE(v_ord.order_id || ' total: ' || v_running);
  END LOOP;
  CLOSE c1;
END;
/

-- Better: set-based with window function
SELECT order_id, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Production-Safe Cursor Patterns in PL/SQL and PL/pgSQL

When a cursor is justified (e.g., row-by-row business logic with state that cannot be expressed in SQL), you need to follow production patterns that prevent leaks and performance degradation.

Pattern 1: Parameterized cursors Always pass filter values as bind variables, not string concatenation. This avoids SQL injection and allows cursor reuse.

Pattern 2: Limit batch size with BULK COLLECT ... LIMIT As discussed, this is non-optional for any cursor processing thousands of rows.

Pattern 3: Always close and deallocate In PL/SQL, use a dedicated CLOSE statement. In some databases (e.g., PostgreSQL), a cursor is automatically closed at end of transaction, but it's safer to CLOSE explicitly.

Pattern 4: Use FOR UPDATE cursor only when needed If you need to update the current row, declare a cursor with FOR UPDATE OF column to lock only the relevant row. Avoid FOR UPDATE on read-only cursors.

Pattern 5: Prefer implicit cursors when possible Implicit cursors (like SELECT INTO or FOR rec IN (query) LOOP) are managed automatically and often more efficient because they use bulk collect internally.

CREATE OR REPLACE PACKAGE BODY io_thecodeforge.cursor_safe AS

  PROCEDURE process_commissions(p_dept_id IN NUMBER) IS
    CURSOR emp_cur(p_dept NUMBER) IS
      SELECT employee_id, salary FROM employees WHERE department_id = p_dept
      FOR UPDATE OF commission_pct; -- only lock necessary column
    TYPE emp_tab_type IS TABLE OF emp_cur%ROWTYPE;
    emp_tab emp_tab_type;
    k_batch CONSTANT PLS_INTEGER := 500;
  BEGIN
    OPEN emp_cur(p_dept_id);
    LOOP
      FETCH emp_cur BULK COLLECT INTO emp_tab LIMIT k_batch;
      FOR i IN 1..emp_tab.COUNT LOOP
        -- complex commission calculation based on cumulative thresholds
        UPDATE employees
        SET commission_pct = compute_commission(emp_tab(i).salary,
                                                  i * k_batch)
        WHERE CURRENT OF emp_cur;
      END LOOP;
      EXIT WHEN emp_tab.COUNT < k_batch;
    END LOOP;
    CLOSE emp_cur;
  END;

END;
/