CODEWITHSUNDEEP
postgresqlgotransactionsisolation-levelgo-gorm
robbieperry22
robbieperry22

Reputation: 2245

Transaction viewing changes to database rows made after it began

I've discovered something I can't wrap my head around when working with Postgres and Go GORM.

In the following code snippet, I try and Find, Delete, and Create the same item, but in 2 different transactions, however I begin the second transaction and Find the item BEFORE committing the first transaction.

In the second transaction, something strange happens. When I call t2.Delete(), it doesn't Delete any rows. In the database, after calling t1.Commit(), the primary key of the item becomes 2, but since it already got the old item, it Deletes for the primary key 1.

Why does transaction 2 see the new row, despite being created BEFORE committing the first?

db, err := gorm.Open("postgres", "host=localHost port=5432 user=postgres dbname=test password=postgres sslmode=disable")
defer db.Close()
db.DropTableIfExists(&Product{})
db.AutoMigrate(&Product{})
db.LogMode(true)

db.Create(&Product{Code: "A", Price: 1000})
// SQL: INSERT  INTO "products" ("code","price") VALUES ('A',1000) RETURNING "products"."id"

// Start transaction 1, find item, delete it
t1 := db.Begin()
product := &Product{}

err = t1.Find(product, "code = ?", "A").Error
// SQL: SELECT * FROM "products"  WHERE (code = 'A')

err = t1.Delete(product).Error
// SQL: DELETE FROM "products"  WHERE "products"."id" = 1

// Start transaction 2 and get item, before transaction 1 creates new item and commits
t2 := db.Begin()

product2 := &Product{}
err = t2.Find(product2, "code = ?", "A").Error
// SQL: SELECT * FROM "products"  WHERE (code = 'A')

err = t1.Create(&Product{Code: "A", Price: 3000}).Error
// SQL: INSERT  INTO "products" ("code","price") VALUES ('A',3000) RETURNING "products"."id"

err = t1.Commit().Error
// Database now contains

err = t2.Delete(product2).Error
// SQL: DELETE FROM "products"  WHERE "products"."id" = 1
// [0 rows affected or returned ]

err = t2.Save(&Product{Code: "A", Price: 4000}).Error
// SQL: INSERT  INTO "products" ("code","price") VALUES ('A',4000) RETURNING "products"."id"
// ERROR HERE: pq: duplicate key value violates unique constraint "products_code_key"

err = t2.Commit().Error

Note: GORM uses by default Read Committed isolation level. I'm aware of the integrity issues that this would cause, if it worked correctly. I'll instead be using Serializable, which would give an error on Commit, or would block on Get, if specified "FOR UPDATE;"

Upvotes: 1

Views: 2507

Answers (1)

Brits
Brits

Reputation: 18380

From the PostgreSQL docs

Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions. In effect, a SELECT query sees a snapshot of the database as of the instant the query begins to run.

...

Also note that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes after the first SELECT starts and before the second SELECT starts.

The second paragraph seems to sum up your situation. Any "SELECT" in t2 that is started after t1 is committed can see the updates applied by t1.

Upvotes: 1

Related Questions