Committed Data Not Visible to Other Open Connections

[MistrKernnunos]

Summary

When multiple connections are open to the same DuckDB database file, committed transactions from one connection are not visible to other open connections, even after explicit COMMIT and CHECKPOINT. This behavior differs from the Python DuckDB bindings and contradicts the documented DuckDB behavior.

Environment

• duckdb-rs version: 1.10500.0 (DuckDB 1.1.5)
• Rust version: 1.93.0
• OS: macOS (also reproducible on Linux)
• DuckDB Python version (for comparison): 1.5.0

Expected Behavior

According to the DuckDB ACID blog post, when one connection commits a transaction, other open connections should be able to see the committed data. The blog post demonstrates this with Python:

con1.begin()
con1.sql("INSERT INTO customer VALUES (43, 'Wilbur the Duck')")
# no commit!

con2 = duckdb.connect(":memory:mydb")
con2.sql("SELECT name FROM customer").show()  # Does NOT show Wilbur

con1.commit()

con2.sql("SELECT name FROM customer").show()  # NOW shows Wilbur ✅

Actual Behavior in Rust

In the Rust bindings, after con1 commits, con2 still cannot see the committed data:

let con1 = duckdb::Connection::open(&db_path).unwrap();
con1.execute_batch("CREATE TABLE customer (id INTEGER, name VARCHAR)").unwrap();
con1.execute("INSERT INTO customer VALUES (?, ?)", params![42, "DuckDB Labs"]).unwrap();

con1.execute_batch("BEGIN TRANSACTION").unwrap();
con1.execute("INSERT INTO customer VALUES (?, ?)", params![43, "Wilbur the Duck"]).unwrap();

let con2 = duckdb::Connection::open(&db_path).unwrap();

// Before commit - correctly shows 1 row
let count: i64 = con2.query_row("SELECT COUNT(*) FROM customer", [], |row| row.get(0)).unwrap();
assert_eq!(count, 1); // ✅ Passes

// Commit from con1
con1.execute_batch("COMMIT").unwrap();

// After commit - STILL shows 1 row (should show 2)
let count: i64 = con2.query_row("SELECT COUNT(*) FROM customer", [], |row| row.get(0)).unwrap();
assert_eq!(count, 2); // ❌ FAILS - count is still 1

The committed data only becomes visible if:

1. con1 is closed/dropped, AND
2. A fresh connection (con3) is opened

Minimal Reproduction

Rust Test (Fails)

use duckdb;
use tempfile::TempDir;

#[test]
fn test_transaction_visibility() {
    let temp_dir = TempDir::new().unwrap();
    let db_path = temp_dir.path().join("test.db");
    
    let con1 = duckdb::Connection::open(&db_path).unwrap();
    con1.execute_batch("CREATE TABLE test (id INTEGER, value TEXT)").unwrap();
    
    let con2 = duckdb::Connection::open(&db_path).unwrap();
    
    // Write and commit with con1
    con1.execute_batch("BEGIN TRANSACTION").unwrap();
    con1.execute("INSERT INTO test VALUES (1, 'hello')", []).unwrap();
    con1.execute_batch("COMMIT").unwrap();
    
    // con1 sees the data
    let count1: i64 = con1.query_row("SELECT COUNT(*) FROM test", [], |row| row.get(0)).unwrap();
    assert_eq!(count1, 1); // ✅ Passes
    
    // con2 should see the committed data but doesn't
    let count2: i64 = con2.query_row("SELECT COUNT(*) FROM test", [], |row| row.get(0)).unwrap();
    assert_eq!(count2, 1); // ❌ FAILS - count2 is 0
}

Python Test (Works)

import duckdb
import tempfile
import os

with tempfile.TemporaryDirectory() as tmpdir:
    db_path = os.path.join(tmpdir, "test.db")
    
    con1 = duckdb.connect(db_path)
    con1.execute("CREATE TABLE test (id INTEGER, value TEXT)")
    
    con2 = duckdb.connect(db_path)
    
    # Write and commit with con1
    con1.execute("BEGIN TRANSACTION")
    con1.execute("INSERT INTO test VALUES (1, 'hello')")
    con1.execute("COMMIT")
    
    # con2 sees the committed data immediately
    result = con2.execute("SELECT COUNT(*) FROM test").fetchone()
    assert result[0] == 1  # ✅ Passes

Additional Observations

1. CHECKPOINT doesn't help: Even calling con1.execute_batch("CHECKPOINT") after commit doesn't make the data visible to con2
2. Data is not persisted: The committed data is not written to the database file until con1 is closed
3. Works with sequential connections: If con1 is closed before opening con2, the data is visible
4. Affects both file and in-memory databases: The issue occurs with both file-based and in-memory databases

Impact

This issue breaks any application that needs to:

• Have multiple connections open simultaneously
• Read committed data from one connection while another connection is still open
• Implement concurrent read/write patterns as documented in DuckDB's concurrency model

Workaround

Currently, the only workaround is to ensure all writing connections are closed before opening new connections for reading:

// Write
let con1 = duckdb::Connection::open(&db_path).unwrap();
con1.execute("INSERT INTO test VALUES (1, 'hello')", []).unwrap();
drop(con1); // Must close

// Read
let con2 = duckdb::Connection::open(&db_path).unwrap();
let count: i64 = con2.query_row("SELECT COUNT(*) FROM test", [], |row| row.get(0)).unwrap();
// Now it works

Related Documentation

• DuckDB ACID Blog Post
• DuckDB Concurrency Documentation

Question

Is this intended behavior in the Rust bindings, or is this a bug? The Python bindings clearly support the documented behavior of transaction visibility across connections, but the Rust bindings do not.

[mlafeldt]

This is a known limitation of the current API: each Connection::open() call creates a separate duckdb_database instance via duckdb_open_ext(), even for the same file path. They are independent in-process databases that don't share transaction state.

Python's duckdb.connect() avoids this by internally reusing the same database instance for the same path.

As a workaround, I recommend using try_clone() to create connections that share the same database handle:

let con1 = Connection::open(&db_path)?;
let con2 = con1.try_clone()?; // shares the same duckdb_database instance

See also: #378

Longer-term fix: DuckDB's C API provides an instance cache that automatically reuses database instances for the same path. This would match Python's behavior.