Tag: concurrency
Row locks in PostgreSQL
© Laurenz Albe 2023 The PostgreSQL documentation has some information about row locks. But typically, you cannot see them in pg_locks, and not everybody knows how they work and how to track and debug row locks. This article intends to give some insight into how PostgreSQL row locks work “under the hood”. Why are there […]
Transaction anomalies with SELECT FOR UPDATE
© Laurenz Albe 2022 I was recently confronted with a nice example of how adding FOR UPDATE to a query can introduce transaction anomalies. This article will explain how that happens and how you can cope with the problem. Be ready to learn some PostgreSQL implementation details! The example Setting the stage We have a […]
Fixing out-of-sync sequences in PostgreSQL
Creating auto increment columns in PostgreSQL is easy. Simply use two pseudo data types serial and serial8, respectively, then PostgreSQL will automatically take care of your auto increment columns. However, once in a while problems can still occur. Let us take a look and see. Sequences: Avoid manual values To understand the underlying problem, one […]
Subtransactions and performance in PostgreSQL
© Laurenz Albe 2020 (Last updated on 2023-01-24) Recently, while troubleshooting PostgreSQL performance problems, I ran into problems with subtransactions twice. So I thought this was a nice topic for another blog post. What are subtransactions? Everybody knows database transactions. In PostgreSQL, which is operating in autocommit mode, you have to start a transaction […]
“LOCK TABLE” can harm your database’s health
© Laurenz Albe 2019 Many people know that explicit table locks with LOCK TABLE are bad style and usually a consequence of bad design. The main reason is that they hamper concurrency and hence performance. Through a recent support case I learned that there are even worse effects of explicit table locks. Table locks […]
What’s in an xmax?
xmax is a PostgreSQL system column that is used to implement Multiversion Concurrency Control (MVCC). The documentation is somewhat terse: The identity (transaction ID) of the deleting transaction, or zero for an undeleted row version. It is possible for this column to be nonzero in a visible row version. That usually indicates that the deleting […]