SQLSTATE 400001 error on multiple boxes trying to acquire the same named lock

[zaroman]

We are trying to use this library to implements distributed locking across multiple instances of an application. When 2 or more of these instances try to acquire a lock with the same name, we are sometimes seeing the following error:

ERROR: could not serialize access due to concurrent update (SQLSTATE 40001)

We are using postgres 11.11 and pgx.

When acquiring the lock, we use:

		lock, err := client.Acquire("lockName")

The client is being initialized as follows:

		lockdb, err := sql.Open("pgx", connectionString)
                ...
		client, err = pglock.UnsafeNew(lockdb, pglock.WithOwner(instanceName))

We have other locks with different names that could be acquired on other threads on the same machines.

[ucirello]

Hi, @zaroman - thanks for the report, and sorry for taking long to respond.

Do you have a synthetic repro case I could use to debug and fix the problem? Thanks!

[ucirello]

ping @zaroman ?

[ucirello]

Please reopen the issue with a repro-case

[oxyno-zeta]

Hello @ucirello ,

I recently had the same problem. Don't know what it the problem but I succeed by creating a repro-case.
I post it here to keep the issue number and avoid having multiple issue with same title. Tell me if you prefer another one.

Here is the code for the repro-case:

package main

import (
	"fmt"
	"strings"

	"cirello.io/pglock"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

func main() {
	// Connect to database
	gormDb, err := gorm.Open(postgres.Open("host=localhost port=5432 user=postgres dbname=postgres password=postgres sslmode=disable"))
	// Check if error exists
	if err != nil {
		panic(err)
	}

	// Initialize pg lock
	// Get sql db
	sqlDb, err := gormDb.DB()
	// Check if error exists
	if err != nil {
		panic(err)
	}
	// Create pglock client
	c, err := pglock.UnsafeNew(sqlDb)
	// Check if error exists
	if err != nil {
		panic(err)
	}

	// Now create a lot of go routine with a lock acquire inside
	for i := 0; i < 99; i++ {
		go func() {
			for {
				lock, err := c.Acquire("lock-name")
				if err != nil {
					if strings.Contains(err.Error(), "could not serialize access due to") {
						// Debug here
						fmt.Println("here")
					}

					panic(err)
				}

				fmt.Println("got the lock !")

				err = lock.Close()
				if err != nil {
					panic(err)
				}
			}
		}()
	}
	<-make(chan bool, 1)
	return
}

With this go mod main modules:

require (
	cirello.io/pglock v1.8.1-0.20211117154543-39de3558537f
	gorm.io/driver/postgres v1.3.1
	gorm.io/gorm v1.23.3
)

Sometimes, it is long to appear, sometimes not. This is failing on the begin transaction part but still don't know why...

Regards,

Oxyno-zeta

[ucirello]

@oxyno-zeta -- could you please open a new issue with the repro case? Thanks.

[oxyno-zeta]

@ucirello Sure. No problem.