Why Two SELECTs in the Same Transaction Can Return Different Results in GBase 8c

Seeing different query results within the same transaction in a gbase database can be confusing. Many operators suspect caching, replication lag, or driver issues. However, the root cause often lies in a mismatch between the application’s expectation and the default READ COMMITTED isolation level, which provides statement‑level snapshots, not transaction‑level ones. This article explains the mechanics, demonstrates the behavior with concrete examples, and offers guidance on choosing the right isolation level and retry strategy.

1. Three Things to Check Before Diving In

When two SELECT statements inside an explicit transaction return different values, first clarify:

1. Is the transaction explicitly opened with BEGIN?
2. What is the current isolation level? (default: READ COMMITTED)
3. Does the business need “the latest committed data” or “a stable view throughout the transaction”?

Misinterpreting these points often leads to unnecessary troubleshooting of synchronization or failover mechanisms.

2. Core Concepts

2.1 Default Behavior: Statement‑Level Snapshots

GBase 8c defaults to the READ COMMITTED isolation level. Every statement in a transaction sees a snapshot taken at the start of that statement. If another transaction commits changes between two queries, the second query will see the new data. This is normal and expected.

2.2 Transaction‑Level Stable View: REPEATABLE READ

Under REPEATABLE READ, a transaction sees a snapshot taken at the start of the transaction. Subsequent committed changes from other transactions are invisible to this transaction, providing a consistent view for operations like reconciliation or settlement. However, serialization failures may occur, requiring application‑level retries.

2.3 Distributed 2PC ≠ Consistent Read Snapshot

GBase 8c uses two‑phase commit (2PC) for cross‑DN transactions to ensure atomicity. 2PC guarantees that writes across nodes are all committed or all rolled back, but it does not provide a globally consistent read snapshot. Read consistency is still governed by the isolation level.

2.4 MVCC and Non‑Blocking Reads

GBase 8c implements MVCC combined with two‑phase locking, so readers normally don't block writers and vice versa. A “different” read result should not be mistaken for a lock conflict.

3. Reproducing the Behavior

Setup:

gsql -h 192.0.2.10 -p 5432 -d retaildb -U app_user

CREATE SCHEMA IF NOT EXISTS lab_txn;
SET search_path TO lab_txn;

DROP TABLE IF EXISTS t_inventory;
CREATE TABLE t_inventory (
    sku_id       bigint PRIMARY KEY,
    stock_qty    integer NOT NULL,
    update_time  timestamp NOT NULL DEFAULT now()
);

INSERT INTO t_inventory (sku_id, stock_qty) VALUES
(10001, 50),
(10002, 80);

Scenario 1: Different Results Under READ COMMITTED

Session A:

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT stock_qty FROM t_inventory WHERE sku_id = 10001;
-- Returns 50

-- Wait for Session B to commit
SELECT stock_qty FROM t_inventory WHERE sku_id = 10001;
-- May now return 45

COMMIT;

Session B:

BEGIN;
UPDATE t_inventory SET stock_qty = stock_qty - 5, update_time = now()
WHERE sku_id = 10001;
COMMIT;

This is expected READ COMMITTED behavior.

Scenario 2: Consistent View Under REPEATABLE READ

Reset the data:

UPDATE t_inventory SET stock_qty = CASE sku_id WHEN 10001 THEN 50 WHEN 10002 THEN 80 END,
       update_time = now();
COMMIT;

Session A:

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT sum(stock_qty) AS total_qty FROM t_inventory;
-- Returns 130

-- Wait for Session B to commit
SELECT sum(stock_qty) AS total_qty FROM t_inventory;
-- Still returns 130

COMMIT;

Session B:

BEGIN;
UPDATE t_inventory SET stock_qty = stock_qty - 10, update_time = now()
WHERE sku_id = 10002;
COMMIT;

REPEATABLE READ maintains a stable snapshot, ideal for verification and settlement tasks.

Scenario 3: Adding Application‑Level Retries

When using REPEATABLE READ with updates, serialization failures can occur. Applications must be prepared to retry:

#!/usr/bin/env bash
DB_HOST=192.0.2.10
DB_PORT=5432
DB_NAME=retaildb
DB_USER=settle_user
SQL_FILE=/opt/app/sql/txn_settle.sql
MAX_RETRY=3
TRY_NO=1

while [ ${TRY_NO} -le ${MAX_RETRY} ]
do
  gsql -h ${DB_HOST} -p ${DB_PORT} -d ${DB_NAME} -U ${DB_USER} -f ${SQL_FILE}
  RC=$?
  if [ ${RC} -eq 0 ]; then
    exit 0
  fi
  sleep $((TRY_NO * 2))
  TRY_NO=$((TRY_NO + 1))
done
exit 1

4. Choosing the Right Isolation Level

5. Common Pitfalls

• “A BEGIN guarantees a stable view” → Only REPEATABLE READ provides a transaction‑level snapshot.
• “2PC guarantees consistent reads across nodes” → 2PC ensures write atomicity, not read snapshot consistency.
• “Switching to REPEATABLE READ solves everything” → Conflicts may increase; retries are essential.
• “It worked in my manual test” → Connection pools and auto‑commit can alter transaction boundaries.
• “My single SELECT is part of a transaction” → Without an explicit BEGIN, a statement runs in its own auto‑commit transaction.

6. Practical Recommendations

1. Clarify the business requirement: statement‑level consistency or transaction‑level consistency.
2. Set the isolation level explicitly at the start of the transaction.
3. Avoid wrapping multiple long‑running operations in one transaction; split the pipeline.
4. Distinguish “snapshot drift” from “lock contention”; they have different root causes.
5. For distributed transactions, evaluate both 2PC atomicity and the isolation level.

Mastering the boundaries between transaction snapshots and isolation levels is key to eliminating unexpected result variations in your gbase database.