5. Getting Transactions and Locking Right | Troubleshooting PostgreSQL

Transaction isolation

In this section, you will be introduced to an important topic called transaction isolation. In my long career as a PostgreSQL database consultant, I have seen countless scenarios in which transaction isolation has caused enormous problems. Sometimes, people even desperately replaced their hardware to get around issues that were definitely not related to hardware.

Demonstrating read committed mode

So, what is transaction isolation? The idea behind transaction isolation is to give users a tool to control what they see inside a transaction. A report might have different requirements than a simple OLTP application, but let's not get lost in plan theory. Instead, let's look at a real example with some data:

test=# CREATE TABLE t_test (id int);
CREATE TABLE
test=# INSERT INTO t_test VALUES (4), (5);
INSERT 0 2

We've got two rows. Now let's see what happens if two transactions battle for the data at the same time:

User 1:	User 2:
BEGIN;
SELECT sum(id) FROM t_test; sum...

User 1:

User 2:

BEGIN;

SELECT sum(id) FROM t_test;
 sum...

Tech Concepts

Programming languages

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

Troubleshooting PostgreSQL

By : Hans-Jürgen Schönig

Troubleshooting PostgreSQL

By: Hans-Jürgen Schönig

Overview of this book

Transaction isolation

Demonstrating read committed mode

Troubleshooting PostgreSQL

By : Hans-Jürgen Schönig

Troubleshooting PostgreSQL

By: Hans-Jürgen Schönig

Overview of this book

Transaction isolation

Demonstrating read committed mode

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access