Golang中提交时出现意外的数据库锁问题

Golang中提交时出现意外的数据库锁问题 我正在开发一个应用程序，它提供一项服务和一个HTTP API，用于管理存储在数据库（SQLite或PostgreSQL）中的用户。这两者都是通过goroutine实现的。

到目前为止，创建和删除用户的功能一直按预期工作。现在我想添加一个额外的goroutine来定期执行数据库维护任务（例如删除未使用的用户）。

对于删除特定用户，使用的是已存在的函数 Unregister()。该函数只是锁定数据库，启动一个事务，准备删除SQL语句，最后以用户名作为参数执行它。

import (
  "database/sql"
  _ "github.com/mattn/go-sqlite3"
)

func (d *userdb) Unregister(username uuid.UUID) error {
  d.Lock()
  defer d.Unlock()
  var err error
  tx, err := d.DB.Begin()

  // 如果出错则回滚，否则提交
  defer func() {
    if err != nil {
      tx.Rollback()
      return
    }
    err = tx.Commit()
    if err != nil {
      fmt.Println(err)
    }
  }()

  unregSQL := `DELETE FROM records WHERE Username = $1`
  if Config.Database.Engine == "sqlite3" {
    unregSQL = getSQLiteStmt(unregSQL)
  }
  sm, err := tx.Prepare(unregSQL)
  if err != nil {
    log.WithFields(log.Fields{"error": err.Error()}).Error("Database error in prepare")
    return errors.New("SQL error")
  }
  defer sm.Close()
  _, err = sm.Exec(username.String())
  if err != nil {
    log.WithFields(log.Fields{"error": err.Error()}).Error("Database error in execute")
    return errors.New("SQL error")
  }

  return nil
}

如果通过API使用它，它可以正常工作，但如果通过维护函数使用，则不行。问题在于事务提交会挂起，并在超时后失败，报错 database is locked。

有人知道这个问题的可能原因是什么吗？


更多关于Golang中提交时出现意外的数据库锁问题的实战教程也可以访问 https://www.itying.com/category-94-b0.html

3 回复

你的代码存在潜在的缺陷，并且设计不佳。应该使用两种不同的用户服务：一种用于 SQLite3，另一种用于 PostgreSQL，并将它们注入到你所谓的服务器中。修改你的日志记录设计，尽量避免在服务宕机时记录错误。返回带有堆栈信息的错误是一个好习惯。

遇到错误时立即处理，不要留到以后：

 defer tx.Rollback() // 如果提交成功，则忽略回滚并记录日志
  _, err = sm.Exec(username.String())
  if err != nil {
    return errors.WithStack(err)
  }
 tx.Commit()

你查看过数据库锁的日志吗？你是否并发运行了 Unregister 方法？我猜 Username 字段上应该有索引，对吗？如果 username 上没有定义索引，并且你并发运行它（在不了解 PostgreSQL 和 SQLite 的情况下），数据库引擎可能会锁定表。

更多关于Golang中提交时出现意外的数据库锁问题的实战系列教程也可以访问 https://www.itying.com/category-94-b0.html


感谢您的帮助。我终于找到了锁的原因。这不是数据库本身的锁，而是来自 database/sql 包的锁。

在清理函数中，运行了一个 SELECT 查询来获取所有未使用的用户。之后，结果被逐行迭代以删除每个用户。简而言之，主要步骤如下：

for rows.Next() {
	var user uuid.UUID
	var maxUpdate int
	err = rows.Scan(&user, &maxUpdate)
	err = DB.Unregister(user)
}

不幸的是，我之前没有意识到在 rows.Close() 调用之前会存在一个锁。因此，在这个锁存在期间，用户当然无法被删除。所以我不得不修改它，先从结果中收集用户数据，执行 rows.Close()，然后再删除用户：

var usersToDelete = []userToDelete{}
for rows.Next() {
	var userToDelete userToDelete
	err = rows.Scan(&userToDelete.user, &userToDelete.LatestUpdate)
	usersToDelete = append(usersToDelete, userToDelete)
}
rows.Close()
for _, userToDelete := range usersToDelete {
	err = DB.Unregister(userToDelete.user)
}

如果您知道解决这个问题的更好方法，请告诉我。

从代码来看，问题很可能出现在锁的持有时间过长与事务隔离的冲突上。你的 Unregister() 方法在函数开始时通过 d.Lock() 获取了互斥锁，但事务的提交（tx.Commit()）发生在 defer 中，这意味着锁会一直持有到整个函数结束。当维护 goroutine 和 API goroutine 并发操作时，如果多个操作试图同时访问数据库，SQLite 可能报告锁冲突，因为一个连接的事务在等待锁释放，而另一个连接持有锁并试图提交事务。

SQLite 在并发写入时尤其敏感，因为它的锁机制是数据库级别的。即使你使用了互斥锁保护了 Unregister() 方法，但事务提交时数据库锁的获取可能与其他未受同一互斥锁保护的数据库操作冲突。例如，如果其他代码路径（如查询）直接使用 d.DB 执行 SQL 而没有通过相同的互斥锁序列化，它们可能并发运行，导致锁超时。

以下是一个示例，展示如何重构代码以缩短锁的持有时间，并确保事务提交在锁释放前完成：

func (d *userdb) Unregister(username uuid.UUID) error {
    d.Lock()
    defer d.Unlock()

    // 将事务操作移出 defer 以更精确控制锁和事务生命周期
    tx, err := d.DB.Begin()
    if err != nil {
        log.WithFields(log.Fields{"error": err.Error()}).Error("Failed to begin transaction")
        return errors.New("SQL error")
    }

    unregSQL := `DELETE FROM records WHERE Username = $1`
    if Config.Database.Engine == "sqlite3" {
        unregSQL = getSQLiteStmt(unregSQL)
    }
    sm, err := tx.Prepare(unregSQL)
    if err != nil {
        tx.Rollback()
        log.WithFields(log.Fields{"error": err.Error()}).Error("Database error in prepare")
        return errors.New("SQL error")
    }
    defer sm.Close()

    _, err = sm.Exec(username.String())
    if err != nil {
        tx.Rollback()
        log.WithFields(log.Fields{"error": err.Error()}).Error("Database error in execute")
        return errors.New("SQL error")
    }

    // 在释放锁之前提交事务
    err = tx.Commit()
    if err != nil {
        log.WithFields(log.Fields{"error": err.Error()}).Error("Failed to commit transaction")
        return errors.New("SQL error")
    }

    return nil
}

此外，检查你的维护函数和其他数据库操作是否共享同一个数据库连接，以及是否所有写操作都通过相同的互斥锁序列化。如果使用 SQLite，考虑设置连接池参数为 1（db.SetMaxOpenConns(1)）以避免并发写入冲突：

db, err := sql.Open("sqlite3", "./users.db")
if err != nil {
    log.Fatal(err)
}
db.SetMaxOpenConns(1) // 限制只有一个打开的连接，防止锁竞争

对于 PostgreSQL，通常并发处理更好，但同样需要确保事务隔离和锁的正确使用。如果问题持续，启用 SQLite 的 WAL 模式可能有助于改善并发：

PRAGMA journal_mode=WAL;

这可以在数据库初始化时执行。

回到顶部