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.

[Michael-WhiteCapData]

This behavior is expected, although the current wording around "handle" is easy to read differently.

cursor() shares the underlying DuckDB database instance/catalog, but it has its own client context and transaction. It does not join the parent connection's active transaction. Therefore:

• the insert is visible on con, which owns the uncommitted write;
• the cursor cannot see it before con.commit();
• after the commit, a query on the cursor can see it under normal read-committed behavior.

There is no supported way for several worker contexts to read another context's uncommitted changes as one shared transaction. Work that requires that state must run through the parent connection and will be serialized. The practical options are:

1. commit the setup writes before starting workers, then give each worker its own con.cursor(); or
2. keep the whole operation on con if atomic visibility of the uncommitted setup is required.

For a concurrent read phase followed by a write-back phase, I would commit setup, run workers with thread-local cursors, collect their results, then start a new transaction on the parent for the write-back. If one atomic transaction across all three phases is mandatory, concurrent DuckDB client contexts are not the right boundary.

The threading guide's phrase "thread-local connection" matches the observed semantics: DuckDB multiple Python threads.