PostgreSQL - Locks: A Beginner's Guide

Hello there, future database wizards! Today, we're going to embark on an exciting journey into the world of PostgreSQL locks. Don't worry if you've never written a line of code before - I'll be your friendly guide through this adventure, drawing from my years of teaching experience to make sure you understand every step of the way.

What are Locks?

Before we dive into the nitty-gritty, let's start with a simple analogy. Imagine you're in a library, and you want to borrow a book. You take it off the shelf, and while you're reading it, no one else can borrow that same book. That's essentially what a lock does in a database - it prevents multiple users from modifying the same data at the same time, which could lead to confusion and errors.

Types of Locks in PostgreSQL

PostgreSQL uses various types of locks to manage concurrent access to data. Here's a table summarizing the main types:

Lock Type	Description
Row-level locks	Protect individual rows from being modified simultaneously
Table-level locks	Protect entire tables from certain operations
Advisory locks	User-defined locks for application-specific purposes

Now, let's explore these in more detail!

Row-level Locks

Row-level locks are the most common type you'll encounter. They're automatically applied when you modify data in a table.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Row with id = 1 is now locked
COMMIT;

In this example, when you start updating the balance for account 1, PostgreSQL automatically locks that row. If another transaction tries to modify the same row before you commit, it will have to wait.

Table-level Locks

Table-level locks protect entire tables. They're less common but sometimes necessary for operations that affect the whole table.

BEGIN;
LOCK TABLE accounts IN EXCLUSIVE MODE;
-- The entire accounts table is now locked
UPDATE accounts SET interest_rate = interest_rate + 0.01;
COMMIT;

This code locks the entire accounts table, then updates all rows. It's like putting a "Do Not Disturb" sign on the whole bookshelf instead of just one book!

DeadLocks

Now, let's talk about something a bit trickier: deadlocks. Imagine two people each holding a book that the other person wants. Neither can continue reading until they get the other book, but neither will give up their current book. That's a deadlock!

In PostgreSQL, deadlocks can occur when two transactions are waiting for each other to release a lock. Here's an example:

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction 2 (running concurrently)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

If these transactions run at the same time, they might deadlock: Transaction 1 holds a lock on account 1 and waits for account 2, while Transaction 2 holds a lock on account 2 and waits for account 1.

Fortunately, PostgreSQL can detect deadlocks and will automatically cancel one of the transactions to resolve the situation. It's like a librarian stepping in to solve the book-swapping dilemma!

Advisory Locks

Last but not least, let's talk about advisory locks. These are special because you, the developer, get to decide when to use them. It's like creating your own "Do Not Disturb" sign in the library!

Here's how you might use an advisory lock:

-- Acquire an advisory lock
SELECT pg_advisory_lock(123);

-- Do some work here...

-- Release the lock
SELECT pg_advisory_unlock(123);

In this example, 123 is an arbitrary number you choose to identify your lock. Any other process that tries to acquire a lock with the same number will have to wait until you release it.

Advisory locks are great for coordinating activities in your application that aren't tied to specific database objects. For instance, you might use them to ensure only one instance of a batch job runs at a time.

Conclusion

And there you have it, folks! We've journeyed through the land of PostgreSQL locks, from the common row-level locks to the trickier concepts of deadlocks and the customizable advisory locks. Remember, locks are like the traffic lights of your database - they help keep everything running smoothly and prevent crashes.

As you continue your PostgreSQL adventure, you'll encounter more complex scenarios involving locks. But don't worry - with this foundation, you're well-prepared to tackle those challenges. Keep practicing, stay curious, and happy coding!

Credits: Image by storyset