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

[BroadZhang]

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

**

[BroadZhang]

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.