How to solve concurrent read and write without affecting each other，This is a big problem for me, please help me

[bitszhang]

package main
import (
	"database/sql"
	"fmt"
	"log"
	"net/http"
	"sync"

	"github.com/gin-gonic/gin"
	_ "github.com/marcboeker/duckdb"
)

type DuckDB struct {
	dataSourceName string
	db             *sql.DB
}

var (
	instance *DuckDB
	once     sync.Once
)

// NewDuckDB 创建一个新的 DuckDB 实例，并配置连接池
func NewDuckDB(dataSourceName string) (*DuckDB, error) {
	db, err := sql.Open("duckdb", dataSourceName)
	if err != nil {
		return nil, err
	}

	// 配置连接池参数
	db.SetMaxOpenConns(10)  // 最大打开连接数
	db.SetMaxIdleConns(5)   // 最大空闲连接数
	db.SetConnMaxLifetime(0) // 连接可复用的最大时间，0表示无限制

	return &DuckDB{
		dataSourceName: dataSourceName,
		db:             db,
	}, nil
}

// GetInstance 返回 DuckDB 的全局实例
func GetInstance(dataSourceName string) (*DuckDB, error) {
	var err error
	once.Do(func() {
		instance, err = NewDuckDB(dataSourceName)
	})
	return instance, err
}

// CreateTable 创建表
func (d *DuckDB) CreateTable(query string) error {
	_, err := d.db.Exec(query)
	return err
}

// ExecuteQuery 执行查询并返回结果
func (d *DuckDB) ExecuteQuery(query string) (*sql.Rows, error) {
	return d.db.Query(query)
}
//执行删除操作
func (d *DuckDB) Delete(query string) error {
	_, err := d.Db.Exec(query)
	return err
}

** I will call GetInstance in the main method, and the following operations are methods under Instance

The above is my duck db operation. When I execute delete, other query requests will wait for delete to complete, even if they are not a table

**

[bitszhang]

Just to add, my data volume is about 50000 entries, and the data table contains several indexes. When executing delete from table, it takes about 15 seconds. During these 15 seconds, all other read and write operations are waiting

[marcboeker]

Have you seen the DuckDB docs about concurrency? Your code looks like it's used on an environment with multiple threads. Maybe you can make sure, to run your code in a single thread to ensure concurrency.

[taniabogatsch]

Just to add, my data volume is about 50000 entries, and the data table contains several indexes. When executing delete from table, it takes about 15 seconds. During these 15 seconds, all other read and write operations are waiting

While deleting from an index, DuckDB acquires an index lock. So other waiting write operations sounds expected, as we do not support concurrency on index-write operations.

Additionally, as far as I know, the index is available for read operations while being altered. But, once these changes are flushed, there are a few locks in the code, so those might also be blocking.

To sum up, this seems to be unrelated to go-duckdb. And possibly expected behavior in DuckDB itself... 🤔 There have been a few improvements for index performance lately, but if the delete takes 'longer than expected', you might want to open an issue with a reproduction in the DuckDB repository.

[taniabogatsch]

The above is my duck db operation. When I execute delete, other query requests will wait for delete to complete, even if they are not a table

This sounds unintended, operations on different tables should not be blocked 🤔. Maybe you could open an issue with the full reproduction including the queries, etc., either here or in the DuckDB repo?