Go:v 1.3 db: postgres 使用 lib/pq

我有一个更新 postgres 数据库的应用程序。 postgres 数据库是使用 pgbouncer 设置的。

因此，通过事件连接，我有运行插入和更新的代码。这是插入代码:

func (sitemap *SiteMapData) InsertSiteMap(dbConnection *sql.DB) (int64, error) {

tx, err := dbConnection.Begin()
if err != nil {
    l4g.Error("InsertSiteMap: could not being transaction: %v", err)
    return 0, err
}
result, err := tx.Exec("INSERT INTO sitemap (url) VALUES($1)", sitemap.Url)

if err != nil {
    if !strings.Contains(err.Error(), "duplicate key value violates unique constraint") {
        l4g.Error("sitemapdata.InsertSiteMap: error inserting new sitemap data: %v", err)
    }
    tx.Rollback()
    return 0, nil
}

resultCount, _ := result.RowsAffected()
if err := tx.Commit(); err != nil {
    l4g.Error("InsertSiteMap: could not commit transaction: %v", err)
    return resultCount, err
}
l4g.Info("InsertSiteMap: Insert with %s completed with count: %d", sitemap.Url, resultCount)
return resultCount, nil
}

当我启动应用程序时，它和更新代码一样工作正常:

func (sitemap *SiteMapData) PersistSiteMapData(dbConnection *sql.DB) (int64, error) {

baseQuery, execType := sitemap.buildUpdateQuery()

// as the number of parameters in the statement may vary the following logic is needed
var result sql.Result
var execErr error

tx, err := dbConnection.Begin()
if err != nil {
    l4g.Error("PersistSiteMap: could not being transaction: %v", err)
    return 0, err
}

switch {
case execType == NoExtraDates:
    result, execErr = tx.Exec(baseQuery, sitemap.ConsecutiveFailCount, sitemap.LastAttempt.Time,
        sitemap.Etag.String, sitemap.InternalChecksum.String, sitemap.Id)
case execType == LastGatheredOnly:
    result, execErr = tx.Exec(baseQuery, sitemap.ConsecutiveFailCount, sitemap.LastAttempt.Time,
        sitemap.Etag.String, sitemap.InternalChecksum.String,
        sitemap.LastGathered.Time, sitemap.Id)
case execType == ModifiedHeaderOnly:
    result, execErr = tx.Exec(baseQuery, sitemap.ConsecutiveFailCount, sitemap.LastAttempt.Time,
        sitemap.Etag.String, sitemap.InternalChecksum.String,
        sitemap.ModifiedHeader.Time, sitemap.Id)
case execType == BothDates:
    result, execErr = tx.Exec(baseQuery, sitemap.ConsecutiveFailCount, sitemap.LastAttempt.Time,
        sitemap.Etag.String, sitemap.InternalChecksum.String,
        sitemap.LastGathered.Time, sitemap.ModifiedHeader.Time, sitemap.Id)
}

if execErr != nil {
    tx.Rollback()
    return -1, fmt.Errorf("PersistSiteMapData Error %s: %v", baseQuery, execErr)
}

resultCount, _ := result.RowsAffected()
if err := tx.Commit(); err != nil {
    l4g.Error("PersistSiteMap: could not commit transaction: %v", err)
    return resultCount, err
}
l4g.Info("PersistSiteMapData Updated sitemap %s(%d) correctly", sitemap.Url, sitemap.Id)
return resultCount, nil
}

// buildUpdateQuery returns the update query dependent on the presence of valid datetime fields.
func (sitemap *SiteMapData) buildUpdateQuery() (string, int) {
// note: lastAttempt is not covered here as this is set immediatley prior to the retrieval attempt
nextParam := 5
execType := NoExtraDates
baseQuery := "UPDATE sitemap " +
    "SET " +
    "consecutive_fail_count = $1, last_attempt = $2, etag = $3, internal_checksum = $4"

if sitemap.LastGathered.Valid {
    baseQuery = fmt.Sprintf("%s, last_gathered = $%d", baseQuery, nextParam)
    nextParam++
    execType += LastGatheredOnly
}

if sitemap.ModifiedHeader.Valid {
    baseQuery = fmt.Sprintf("%s, modified_header = $%d", baseQuery, nextParam)
    nextParam++
    execType += ModifiedHeaderOnly
}
baseQuery = fmt.Sprintf("%s WHERE id = $%d", baseQuery, nextParam)

return baseQuery, execType
}

日志显示更新一开始是正确发生的，然后过一会儿我再次检查日志，我看到了:

pq: unexpected transaction status in a failed transaction

通过 lib/pq 代码进行跟踪，它看起来像是在调用 *sql.DB.Begin() 并且事务已在运行时发生的。

我想知道是否有人可以进一步阐明这一点？

我有点想，也许我应该专门跟踪该消息，如果我收到它，则进入一个基于时间的重试循环？或者有没有办法找到错误的交易并将其终止？

谢谢 弥敦道

最佳答案

来自 http://golang.org/pkg/database/sql/#DB.Begin :

Begin starts a transaction. The isolation level is dependent on the driver.

所以看起来 lib/pq 不能很好地处理事务的隔离。

唯一的解决方法是使用 sync.Mutex 在本地锁定事务并在他们的问题跟踪器上提交错误，因为这是一个驱动程序错误。

关于postgresql - Golang、postgres事务: pq: unexpected transaction status in a failed transaction，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/25864670/