Transaction scope and .cursor()

[jprafael]

Context:
I'm currently using python + multiple threads to concurrently process many isolated read-only queries.
The parent then gathers responses and writes back to duckdb. All of this happens in the context of a single transaction.
Each worker thread uses a db.cursor() as explained in the documentation.

A new requirement has been introduced that makes the main process write to duckdb before the workers start, but after the transaction has started.

Issue:
I'm observing the unexpected (for me) behavior that the worker threads have no visibility over the uncommitted data introduced before .cursor() was invoked.

Here's a short demo without all the threading abstracted:

#!/usr/bin/env python

import duckdb

con = duckdb.connect()
con.execute("CREATE TABLE t(i INTEGER)")

con.begin()
con.execute("INSERT INTO t VALUES (1)")

parent_count = con.execute("SELECT COUNT(*) FROM t").fetchone()[0]

cursor = con.cursor()
cursor_count_before_commit = cursor.execute("SELECT COUNT(*) FROM t").fetchone()[0]

con.commit()

cursor_count_after_commit = cursor.execute("SELECT COUNT(*) FROM t").fetchone()[0]

print(f"DuckDB version: {duckdb.__version__}")
print(f"Parent connection count inside transaction: {parent_count}")
print(f"cursor() count before parent commit: {cursor_count_before_commit}")
print(f"cursor() count after parent commit: {cursor_count_after_commit}")

assert parent_count == 1
assert cursor_count_before_commit == 0
assert cursor_count_after_commit == 1

I'm unsure if this is expected behavior.
Either the cursor shares the full context to the db connection, in which case it should be able to read the uncommited data.
Or the cursor does not share is state with the parent, and behaves more as a separate connection. If this is the case then the documentation could be more explicit about this.

[nazeeh111]

Yes, this is expected. con.cursor() creates a separate connection to the same database, with its own transaction. It does not join the transaction started by con.begin().

I ran your example on DuckDB 2.0.0 / Python 3.14.7 and got 1, 0, 1: the parent sees its own uncommitted insert, the cursor cannot see it before the parent commits, and its next query sees it after the commit.

There is one extra distinction: your cursor is in autocommit mode. If it explicitly begins a transaction and reads before the parent's commit, further reads keep that earlier snapshot until the cursor ends its transaction. I checked that case too.

For your threaded workflow:

• If the setup write can be committed separately, commit it before starting the workers, then collect their results and write them back in a later transaction. This gives up atomicity across the whole operation.
• If the setup write, dependent reads and final writes must all commit or roll back together, run those database operations on the transaction-owning connection. Separate worker cursors cannot read its uncommitted changes. You can still parallelize computation on data fetched by that connection, then write the results back through it.

The current Python API docs now explicitly describe the independent transaction, and the threading guide calls for a separate connection per thread. So your proposed interpretation as a separate connection is the right one.

[evertlammerts]

The reply above is right, thanks nazeeh111. I checked it on 2.0.0 and on main: cursor() opens a new connection to the same database with its own transaction, so it cannot see the parent's uncommitted rows, and it also has its own temp tables and search path. The 2.0 docs now say a cursor has its own transaction.