PostgreSQL Performance & Query Optimization · 课时

识别并解决锁竞争

学习诊断和缓解锁竞争的实用方法，确保数据库操作顺畅进行

识别并解决锁竞争 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。这是第 2 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

What is Lock Contention?

Imagine a busy road. When multiple cars try to use the same lane at the same time, traffic slows down or stops. In PostgreSQL, this 'traffic jam' is called lock contention.

It happens when one transaction holds a lock on a resource (like a row or table) that another transaction needs. The second transaction then has to wait for the first one to release its lock.

The Cost of Contention

Lock contention isn't just an inconvenience; it can severely impact your database's performance and application responsiveness. Here's how:

Increased Query Latency: Queries take longer to complete.
Reduced Throughput: The database processes fewer transactions per second.
Application Timeouts: Frontend applications might time out waiting for a database response.
Resource Waste: Waiting sessions consume server resources without making progress.

Identifying Waits with pg_locks

PostgreSQL provides built-in tools to help us spot contention. The pg_locks system view is your first stop. It shows all active locks held or awaited by backend processes.

Key columns to watch are pid (process ID), locktype, relation (the object being locked), mode (the type of lock), and especially granted.

Spotting Waiting Sessions

A granted = false value in pg_locks indicates a session that is currently waiting for a lock. Let's see how to query for these waiting sessions:

SELECT
  pid,
  locktype,
  relation::regclass AS locked_object,
  mode,
  granted
FROM pg_locks
WHERE granted = false;

Finding the Blocker with pg_stat_activity

Once you've identified a waiting session using pg_locks, the next step is to find out who is holding the lock and preventing it from proceeding. This is where pg_stat_activity comes in.

This view gives you details about all active sessions, including their current query, state, and when they started.

Querying for Blocking Queries

By combining information from pg_locks and pg_stat_activity, we can pinpoint blocking queries. Here's a simplified query to find active queries that might be causing contention:

SELECT
  pid,
  usename,
  application_name,
  client_addr,
  query_start,
  state,
  query
FROM pg_stat_activity
WHERE state = 'active'
  AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start ASC
LIMIT 5;

Common Causes of Contention

Understanding the root causes helps in prevention:

Long-Running Transactions: Transactions that hold locks for extended periods.
Missing Indexes: Forgetting an index can lead to full table scans, acquiring more locks than necessary.
DDL Operations: Commands like ALTER TABLE often require exclusive table locks.
'Hot Rows' / 'Hot Pages': Frequent updates or deletions on the same few rows or data blocks.

Resolution: Shorten Transactions

One of the most effective strategies is to keep your database transactions as short and efficient as possible. This means:

Commit Frequently: Don't hold locks longer than needed.
Batch Operations: Break down large operations into smaller, manageable chunks.
Optimize Queries: Ensure SQL queries within transactions are highly optimized and use appropriate indexes.

Resolution: Timeouts & Skipping Locks

Sometimes, waiting indefinitely isn't an option. PostgreSQL offers ways to manage this:

SET lock_timeout: Prevents queries from waiting forever. The query will error out if it can't acquire a lock within the specified time.
FOR UPDATE SKIP LOCKED: For specific use cases (like processing a queue), this clause allows a query to skip rows that are currently locked by other transactions, rather than waiting.

Check Your Knowledge

Which of the following are effective strategies for identifying or resolving lock contention in PostgreSQL?

Recap & Next Steps

Great job! You've learned how to identify and begin resolving lock contention in PostgreSQL. We covered:

What lock contention is and its performance impact.
Using pg_locks and pg_stat_activity to find waiting sessions and their blockers.
Common causes like long transactions and missing indexes.
Key resolution strategies: shortening transactions, optimizing queries, and using lock_timeout or SKIP LOCKED.

Next, we'll dive deeper into advanced row-level locking strategies to further optimize concurrent writes!

常见问题解答

「识别并解决锁竞争」课时是免费的吗？

是的 — 「识别并解决锁竞争」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「识别并解决锁竞争」这节课中我会学到什么？

学习诊断和缓解锁竞争的实用方法，确保数据库操作顺畅进行你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 PostgreSQL Performance & Query Optimization 需要有经验吗？

无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 2 节课，共 4 节。

「识别并解决锁竞争」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？

能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 PostgreSQL Performance & Query Optimization