PostgreSQL Performance & Query Optimization · 课时

行级锁定策略

探索管理行级锁的高级策略，优化并发写入

第 3 / 4 课12 个步骤

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

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

Why Row-Level Locking?

When multiple users or processes try to change the same data at the same time, databases need a way to prevent conflicts and ensure data integrity. This is where row-level locking comes in.

A row-level lock allows a transaction to claim exclusive or shared access to specific rows, preventing other transactions from making conflicting changes until the lock is released. It's crucial for high-concurrency applications.

Implicit Row Locks

PostgreSQL automatically applies row-level locks during Data Manipulation Language (DML) operations like INSERT, UPDATE, and DELETE.

INSERT: Places an exclusive lock on the newly inserted row.
UPDATE: Places an exclusive lock on the row being modified.
DELETE: Places an exclusive lock on the row being deleted.

These implicit locks ensure that only one transaction can modify a specific row at a time.

Explicit Locks: FOR UPDATE

Sometimes you need to lock rows before modifying them, especially when your application logic involves reading data, making decisions, and then updating. This is where SELECT ... FOR UPDATE is invaluable.

It acquires an exclusive lock on the selected rows, preventing other transactions from updating or deleting them until your transaction commits or rolls back.

FOR UPDATE in Action

Try this example. If you run SELECT ... FOR UPDATE in one database session, then try to UPDATE the same row from another session, the second session will wait.

Session 1:

BEGIN;
SELECT * FROM products WHERE product_id = 1 FOR UPDATE;
-- Do some work...
-- UPDATE products SET stock = stock - 1 WHERE product_id = 1;
-- ROLLBACK; OR COMMIT;

FOR UPDATE: What Happens

The previous code snippet shows how FOR UPDATE works. If you ran the SELECT in Session 1, then immediately tried to run this UPDATE in a different Session 2, Session 2 would wait until Session 1 either COMMITs or ROLLBACKs.

Session 2 (will wait):

UPDATE products SET price = 10.99 WHERE product_id = 1;

Explicit Locks: FOR SHARE

What if you want to prevent updates, but allow other transactions to read the data or even acquire their own shared lock?

SELECT ... FOR SHARE acquires a shared lock. This means:

Other transactions can read the rows.
Other transactions can acquire their own FOR SHARE locks.
Other transactions cannot acquire FOR UPDATE locks or modify the rows.

FOR SHARE in Action

If Session 1 holds a FOR SHARE lock, Session 2 can also acquire a FOR SHARE lock, but a FOR UPDATE or DML operation on the same row will wait.

Session 1:

BEGIN;
SELECT * FROM orders WHERE order_id = 101 FOR SHARE;
-- Do some calculations...
-- COMMIT; OR ROLLBACK;

More Granular Locks: FOR NO KEY UPDATE

SELECT ... FOR NO KEY UPDATE is similar to FOR UPDATE but is weaker. It acquires an exclusive lock that doesn't block FOR KEY SHARE locks.

It's useful when you're updating non-key columns and don't need to prevent concurrent foreign key operations, which are typically very short-lived.

Shared Read Locks: FOR KEY SHARE

SELECT ... FOR KEY SHARE is the weakest explicit row-level lock. It allows other transactions to acquire FOR SHARE, FOR NO KEY UPDATE, and even other FOR KEY SHARE locks.

It primarily prevents other transactions from deleting the locked rows or acquiring an exclusive lock that would modify key columns. It's often used by foreign key constraints.

Locking Order Strategy

A critical strategy to prevent deadlocks (where two transactions wait for each other indefinitely) is to always acquire locks on multiple rows in a consistent order.

For example, if you need to lock rows with product_id = 5 and product_id = 10, always lock 5 first, then 10 across all transactions. This prevents a scenario where one transaction locks 5 then tries for 10, while another locks 10 then tries for 5.

Quick Check: Row Locks

Consider two concurrent transactions. Transaction A runs SELECT * FROM users WHERE user_id = 1 FOR UPDATE;. What happens if Transaction B immediately tries to run UPDATE users SET email = 'new@example.com' WHERE user_id = 1;?

在交互式课程中回答

Recap: Row-Level Locks

We've explored how PostgreSQL manages concurrency with row-level locks:

Implicit locks protect DML operations.
FOR UPDATE provides exclusive row locks for modifications.
FOR SHARE provides shared locks, allowing reads but blocking updates.
FOR NO KEY UPDATE and FOR KEY SHARE offer more granular control.
Consistently ordering lock acquisition is a key strategy to prevent deadlocks.

Mastering these strategies ensures your application handles concurrent writes efficiently and reliably!

免费开始

用 AI 导师学习 SQL — 免费

在浏览器中编写并运行真实代码，获得全天候 AI 导师的即时帮助，并在网页或应用中继续学习。

课程: 22
课程: 88

免费开始学习查看完整课程

常见问题解答

「行级锁定策略」课时是免费的吗？

是的 — 「行级锁定策略」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 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 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 3 节课，共 4 节。

「行级锁定策略」课时需要多长时间？

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

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

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

此课程中的所有课时

← 返回 PostgreSQL Performance & Query Optimization