Getting ChatGPT to Write Accurate Database Transaction Logic Without Phantom Reads

You ask ChatGPT to write a database transaction for a financial transfer or an inventory reservation. The code looks clean, the syntax is correct, and it wraps everything in a BEGIN/COMMIT block. Then a week after deployment, you start seeing balance discrepancies that only appear under load.

The problem is almost always isolation level. ChatGPT defaults to whatever feels idiomatic for the language or ORM in context, which is usually READ COMMITTED. That's fine for simple reads, but it leaves phantom reads wide open in any transaction that reads a range of rows and then acts on that range.

What you'll learn

• Why phantom reads slip through the code ChatGPT generates by default
• How to specify isolation level requirements in your prompts so ChatGPT picks the right one
• How to prompt for correct pessimistic and optimistic locking strategies
• How to get ChatGPT to include retry logic that actually handles serialization failures
• The most common review mistakes engineers make when accepting ChatGPT transaction code

Prerequisites

This article assumes you know what a database transaction is and have worked with at least one relational database (PostgreSQL or MySQL). You don't need to be a concurrency expert, but you should be comfortable reading SQL and basic Python or JavaScript. Examples use PostgreSQL and Python with psycopg2, but the prompting patterns apply to any stack.

What phantom reads actually are (and why they're sneaky)

A phantom read happens when a transaction reads a set of rows matching some condition, another transaction inserts or deletes rows matching that same condition, and the first transaction re-reads and gets a different set. The classic example is seat booking: you read "there are 3 seats available," another session books one, and when you go to insert the reservation you've now over-committed.

What makes phantoms worse than dirty reads is that no single row is corrupted. Each individual write is valid in isolation. The bug lives in the gap between two reads inside the same transaction — which is exactly why it rarely shows up in unit tests and almost always shows up under concurrent load.

The SQL standard defines four isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Only SERIALIZABLE prevents phantoms fully. REPEATABLE READ prevents re-reading changed rows but in most databases (notably MySQL InnoDB) does not prevent phantom inserts unless you add explicit gap locks.

How ChatGPT typically gets transaction isolation wrong

Without explicit instruction, ChatGPT tends to produce transaction code in one of two shapes. The first is no isolation level at all — it just uses the database default, which on PostgreSQL is READ COMMITTED and on MySQL is REPEATABLE READ. Neither prevents phantoms.

The second shape is worse: ChatGPT sometimes adds SERIALIZABLE as a comment annotation or ORM keyword but forgets to add the serialization failure retry loop. A transaction at SERIALIZABLE isolation can and will be rolled back by the database with a 40001 serialization failure error when a conflict is detected. Code that doesn't catch and retry that error will simply crash or surface a 500 to the user.

This is the same category of gap that appears in distributed locking, where assuming the happy path is safe enough leads to subtle bugs under contention. If you've read about how ChatGPT mishandles distributed lock logic, the pattern will feel familiar: the model produces syntactically correct code that ignores the failure modes the infrastructure can actually raise.

Giving ChatGPT the isolation level it needs

The single highest-impact change you can make to your prompt is stating the isolation level explicitly and explaining why. ChatGPT responds well to constraint-first prompting — if you tell it the requirement before you describe the task, it reasons toward a solution that satisfies the constraint rather than retrofitting one afterward.

Bad prompt:

Write a Python function that transfers money between two accounts using PostgreSQL and psycopg2.

Better prompt:

Write a Python function that transfers money between two accounts using PostgreSQL and psycopg2. The transaction must use SERIALIZABLE isolation to prevent phantom reads. It must catch serialization failure errors (PostgreSQL error code 40001) and retry up to 5 times with exponential backoff before raising. Lock acquisition order must always go lower account ID first to prevent deadlocks.

That second prompt gives ChatGPT the isolation level, the error code to catch, the retry strategy, and the lock ordering rule. Each of those is something the model will omit if you don't ask. Here's the kind of output you should expect after prompting correctly:

import time
import random
import psycopg2
from psycopg2 import errors

def transfer_funds(conn_params, from_id, to_id, amount, max_retries=5):
    attempt = 0
    while attempt < max_retries:
        try:
            with psycopg2.connect(**conn_params) as conn:
                conn.set_isolation_level(
                    psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE
                )
                with conn.cursor() as cur:
                    # Always lock lower ID first to prevent deadlocks
                    first_id, second_id = sorted([from_id, to_id])
                    cur.execute(
                        "SELECT balance FROM accounts WHERE id = %s FOR UPDATE",
                        (first_id,)
                    )
                    cur.execute(
                        "SELECT balance FROM accounts WHERE id = %s FOR UPDATE",
                        (second_id,)
                    )
                    cur.execute(
                        "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                        (amount, from_id)
                    )
                    cur.execute(
                        "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                        (amount, to_id)
                    )
                conn.commit()
                return
        except errors.SerializationFailure:
            attempt += 1
            wait = (2 ** attempt) + random.uniform(0, 1)
            time.sleep(wait)
    raise RuntimeError(f"Transfer failed after {max_retries} retries")

Notice what would be missing without the detailed prompt: the ISOLATION_LEVEL_SERIALIZABLE setting, the SerializationFailure catch, the retry loop, and the sorted lock order. ChatGPT doesn't add these by default because they're not needed in the common case — and the model optimizes for the common case.

Prompting for pessimistic vs. optimistic locking

Phantom reads can also be prevented without SERIALIZABLE isolation if you use explicit row-level locks. This is called pessimistic locking and is often the right choice when contention is high and you'd rather wait than retry. Tell ChatGPT which strategy you want, or it will pick one arbitrarily.

For pessimistic locking, add this to your prompt: Use SELECT FOR UPDATE to lock affected rows before reading their values. Do not use SKIP LOCKED unless the task specifically involves a queue.

For optimistic locking (better when contention is low), tell ChatGPT: Use a version column for optimistic locking. The UPDATE must include a WHERE clause that checks the version matches what was read, and the function must verify rowcount equals 1 after the update or raise a ConcurrentModificationError.

def update_inventory_optimistic(cur, product_id, delta):
    cur.execute(
        "SELECT quantity, version FROM inventory WHERE product_id = %s",
        (product_id,)
    )
    row = cur.fetchone()
    if row is None:
        raise ValueError("Product not found")
    quantity, version = row
    new_quantity = quantity + delta
    if new_quantity < 0:
        raise ValueError("Insufficient inventory")
    cur.execute(
        """UPDATE inventory
           SET quantity = %s, version = version + 1
           WHERE product_id = %s AND version = %s""",
        (new_quantity, product_id, version)
    )
    if cur.rowcount != 1:
        raise RuntimeError("Concurrent modification detected — retry")

The key thing to include in the prompt is the rowcount check. ChatGPT will write the UPDATE with the version condition, but it almost always forgets to verify that exactly one row was affected. Without that check, a silent concurrency conflict looks like a success.

Handling retry logic inside transactions

Serialization failures require a full transaction retry, not just a statement retry. This is a distinction ChatGPT gets wrong more often than not. If you've seen similar problems in retry logic for HTTP calls, the pattern is discussed in depth in the guide on getting ChatGPT to write accurate retry logic without infinite loop traps.

The specific thing to tell ChatGPT is: On a serialization failure, close the connection or roll back and begin a completely new transaction. Do not retry individual statements within the same transaction context.

Also specify a cap: Retry at most N times. After N attempts, raise an exception — do not enter an infinite loop. Without an explicit cap, ChatGPT often writes while True retry loops with a break on success and no maximum, which will spin forever if the database is stuck in a conflict cycle.

Prompting for correct savepoint usage

Savepoints let you roll back part of a transaction without discarding the whole thing. They're useful when a transaction includes multiple independent operations and a failure in one shouldn't abort the others. ChatGPT knows what savepoints are but tends to misplace them.

A common mistake: ChatGPT puts the savepoint after the risky operation instead of before it, which makes the rollback target useless. Be explicit in your prompt: Create a savepoint immediately before each operation that might fail. On failure, roll back to that savepoint and handle the error, then decide whether to continue or abort the full transaction.

BEGIN;

SAVEPOINT before_charge;
UPDATE accounts SET balance = balance - 100 WHERE id = 42;

-- If insufficient funds error:
-- ROLLBACK TO SAVEPOINT before_charge;

SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 100 WHERE id = 99;

COMMIT;

Ask ChatGPT to generate comments indicating where each rollback-to-savepoint call belongs. This forces the model to reason about error paths rather than just the happy path, and it makes the generated code much easier to review.

Common pitfalls when reviewing ChatGPT transaction code

Even with a well-crafted prompt, there are several things to check before you ship ChatGPT-generated transaction code.

No isolation level set explicitly. Search the output for the word "serializable" or the ORM equivalent. If it's not there, the code is using the database default. For code dealing with financial data, inventory, or seat reservations, the default is almost certainly wrong.

Retry loop without a cap. Any retry loop that isn't bounded by a maximum attempt count is a latent infinite loop. Check for a counter or a maximum retry constant every time.

Catching too broad an exception. ChatGPT sometimes catches Exception and retries on anything, including programming errors, connection failures, and constraint violations. Serialization failures have a specific error code (40001 in PostgreSQL). Only catch and retry on that.

Autocommit left on. Some database libraries default to autocommit mode. If ChatGPT doesn't explicitly disable autocommit, every statement is its own transaction and your BEGIN/COMMIT blocks are no-ops. Always verify the connection's autocommit setting.

Lock order not enforced. If multiple transactions lock the same rows in different orders, you get deadlocks. Ask ChatGPT to explain why the lock order in the generated code is safe, and verify the answer makes sense. This kind of follow-up question often surfaces incorrect assumptions the model made silently.

Similar review habits are worth building for any generated database code. The guide on getting ChatGPT to write accurate SQL migrations without locking production tables covers the same discipline applied to schema changes, where the consequences of getting it wrong are even more visible.

One more thing to watch: ChatGPT sometimes produces transaction code that calls an external service (an API, a message queue) inside the transaction boundary. External calls inside a database transaction are dangerous because they can hold locks open for unpredictable durations and they cannot be rolled back. Tell ChatGPT explicitly: Do not make any network calls, file writes, or external API calls inside the transaction boundary. Queue those as side effects to run after commit.

This also intersects with event-driven patterns — if you're publishing events after a transaction commits, the guidance on ChatGPT and event-driven architecture configs covers how to structure that boundary correctly.

Wrapping up

ChatGPT is a fast way to scaffold transaction code, but it defaults to the assumptions that are safest for simple CRUD, not for concurrent write-heavy workloads. Here are five concrete actions to take right now:

1. State the isolation level in every transaction prompt. Include the exact level (SERIALIZABLE, REPEATABLE READ) and the reason, so ChatGPT can reason toward the correct failure handling.
2. Require serialization failure handling by error code. Ask for 40001 (PostgreSQL) or the equivalent for your database, and ask for a bounded retry loop with exponential backoff.
3. Specify pessimistic or optimistic locking explicitly. Don't let the model choose. If you use optimistic locking, always require the rowcount check after the update.
4. Audit every generated transaction for autocommit state, lock order, and external calls inside the boundary. These three are the most common silent errors in ChatGPT-generated database code.
5. Ask ChatGPT to explain why the lock order is safe before accepting the code. If the answer is vague or wrong, that's a signal to regenerate with a tighter prompt.