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!

以下是繁體中文的翻譯：

PostgreSQL - Locks：初學者指南

你好，未來的數據庫大師！今天，我們將踏上一段令人興奮的旅程，探索 PostgreSQL 的鎖定世界。別擔心如果你從來沒有寫過一行代碼——我將成為你這次冒險中的友好導遊，憑藉我多年的教學經驗，確保你能夠理解每一步。

Locks 是什麼？

在我們深入細節之前，讓我們從一個簡單的比喻開始。想像你在一個圖書館裡，你想借一本書。你把它從書架上拿走，當你在讀它時，其他人不能借這本書。這就是數據庫中鎖定的作用——它防止多個用戶同時修改相同數據，這可能會導致混亂和錯誤。

PostgreSQL 中的鎖定類型

PostgreSQL 使用各種類型的鎖定來管理數據的並發訪問。這裡有一個總結主要類型的表格：

鎖定類型	描述
行級鎖定	保護單個行不被同時修改
表級鎖定	保護整個表免受某些操作影響
咨詢鎖定	為應用程序特定目的而定義的用戶鎖定

現在，讓我們詳細探討這些！

行級鎖定

行級鎖定是你最常遇到的類型。當你修改表中的數據時，它會自動應用。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- id = 1 的行現在已被鎖定
COMMIT;

在這個例子中，當你開始更新賬戶 1 的餘額時，PostgreSQL 會自動鎖定該行。如果另一個交易在提交之前嘗試修改同一行，它將必須等待。

表級鎖定

表級鎖定保護整個表。它們較不常見，但有時對影響整個表的操作是必要的。

BEGIN;
LOCK TABLE accounts IN EXCLUSIVE MODE;
-- 整個 accounts 表現在已被鎖定
UPDATE accounts SET interest_rate = interest_rate + 0.01;
COMMIT;

這段代碼鎖定了整個 accounts 表，然後更新所有行。這就像在整個書架上而不是只在一本書上貼上“請勿打擾”的標誌！

死鎖

現在，讓我們來討論一個稍顯複雜的問題：死鎖。想像有兩個人，每個人手中都拿著對方想要的書。在得到對方的書之前，誰都不能繼續閱讀，但誰也不願放棄手中的書。這就是死鎖！

在 PostgreSQL 中，當兩個交易相互等待對方釋放鎖定時，可能會發生死鎖。這裡有一個例子：

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

-- 交易 2（並發執行）
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

如果這些交易同時運行，它們可能會發生死鎖：交易 1 在賬戶 1 上持有鎖定並等待賬戶 2，而交易 2 在賬戶 2 上持有鎖定並等待賬戶 1。

幸運的是，PostgreSQL 可以偵測死鎖，並將自動取消一個交易以解決問題。這就像圖書館的管理員介入解決書籍交換的困境！

咨詢鎖定

最後但同樣重要的是，讓我們來討論一下咨詢鎖定。這些鎖定很特別，因為你可以，作為開發者，決定何時使用它們。這就像在圖書館中創造自己的“請勿打擾”標誌！

以下是如何使用咨詢鎖定的一個例子：

-- 獲取咨詢鎖定
SELECT pg_advisory_lock(123);

-- 在這裡做一些工作...

-- 釋放鎖定
SELECT pg_advisory_unlock(123);

在這個例子中，123 是你選擇用來標識鎖定的任意數字。任何嘗試獲取相同數字鎖定的其他進程將必須等待你釋放它。

咨詢鎖定對於協調應用程序中與特定數據庫對象無關的活動非常有效。例如，你可能會用它來確保同一時間只運行一個批處理任務的實例。

結論

好了，各位！我們已經穿越了 PostgreSQL 鎖定的領地，從常見的行級鎖定到複雜的死鎖概念，以及可定制的咨詢鎖定。記住，鎖定就像數據庫中的交通燈——它們幫助保持一切運行順暢並防止碰撞。

隨著你繼續 PostgreSQL 的冒險，你將會遇到涉及鎖定的更複雜情節。但別擔心——有了這個基礎，你已經準備好應對那些挑戰。持續練習，保持好奇心，並且開心編程！

Credits: Image by storyset