Unexpextend database lock at commit

[webcompas]

I’m working on an application which provides a service and a HTTP API to manage the users stored in a database (SQlite or PostgreSQL). Both of them are realized via goroutines.
Up to now the functions to create and delete users have been working as expected. Now I want to add an additional goroutine to regularly to database maintenance tasks (e.g. deleting unused users).
For deleting a specific user the already existing function Unregister() is used. The function just locks the DB, starts a transaction, prepares the delete SQL and finally executes it with the username as parameter. The

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()

  // Rollback if errored, commit if not
  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
}

If it is used via the API it’s working properly but if used via the maintenance function it isn’t. The problem is that the transaction commit hangs and after the timeout fails with the error database is locked.

Does anyone have an idea what could be the reason for this problem?

[kync (Ali Koyuncu)]

your code is open to potential bugs and not designed well, use two different user service:one for sqllite3 one for postresql and inject them into your server whatever you call it. Change your logging design, try to not log errors in the down services, it is good habit to return errors with stack.

handle errors whenever you encounter, don’t leave it later:

 defer tx.Rollback() // if commits succeds ignores rollback and logs it
  _, err = sm.Exec(username.String())
  if err != nil {
    return errors.WithStack(err)
  }
 tx.Commit()

Did you take a look at the db lock logs? Do you run the Unregister method concurrently? I guess there is an index on the Username, right? If there is no index defined on the username and if you run it concurrently (without knowledge of postresql and sqlite), db engine may lock the table.

[webcompas]

Thank you for your help. I finally found the reason for the lock. It wasn’t a lock from the database itselft but from the database/sql.
In the cleanup function a SELECT is run to get all unused users. Afterwards the result is iterated row by row to delete each user. I short these are the main steps:

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

Unfortunately I wasn’t aware of the fact that there is a lock until rows.Close(). Therefor of course the user cannot be deleted while this lock exists. So I had to change this to gather the user data from the result, do rows.Close() and afterwards delete the users:

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)
}

If you know a better way to solve this problem please let me know.