使用独立*sql.DB仍遇SQLite数据库锁定错误的解决方法

问题根源 #

1. SQLite锁机制限制：SQLite采用文件级锁，写操作需要独占锁，若存在未完成的读操作（比如遍历rows的过程），会阻塞写操作触发锁冲突。
2. 连接资源未及时释放：在rows.Next循环内执行写操作时，当前连接被读操作占用，写操作需等待锁释放，超时后抛出锁定错误。
3. 未处理隐式错误：代码未检查rows.Err()，可能存在资源泄漏的隐式问题。

具体修复步骤 #

• 先读数据再批量更新：避免在遍历rows时执行写操作，先把需要更新的数据读取到内存，关闭rows释放读锁后再执行更新。修改后的DoSomething函数示例：

  func DoSomething() {
      db1, err := sql.Open("sqlite3", "test.db")
      if err != nil {
          panic(err.Error())
      }
      defer db1.Close()
  
      updatePhone, err := db1.Prepare("UPDATE People SET phone = ? WHERE person_id = ?")
      if err != nil {
          log.Fatal(err)
      }
      defer updatePhone.Close()
  
      var person struct {
          name    sql.NullString
          address sql.NullString
          phone   sql.NullInt64
      }
  
      // 循环执行
      for {
          time.Sleep(1 * time.Second)
          rows, err := db1.Query("SELECT * FROM people")
          if err != nil {
              log.Fatal(err)
          }
  
          // 收集需要更新的person_id
          var needUpdateIDs []string
          for rows.Next() {
              err = rows.Scan(&person.name, &person.address, &person.phone)
              if err != nil {
                  log.Fatal(err)
              }
              if !person.phone.Valid {
                  needUpdateIDs = append(needUpdateIDs, person.name.String)
              }
          }
          // 检查遍历过程中的隐式错误
          if err := rows.Err(); err != nil {
              log.Fatal(err)
          }
          rows.Close() // 释放读锁
  
          // 批量执行更新
          for _, id := range needUpdateIDs {
              _, err = updatePhone.Exec(12345, id)
              if err != nil {
                  log.Println(err)
              }
          }
      }
  }
  

• 增加锁等待超时时间：打开数据库时添加_busy_timeout参数，延长锁等待时长，避免短暂冲突直接报错：

  db1, err := sql.Open("sqlite3", "test.db?_busy_timeout=5000")
  

• 优化连接池配置：通过db.SetMaxIdleConns和db.SetMaxOpenConns合理设置连接池大小，避免连接泄漏，例如：

  db1.SetMaxIdleConns(2)
  db1.SetMaxOpenConns(5)
  

• 避免长期持有预编译语句：若不需要复用预编译语句，可直接使用db.Query或db.Exec，让连接池更灵活地管理连接。

额外建议 #

SQLite适合单进程低并发场景，若业务存在高并发读写需求，建议更换为客户端-服务器型数据库（如MySQL、PostgreSQL）。

内容的提问来源于stack exchange，提问作者aste123