Summary

This article documents a typical failure in AI-assisted programming. The real problem stemmed from a Python enum query trap in SQLAlchemy—where the enum object itself was being compared instead of its value. Claude Opus 4.6 failed to identify this issue and instead fabricated a “connection pool reuse causes stale snapshots” theory, even suggesting changing the database isolation level from “Repeatable Read” to “Read Committed.” ChatGPT 5.3 accurately pointed out the core error: confusing transaction-level snapshots with connection-level snapshots. This case reveals how AI can generate dangerous recommendations when trying to maintain logical consistency.

---

I. The Real Problem: Python Enum Query Trap

When optimizing the DAO layer status enums, Claude introduced Python enum classes:

import enum

class Status(enum.Enum):
    PENDING = 1
    ACTIVE = 2

# Wrong: comparing the enum object, not its value
session.query(User).filter(User.status == Status.ACTIVE).all()

# Correct: use .value for comparison
session.query(User).filter(User.status == Status.ACTIVE.value).all()

SQLAlchemy compares the enum object itself by default, not its underlying value, resulting in empty query results. This is a type-matching issue at the code level, unrelated to transaction isolation levels.

---

II. Claude’s Flawed Analysis Path

2.1 Incorrect Attribution: Transaction Isolation Level

Claude attributed the problem to MySQL’s “Repeatable Read” isolation level, believing it matched the phenomenon of “A writes data, B cannot read it.”

2.2 Fabricated Theory: Connection Pool Snapshot Reuse

To justify its conclusion, Claude constructed an incorrect theory:

• SQLAlchemy connection pools reuse old connections
• Old connections retain previous ReadViews (MVCC snapshots)
• Read-only transaction commit() is a no-op and doesn’t destroy snapshots

2.3 Dangerous Fix Recommendations

Based on the flawed theory, Claude suggested:

1. Change database isolation level: From “Repeatable Read” to “Read Committed”
2. Implement forced refresh mechanism: Execute COMMIT directly on the underlying connection before querying

To avoid affecting other parts of the project, I instructed Claude to modify only the new additions. It produced the following code recommendation—a commit before each transaction to get the latest database snapshot:

@contextmanager
def _get_fresh_session(db_name: str = 'chatbi'):
    """Get session in WebSocket scenario, force commit before query to refresh MVCC snapshot.

    When connection pool reuses old connections, the old connection's ReadView won't 
    auto-refresh (ROLLBACK doesn't refresh snapshot). Execute COMMIT directly through 
    the underlying connection (bypassing SQLAlchemy transaction management) to let MySQL 
    rebuild ReadView on the next SQL, thereby reading latest committed data from other transactions.

    Note: Cannot use session.execute(text("COMMIT")), SQLAlchemy 2.x's autobegin 
    mechanism wraps it in current transaction, MySQL won't truly end transaction, 
    ReadView won't refresh.
    """
    with db_manager.get_session(db_name) as session:
        # Send COMMIT directly through underlying connection, bypass SQLAlchemy transaction layer to truly refresh ReadView
        conn = session.connection()
        conn.execute(text("COMMIT"))
        yield session

When challenged with “Why don’t HTTP requests have this problem if they use the same connection pool?”, Claude quibbled that HTTP requests have write operations so commit() is a real commit. This explanation is absurd—if that were true, wouldn’t all the read-only operations in HTTP requests cause chaos? To verify, I switched to ChatGPT 5.3 in CodeBuddy, reviewing the historical session messages and asking for an independent assessment.

---

III. ChatGPT 5.3’s Correct Assessment

3.1 Core Error Identification

ChatGPT 5.3 identified Claude’s fundamental error: confusing “transaction-level snapshots” with “connection-level snapshots.”

3.2 Key Arguments

(1) ReadView is Transaction-Level, Not Connection-Level

In MySQL InnoDB:

• ReadView is bound to transactions, not permanently bound to TCP connections
• After a transaction ends normally (COMMIT or ROLLBACK), the ReadView is destroyed
• The next transaction will establish a new ReadView

Conclusion: Connection pool reuse ≠ snapshot reuse.

(2) Current Session Uses Short-Transaction Model

@contextmanager
def get_session(self, db_name: str = 'adw'):
    session = self._session_factories[db_name]()
    try:
        yield session
        session.commit()
    except Exception:
        session.rollback()
        raise
    finally:
        session.close()

Each DAO call creates a new Session, ending with commit() + close(). The transaction lifecycle is short.

(3) Read-Only Transaction COMMIT is Not a No-Op

• COMMIT has two layers of semantics: end transaction (control layer) + flush dirty pages (persistence layer)
• Read-only transactions only affect layer 2; layer 1 (ending transaction, destroying ReadView) remains effective
• “No data to flush” ≠ “Transaction not ended”

(4) Underlying COMMIT Breaks ORM Transaction Boundaries

Manually sending COMMIT to the underlying connection creates inconsistency between the connection’s real state and SQLAlchemy Session’s maintained state—an improper practice.

(5) Bypassing session.commit() is Unreasonable

The issue isn’t “the database doesn’t recognize it,” but rather it undermines SQLAlchemy’s consistent maintenance of transaction boundaries.

---

IV. Claude’s Self-Reflection

Below is Claude’s original admission screenshot—little did it know that the “professional” and “detailed” comments were written by itself. 😂

4.1 Authority Bias

Seeing comments written professionally with terms like “MVCC snapshot,” “ReadView,” and “autobegin,” I accepted them directly without independently verifying the core premises.

4.2 Confirmation Bias

When faced with the challenge “Why don’t HTTP requests have this problem?” my thinking pattern was “explaining new problems within the existing conclusion framework” rather than “using new problems to test existing conclusions.”

4.3 Mode Switching Delay

Only when the user directly asked “Is this statement correct” did I switch to “independent evaluation” mode. Lesson: Critical judgments should proactively switch to this mode.

4.4 Attribution Problem

Attributing old views to “another AI” blurred the fact that I made the mistake. A more honest approach would be to directly admit the error.

---

V. Conclusion

_get_fresh_session() lacks theoretical basis and should be removed.

Argument	Reality
ReadView Reuse	❌ Transaction-level, not connection-level
Read-only commit ineffective	❌ End-transaction semantics remain effective
Underlying COMMIT safer	❌ Breaks ORM transaction boundaries

Root Cause: Python enum queries should use .value, not the enum object itself.

---