Illustration composed from MariaLetta/free-gophers-pack , original gopher by Renee French. Transaction is a sequence of database operations, grouped as a single unit. All operations could be either committed or rolled back. Simplest example is balance transfer. In case of transfer between two accounts Alice and Bob, with balance, we need to subtract from Alice balance and increase Bob balance in one action. SQL code for this action would be something like this: ; balance = balance - = ; balance = balance + = ; ; BEGIN UPDATE users SET 10 WHERE name 'Alice' UPDATE users SET 10 WHERE name 'Bob' COMMIT I won't go deep in theory, how Postgres handles all this internally, but rather focus on Go examples. Go have two main not-orm libraries to work with Postgres and . Pgx is preferable and I gonna use it in examples. Although pg/lib supports ,  it is not maintained anymore and has some issues, like when error occurred, instead of returning an error. pg/lib jackc/pgx database/sql panic Let's take a lot at a little bit more complex example: Assume that we have a users table, each user has name, balance, and group_id. Seed table with 5 users, each with a balance of 100, split to 3 groups. ( , ,
		balance , ,
		PRIMARY ( )
	); ( , balance, ) ( , , ),
       ( , , ),
       ( , , ),
       ( , , ),
       ( , , ); CREATE TABLE users id serial name text integer group_id integer KEY id INSERT INTO users name group_id VALUES 'Bob' 100 1 'Alice' 100 1 'Eve' 100 2 'Mallory' 100 2 'Trent' 100 3 We need to read data to our program, do something with it, and then update, all in one ACID transaction. If someone else will try to update the same data concurrently, then the transaction would behave differently depends on its isolation level. Isolation levels It theory there 4 isolation levels, Postgres supports only 3 of them. And 4 phenomena, that different isolation levels should prevent. , , , and . Read uncommitted Read committed Repeatable read Serializable is equal to and is default isolation level in Postgres. Read uncommitted Read committed Isolation levels are targeted to prevent undesirable phenomena: dirty read, nonrepeatable read, phantom read, and serialization anomaly. Dirty read Basically it is reading of uncommitted changes from different transactions. All transactions are Postgres protected from dirty read, it is not possible to read changes, that not yet committed. Default level of isolation , which is equal to in Postgres. Read Committed Read uncommitted in different databases allows dirty read. Read uncommitted First, we need to prepare two separate connections to the same database, in order to send transactions with both of them concurrently: ctx = context.Background()

conn1, err := pgx.Connect(ctx, connString) err != {
	fmt.Fprintf(os.Stderr, , err)
	os.Exit( )
} conn1.Close(ctx)

conn2, err := pgx.Connect(ctx, connString) err != {
	fmt.Fprintf(os.Stderr, , err)
	os.Exit( )
} conn2.Close(ctx) if nil "Unable to connect to database: %v\n" 1 defer if nil "Unable to connect to database: %v\n" 1 defer Attempt to dirty read: Change Bob balance to 256 Inside main transaction. Read Bob balance from transaction. Read Bob balance with second connection. Commit transaction. If dirty read would be possible, then the results of reading on steps 2 and 3 would be the same. But since changes made inside the transaction, it's unavailable outside, before the commit is made. tx, err := conn1.Begin(ctx) err != { (err)
}
tx.Exec(ctx, +isolationLevel)

_, err = tx.Exec(ctx, ) err != {
	fmt.Printf( , err)
} balance row := tx.QueryRow(ctx, )
row.Scan(&balance)
fmt.Printf( , balance)

row = conn2.QueryRow(ctx, )
row.Scan(&balance)
fmt.Printf( , balance) err := tx.Commit(ctx); err != {
	fmt.Printf( , err)
} if nil panic "SET TRANSACTION ISOLATION LEVEL " "UPDATE users SET balance = 256 WHERE name='Bob'" if nil "Failed to update Bob balance in tx: %v\n" var int "SELECT balance FROM users WHERE name='Bob'" "Bob balance from main transaction after update: %d\n" "SELECT balance FROM users WHERE name='Bob'" "Bob balance from concurrent transaction: %d\n" if nil "Failed to commit: %v\n" For both isolation level results would be the same: Dirty read

Isolation level - READ UNCOMMITTED
Bob balance from main transaction after update: 256
Bob balance from concurrent transaction: 100
Final table state:
 1 |        Bob |   256 | 1
 2 |      Alice |   100 | 1
 3 |        Eve |   100 | 2
 4 |    Mallory |   100 | 2
 5 |      Trent |   100 | 3

Isolation level - READ COMMITTED
Bob balance from main transaction after update: 256
Bob balance from concurrent transaction: 100
Final table state:
 1 |        Bob |   256 | 1
 2 |      Alice |   100 | 1
 3 |        Eve |   100 | 2
 4 |    Mallory |   100 | 2
 5 |      Trent |   100 | 3 Nonrepeatable read Transaction read some values from rows, and those values could be changes by concurrent translations, before the transaction ends. To prevent this, isolation level read all these values again before the transaction is committed, and cancels transaction if values differs from initial ones. Otherwise if data changes are ignored, it is situation. Repeatable read nonrepeatable read Test with , isolation levels: Read committed Repeatable read Read Bob balance from transaction. Change Bob balance to 1000 with a second connection. Change Bob balance to 110 from transaction. Commit transaction. Transaction with simply ignores concurrent changes and overwrites them. With Postgres detects concurrent changes on step 3 and stops transaction. Read committed Repeatable read tx, err := conn1.Begin(ctx) err != { (err)
}
tx.Exec(ctx, +isolationLevel)

row := tx.QueryRow(ctx, ) balance row.Scan(&balance)
fmt.Printf( , balance)

fmt.Printf( )
_, err = conn2.Exec(ctx, ) err != {
	fmt.Printf( , err)
}

_, err = tx.Exec(ctx, , balance+ ) err != {
	fmt.Printf( , err)
} err := tx.Commit(ctx); err != {
	fmt.Printf( , err)
} if nil panic "SET TRANSACTION ISOLATION LEVEL " "SELECT balance FROM users WHERE name='Bob'" var int "Bob balance at the beginning of transaction: %d\n" "Updating Bob balance to 1000 from connection 2\n" "UPDATE users SET balance = 1000 WHERE name='Bob'" if nil "Failed to update Bob balance from conn2  %e" "UPDATE users SET balance = $1 WHERE name='Bob'" 10 if nil "Failed to update Bob balance in tx: %v\n" if nil "Failed to commit: %v\n" Results are different, in the second case transaction failed: Nonrepeatable read

Isolation level - READ COMMITTED
Bob balance at the beginning of transaction: 100
Updating Bob balance to 1000 from connection 2
Final table state:
 1 |        Bob |   110 | 1
 2 |      Alice |   100 | 1
 3 |        Eve |   100 | 2
 4 |    Mallory |   100 | 2
 5 |      Trent |   100 | 3

Isolation level - REPEATABLE READ
Bob balance at the beginning of transaction: 100
Updating Bob balance to 1000 from connection 2
Failed to update Bob balance in tx: ERROR: could not serialize access due to concurrent update (SQLSTATE 40001)
Failed to commit: commit unexpectedly resulted in rollback
Final table state:
 1 |        Bob |  1000 | 1
 2 |      Alice |   100 | 1
 3 |        Eve |   100 | 2
 4 |    Mallory |   100 | 2
 5 |      Trent |   100 | 3 Phantom read Phantom read is similar to , but it is about that was selected within the transaction. If with external changes, a set of rows also changes this is situation. level prevents it in Postgres. nonrepeatable read a set of   rows phantom read Repeatable read Test with , isolation levels: Read committed Repeatable read Read users with group_id=2 from the transaction. Move Bob to group 2  with a second connection. Read users with group_id=2 from the transaction again. Update selected users balances by +15. Commit transaction. Transaction with will read different rows on steps 1 and 3. With Postgres will save data from the beginning of transaction and 1 and 3 reads will return the same set of rows, isolated from concurrent changes. Read committed Repeatable read tx, err := conn1.Begin(ctx) err != { (err)
}
tx.Exec(ctx, +isolationLevel) users []User user User
rows, _ := tx.Query(ctx, ) rows.Next() { user User
	rows.Scan(&user.Name, &user.Balance)
	users = (users, user)
}
fmt.Printf( , users)

fmt.Printf( )
conn2.Exec(ctx, )

users = []User{}
rows, _ = tx.Query(ctx, ) rows.Next() {
	rows.Scan(&user.Name, &user.Balance)
	users = (users, user)
}
fmt.Printf( , users)

fmt.Printf( ) _, user := users {
	_, err = tx.Exec(ctx, , user.Balance+ , user.Name) err != {
		fmt.Printf( , err)
	}
} err := tx.Commit(ctx); err != {
	fmt.Printf( , err)
} if nil panic "SET TRANSACTION ISOLATION LEVEL " var var "SELECT name, balance FROM users WHERE group_id = 2" for var append "Users in group 2 at the beginning of transaction:\n%v\n" "Cuncurrent transaction moves Bob to group 2\n" "UPDATE users SET group_id = 2 WHERE name='Bob'" "SELECT name, balance FROM users WHERE group_id = 2" for append "Users in group 2 after cuncurrent transaction:\n%v\n" "Update selected users balances by +15\n" for range "UPDATE users SET balance = $1 WHERE name=$2" 15 if nil "Failed to update in tx: %v\n" if nil "Failed to commit: %v\n" Results are different, based on the second select, different users affected by the upgrade: Phantom read

Isolation level - READ COMMITTED
Users in group 2 at the beginning of transaction:
[{Eve 100} {Mallory 100}]
Cuncurrent transaction moves Bob to group 2
Users in group 2 after cuncurrent transaction:
[{Eve 100} {Mallory 100} {Bob 100}]
Update selected users balances by +15
Final table state:
 1 |        Bob |   115 | 2
 2 |      Alice |   100 | 1
 3 |        Eve |   115 | 2
 4 |    Mallory |   115 | 2
 5 |      Trent |   100 | 3

Isolation level - REPEATABLE READ
Users in group 2 at the beginning of transaction:
[{Eve 100} {Mallory 100}]
Cuncurrent transaction moves Bob to group 2
Users in group 2 after cuncurrent transaction:
[{Eve 100} {Mallory 100}]
Update selected users balances by +15
Final table state:
 1 |        Bob |   100 | 2
 2 |      Alice |   100 | 1
 3 |        Eve |   115 | 2
 4 |    Mallory |   115 | 2
 5 |      Trent |   100 | 3 Serialization anomaly Let's assume that we have several concurrent transactions in progress, both do some reading and writing with a table. In case if the final table state will depend on the order of running and committing these transactions, then it is . Serialization anomaly In this case results could be affected by race conditions. Isolation level help prevents this type of issue. I'll have to say, that even with this serialization level, some rare cases could still cause this phenomena. Serializable Test with and isolation levels: Repeatable read Serializable Start the second transaction with a second connection. Set second transaction isolation level the same to the main transaction. Read the sum of users balances with group_id=2 from transaction 1. Move Bob to group 2  with transaction 2. Read users with group_id=2 from transaction 1. Update selected users balances by +sum from 1 action. Commit the main transaction. Commit second transaction. Transactions with both will be committed without errors. With isolation level second transaction won't be committed. These two transactions work with the same data and the order of commits will affect results, which could lead to unpredictable outcomes. Postgres would prevent the commit of the second transaction, to prevent this uncertainty. Repeatable read Serializable tx, err := conn1.Begin(ctx) err != { (err)
}
tx.Exec(ctx, +isolationLevel)

tx2, err := conn2.Begin(ctx) err != { (err)
}
tx2.Exec(ctx, +isolationLevel) sum row := tx.QueryRow(ctx, )
row.Scan(&sum)

tx2.Exec(ctx, ) err != {
	fmt.Printf( , err)
}

rows, _ := tx.Query(ctx, ) User {
	Name Balance } users []User rows.Next() { user User
	rows.Scan(&user.Name, &user.Balance)
	users = (users, user)
} _, user := users {
	_, err = tx.Exec(ctx, , user.Balance+sum, user.Name) err != {
		fmt.Printf( , err)
	}
} err := tx.Commit(ctx); err != {
	fmt.Printf( , err)
} err := tx2.Commit(ctx); err != {
	fmt.Printf( , err)
} if nil panic "SET TRANSACTION ISOLATION LEVEL " if nil panic "SET TRANSACTION ISOLATION LEVEL " var int "SELECT SUM(balance) FROM users WHERE group_id = 2" "UPDATE users SET group_id = 2 WHERE name='Bob'" if nil "Error in tx2: %v\n" "SELECT name, balance FROM users WHERE group_id = 2" type struct string int var for var append for range "UPDATE users SET balance = $1 WHERE name=$2" if nil "Failed to update in tx: %v\n" if nil "Failed to commit tx: %v\n" if nil "Failed to commit tx2: %v\n" In second case transaction failed with error: "could not serialize access due to read/write dependencies among transactions" Serialization anomaly

Isolation level - REPEATABLE READ
Final table state:
 1 |        Bob |   100 | 2
 2 |      Alice |   100 | 1
 3 |        Eve |   300 | 2
 4 |    Mallory |   300 | 2
 5 |      Trent |   100 | 3

Isolation level - SERIALIZABLE
Failed to commit tx2: ERROR: could not serialize access due to read/write dependencies among transactions (SQLSTATE 40001)
Final table state:
 1 |        Bob |   100 | 1
 2 |      Alice |   100 | 1
 3 |        Eve |   300 | 2
 4 |    Mallory |   300 | 2
 5 |      Trent |   100 | 3 Conclusion When you have multiple connections and concurrent access to Postgres database, choose the isolation level carefully. Higher isolation levels provides safety, but reduces performance. Also, you should check, that transaction has been committed successfully, and repeat if necessary. All examples from the article: https://github.com/kochetkov-av/go-postgresql-transaction-isolation

Different

Read My Stories

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

PostgreSQL Transaction Isolation Levels with Go Examples

Too Long; Didn't Read

Company Mentioned

Aleksandr Kochetkov

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

PostgreSQL Transaction Isolation Levels with Go Examples

Too Long; Didn't Read

Company Mentioned

Aleksandr Kochetkov

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES