Troubleshooting & FAQ

Error: “Could not set lock on file”

Error: Could not set lock on file "path/to/database.duckdb": 
Conflicting lock is held in /path/to/R (PID 12345) by user...

Cause: DuckDB uses file-level locking to ensure data integrity. Only one process can write to a database file at a time. This error occurs when:

• Another R session is connected to the same database
• A previous R session didn’t disconnect properly before closing
• Multiple scripts are trying to access the same database simultaneously

Solutions:

1. Kill the conflicting process (shown in the error message):

   # Replace 12345 with the actual PID from the error
   kill 12345

2. Restart RStudio to close all R processes

3. Properly disconnect before closing sessions:

   # Always disconnect when done
   proj$disconnect()
   
   # Or for direct DBI connections:
   DBI::dbDisconnect(con, shutdown = TRUE)

4. Use read-only mode for concurrent access:

   # Multiple processes can read simultaneously
   con <- DBI::dbConnect(duckdb::duckdb(), dbdir = "path/to/db", read_only = TRUE)

Error: “No active or cached connection available”

Cause: The dbProject connection has been closed or was never established. Solution: Reconnect to the database:

proj$reconnect()

Objects showing “Error: Column references …” after restart

Cause: Lazy table references become stale after R restarts because the connection is lost.

Solution: Use dbProject for automatic reconnection:

# Create project (saves connection info)
proj <- dbProject$new(path = "my_project", dbdir = "data.duckdb")

# After restart, just reload
proj <- dbProject$new(path = "my_project", dbdir = "data.duckdb")
proj$reconnect()

# Pinned objects are automatically reconnected
my_data <- proj$pin_read("my_table")

Large datasets causing R to crash

Cause: Accidentally collecting large lazy tables into memory.

Prevention:

1. Avoid collect() on large tables:

   # BAD - loads entire table into memory
   big_df <- big_table |> collect()
   
   # GOOD - keep lazy, push computation to database and only collect what's needed
   result <- big_table |> 
     filter(gene == "BRCA1") |>
     collect()

2. Use pin_write() to materialize intermediates to disk:

   # Materialize to database, not memory
   filtered_data <- proj$pin_write(x = lazy_filtered, name = "filtered")

dbMatrix: Slow operations after many transformations

Cause: Complex lazy query chains can cause performance issues.

Solution: Use dplyr::compute() or pin_write() to materialize intermediate results:

# Materialize after expensive operations
complex_result <- my_dbMatrix |>
  some_expensive_transform() |> # e.g. a function containing several SQL joins
  dplyr::compute(name = "materialized", temporary = FALSE)