Introduction to Isolation and Locking

The main feature of any database system is its implementation of concurrency and full respect for the system’s constraints and properties when multiple transactions are modifying the state of the system at the same time.

Transaction isolation

PostgreSQL is fully ACID compliant and implements transactions isolation so that your application’s concurrency can be dealt with gracefully. Concurrency is a tricky and complex problem, and concurrency issues are often hard to reproduce. That’s why it’s best to rely on existing solutions for handling concurrency.

PostgreSQL and concurrency

Dealing with concurrency issues in programming languages usually involves proper declaration and use of lock, mutex, and semaphore facilities, which make clever use of atomic operations as supported by our CPU and sometimes provided by the operating system. Some programming languages, such as Java, offer synchronized blocks that, in turn, make use of previously listed low-level features. Other programming languages, such as Erlang, only implement message-passing facilities and handle concurrency internally (in a mailbox system) so that you don’t have to.

SQL is a declarative programming language, where our role as developers is to declare our intention: the result we want to achieve. The implementation is then tasked with implementing our command and making it right in every detail, including concurrency behavior.