demo optimistic locking in go

demo_optimistic_locking.go

/*
credit to this great article
https://www.2ndquadrant.com/en/blog/postgresql-anti-patterns-read-modify-write-cycles/
*/

package main

import (
"fmt"
"github.com/jmoiron/sqlx"
	"os"
	"time"
	_ "github.com/lib/pq"
)

func connectDB() *sqlx.DB {
	dbUser := "dev"
	dbName := "sandbox"
	dbHost := "localhost:5432"
	dbPassword := "matkhau"
	dsn := fmt.Sprintf("postgres://%s:%s@%s/%s?sslmode=disable", dbUser, dbPassword, dbHost, dbName)
	return sqlx.MustConnect("postgres", dsn)
}

func main () {
	argsWithoutProg := os.Args[1:]
	clientId := argsWithoutProg[0]

	//fmt.Println("connecting to db")
	db := connectDB()
	err := db.Ping()
	if err != nil {
		panic(err)
	}

	sql := `select first_name, last_name, email, version from person where last_name = 'Doe'`
	p := struct {
		FirstName string `db:"first_name"`
		LastName string `db:"last_name"`
		Email string `db:"email"`
		Version int `db:"version"`
	}{}

	err = db.Get(&p, sql)

	if err != nil {
		panic(err)
	}

	fmt.Printf("[%s - %d] person: %s ver: %d ", clientId, time.Now().Unix(), p.FirstName, p.Version)

	// use affected row

	//sql = fmt.Sprintf(
	//	`
	//	update person
	//	set first_name = '%s', version = version + 1
	//	where last_name = 'Doe' and version = '%d'`,
	//	fmt.Sprintf("Name %d", time.Now().Unix()),
	//	p.Version)
	//
	//rs := db.MustExec(sql)
	//affectedRows, err := rs.RowsAffected()
	//if err != nil {
	//	panic(err)
	//}
	//fmt.Printf(" > [%s - %d] affected rows: %d \n", clientId, time.Now().Unix(), affectedRows)

	// use returning
	rows, err := db.Queryx(`update person
		set first_name=$1, version = version +1
		where last_name = $2 and version = $3
		returning first_name, version
		`,
		fmt.Sprintf("Name %d", time.Now().Unix()),
		"Doe",
		p.Version)

	if err != nil {
		panic(err)
	}
	defer rows.Close()

	var firstName string
	var version int

	for rows.Next() {
		err := rows.Scan(&firstName, &version)
		if err != nil {
			panic(err)
		}
	}
	fmt.Printf(" > [%s - %d] person: %s ver: %d \n", clientId, time.Now().Unix(), firstName, version)

	db.Close()
}

guide.md

to stimulate concurrency, use xargs with -P10

seq 100 | xargs -I$ -P10 go run test.go $ > /tmp/test_concurrency_pg_go.txt 

Those clients meet race condition will be seen as

[15 - 1599831949] person: Name 1599831949 ver: 4493  > [14 - 1599831949] person:  ver: 0 
 > [15 - 1599831949] person: Name 1599831949 ver: 4494 

output.txt

[1 - 1599831947] person: Name 1599831932 ver: 4479  > [1 - 1599831947] person: Name 1599831947 ver: 4480 
...
[16 - 1599831949] person: Name 1599831949 ver: 4491  > [16 - 1599831949] person: Name 1599831949 ver: 4492 
[11 - 1599831949] person: Name 1599831949 ver: 4492 [14 - 1599831949] person: Name 1599831949 ver: 4492  > [11 - 1599831949] person: Name 1599831949 ver: 4493 
[15 - 1599831949] person: Name 1599831949 ver: 4493  > [14 - 1599831949] person:  ver: 0 
 > [15 - 1599831949] person: Name 1599831949 ver: 4494 
[18 - 1599831949] person: Name 1599831949 ver: 4494  > [18 - 1599831949] person: Name 1599831949 ver: 4495 
[19 - 1599831949] person: Name 1599831949 ver: 4495  > [19 - 1599831949] person: Name 1599831949 ver: 4496 
[17 - 1599831949] person: Name 1599831949 ver: 4496  > [17 - 1599831949] person: Name 1599831949 ver: 4497 
...
[23 - 1599831951] person: Name 1599831950 ver: 4498  > [23 - 1599831951] person: Name 1599831951 ver: 4499 
[28 - 1599831951] person: Name 1599831951 ver: 4499 [26 - 1599831951] person: Name 1599831951 ver: 4499  > [28 - 1599831951] person: Name 1599831951 ver: 4500 
 > [26 - 1599831951] person:  ver: 0 
[21 - 1599831951] person: Name 1599831951 ver: 4500  > [21 - 1599831951] person: Name 1599831951 ver: 4501 
[22 - 1599831951] person: Name 1599831951 ver: 4501  > [22 - 1599831951] person: Name 1599831951 ver: 4502 
[29 - 1599831951] person: Name 1599831951 ver: 4502  > [29 - 1599831951] person: Name 1599831951 ver: 4503 
[25 - 1599831951] person: Name 1599831951 ver: 4503  > [25 - 1599831951] person: Name 1599831951 ver: 4504 
[27 - 1599831951] person: Name 1599831951 ver: 4504  > [27 - 1599831951] person: Name 1599831951 ver: 4505 
[24 - 1599831951] person: Name 1599831951 ver: 4505  > [24 - 1599831951] person: Name 1599831951 ver: 4506 
[30 - 1599831952] person: Name 1599831951 ver: 4506  > [30 - 1599831952] person: Name 1599831952 ver: 4507 
[32 - 1599831952] person: Name 1599831952 ver: 4507  > [32 - 1599831952] person: Name 1599831952 ver: 4508 
[31 - 1599831953] person: Name 1599831952 ver: 4508  > [31 - 1599831953] person: Name 1599831953 ver: 4509 
[34 - 1599831953] person: Name 1599831953 ver: 4509  > [34 - 1599831953] person: Name 1599831953 ver: 4510 
[33 - 1599831953] person: Name 1599831953 ver: 4510 [36 - 1599831953] person: Name 1599831953 ver: 4510  > [33 - 1599831953] person: Name 1599831953 ver: 4511 
 > [36 - 1599831953] person:  ver: 0 
[35 - 1599831953] person: Name 1599831953 ver: 4511  > [35 - 1599831953] person: Name 1599831953 ver: 4512 
[37 - 1599831953] person: Name 1599831953 ver: 4512  > [37 - 1599831953] person: Name 1599831953 ver: 4513 
...
[45 - 1599831955] person: Name 1599831955 ver: 4523  > [45 - 1599831955] person: Name 1599831955 ver: 4524 
...
[73 - 1599831960] person: Name 1599831960 ver: 4546  > [73 - 1599831960] person: Name 1599831960 ver: 4547 
---
[94 - 1599831964] person: Name 1599831964 ver: 4567 [92 - 1599831964] person: Name 1599831964 ver: 4567  > [94 - 1599831964] person: Name 1599831964 ver: 4568 
 > [92 - 1599831964] person:  ver: 0 
[93 - 1599831964] person: Name 1599831964 ver: 4568 [95 - 1599831964] person: Name 1599831964 ver: 4568  > [93 - 1599831964] person: Name 1599831964 ver: 4569 
 > [95 - 1599831964] person:  ver: 0 
[96 - 1599831964] person: Name 1599831964 ver: 4569  > [96 - 1599831964] person: Name 1599831964 ver: 4570 
...
[100 - 1599831964] person: Name 1599831964 ver: 4573  > [100 - 1599831964] person: Name 1599831964 ver: 4574 

person.sql

-- public.person definition

-- Drop table

-- DROP TABLE public.person;

CREATE TABLE public.person (
	first_name text NULL,
	last_name text NULL,
	email text NULL,
	"version" int4 NULL DEFAULT 0
);

-- data
INSERT INTO public.person (first_name,last_name,email,"version") VALUES 
('Name 1599831665','Doe','[email protected]',4470)
,('Jason','Moiron','[email protected]',0)
,('Jane','Citizen','[email protected]',0);