PostgreSQL中的锁:2.行级锁

本文探讨了PostgreSQL中的行级锁机制,包括行级锁的组织、独家模式、共享模式、多重交易以及冻结设置。PostgreSQL通过在数据页内存储锁定信息,实现了资源的有效管理和并发控制,同时介绍了锁升级的概念。文章还讨论了如何处理共享锁和独占锁的冲突,并通过实例展示了等待队列的工作原理,以及如何避免锁定饥饿。最后,提到了NOWAIT和SKIP LOCKED选项在避免等待和处理队列时的应用。
摘要由CSDN通过智能技术生成
object-level locks and in particular relation-level locks. In this article, we will see how row-level locks are organized in PostgreSQL and how they are used together with object-level locks. We will also talk of wait queues and of those who jumps the queue. 对象级锁 ,尤其是关系级锁。 在本文中,我们将了解PostgreSQL中行级锁的组织方式以及它们如何与对象级锁一起使用。 我们还将讨论等待队列和跳过队列的人。

行级锁 (Row-level locks)

组织 (Organization)

Let's recall a few weighty conclusions of the previous article.

让我们回想一下上一篇文章的一些重要结论。

  • A lock must be available somewhere in the shared memory of the server.

    服务器共享内存中的某个位置必须有锁。
  • The higher granularity of locks, the lower the contention among concurrent processes.

    锁的粒度越高,并发进程之间的争用就越少。
  • On the other hand, the higher the granularity, the more of the memory is occupied by locks.

    另一方面,粒度越高,锁占用的内存越多。

There is no doubt that we want a change of one row not block other rows of the same table. But we cannot afford to have its own lock for each row either.

毫无疑问,我们希望更改一行而不阻塞同一表的其他行。 但是我们也不能为每一行都拥有自己的锁。

There are different approaches to solving this problem. Some database management systems apply escalation of locks: if the number of row-level locks gets too high, they are replaced with one, more general lock (for example: a page-level or an entire table-level).

解决此问题有不同的方法。 某些数据库管理系统会应用锁升级:如果行级锁的数量过多,则将它们替换为一个更通用的锁(例如:页面级或整个表级)。

As we will see later, PostgreSQL also applies this technique, but only for predicate locks. The situation with row-level locks is different.

稍后我们将看到,PostgreSQL也应用了该技术,但仅用于谓词锁。 行级锁的情况有所不同。

PostgreSQL stores information that a row is locked only and exclusively in the row version inside the data page (and not in RAM). It means that it is not a lock in a usual sense, but just some indicator. Actually, the ID of the xmax transaction, along with additional information bits, serves as the indicator; we will look at how this is organized in detail a little later.

PostgreSQL仅在数据页内的行版本中 (而不是在RAM中)存储有关行被锁定的信息。 这意味着它不是通常意义上的锁,而只是一些指示器。 实际上, xmax事务的ID以及附加的信息位用作指示符。 稍后我们将详细研究它的组织方式。

A pro is that we can lock as many rows as we want without consuming any resources.

一个优点是,我们可以锁定任意数量的行,而不会消耗任何资源。

But there is also a con: since the information on the lock is not available in RAM, other processes cannot be queued. And monitoring is impossible either (to count the locks, all the table needs to be read).

但是也有一个缺点 :由于关于锁的信息在RAM中不可用,因此其他进程无法排队。 而且监视也是不可能的(要计算锁,需要读取所有表)。

Let alone monitoring, but something needs to be done for the queue. To this end, «normal» locks have yet to be used. If we need to wait for a row to be released, we actually need to wait until completion of the locking transaction: all locks are released at the transaction commit or roll back. And to this end, we can request a lock on the ID of the locking transaction (to remind you, the transaction itself holds this lock in an exclusive mode). So, the number of locks used is proportional to the number of simultaneously running processes rather than to the number of rows being updated.

更不用说监视了,但是需要对队列做一些事情。 为此,尚未使用“普通”锁。 如果我们需要等待一行被释放,那么实际上我们需要等待直到锁定事务完成:在事务提交或回滚时释放所有锁。 为此,我们可以请求对锁定事务的ID进行锁定(提醒您,事务本身以独占模式持有此锁定)。 因此,使用的锁数与同时运行的进程数成正比,而不与要更新的行数成正比。

独家模式 (Exclusive modes)

There are 4 modes that allow locking a row. Two of them are exclusive locks, which only one transaction can hold at a time.

有4种模式可以锁定一行。 其中两个是互斥锁,一次只能保留一个事务。

  • FOR UPDATE mode assumes a total change (or delete) of a row.

    FOR UPDATE模式假定一行的总更改(或删除)。
  • FOR NO KEY UPDATE mode assumes a change only to the fields that are not involved in unique indexes (in other words, this change does not affect foreign keys).

    FOR NO KEY UPDATE模式仅假定对唯一索引中不涉及的字段进行了更改(换句话说,此更改不影响外键)。

The UPDATE command itself selects the minimum appropriate locking mode; rows are usually locked in the FOR NO KEY UPDATE mode.

UPDATE命令本身会选择最小的适当锁定模式。 通常在FOR NO KEY UPDATE模式下锁定行。

As you remember, when a row is updated or deleted, the ID of the current transaction is written to the xmax field of the current up-to-date version. This ID shows that the transaction deleted the tuple. And this very number xmax is used to indicate a lock. Indeed, if xmax in a tuple matches an active (not yet completed) transaction and we want to update this very row, we need to wait until the transaction completes, and no additional indicator is needed.

如您所记得 ,当更新或删除一行时,当前事务的ID将被写入当前最新版本的xmax字段中。 此ID显示事务删除了元组。 这个非常大的数字xmax用于表示锁定。 确实,如果元组中的xmax与活动的(尚未完成)交易匹配,并且我们要更新这一行,则需要等到交易完成后才需要其他指示符。

Let's take a look. And let's create a table of accounts, same as in the last article.

让我们来看看。 让我们创建一个帐户表,与上一篇文章相同。

=> CREATE TABLE accounts(
  acc_no integer PRIMARY KEY,
  amount numeric
);
=> INSERT INTO accounts
  VALUES (1, 100.00), (2, 200.00), (3, 300.00);

To look into pages, we need a pretty familiar pageinspect extension.

要浏览页面,我们需要一个非常熟悉的pageinspect扩展。

=> CREATE EXTENSION pageinspect;

For convenience, we will create a view that shows only the information of interest: xmax and some information bits.

为了方便起见,我们将创建一个仅显示感兴趣的信息的视图: xmax和一些信息位。

=> CREATE VIEW accounts_v AS
SELECT '(0,'||lp||')' AS ctid,
       t_xmax as xmax,
       CASE WHEN (t_infomask & 128) > 0   THEN 't' END AS lock_only,
       CASE WHEN (t_infomask & 4096) > 0  THEN 't' END AS is_multi,
       CASE WHEN (t_infomask2 & 8192) > 0 THEN 't' END AS keys_upd,
       CASE WHEN (t_infomask & 16) > 0 THEN 't' END AS keyshr_lock,
       CASE WHEN (t_infomask & 16+64) = 16+64 THEN 't' END AS shr_lock
FROM heap_page_items(get_raw_page('accounts',0))
ORDER BY lp;

So, we start a transaction and update the amount in the first account (the key is unchanged) and the number of the second account (the key is changed):

因此,我们开始交易并更新第一个帐户中的金额(密钥不变)和第二个帐户的编号(密钥已更改):

=> BEGIN;
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
=> UPDATE accounts SET acc_no = 20 WHERE acc_no = 2;

Let's look into the view:

让我们看一下视图:

=> SELECT * FROM accounts_v LIMIT 2;
ctid  |  xmax  | lock_only | is_multi | keys_upd | keyshr_lock | shr_lock 
-------+--------+-----------+----------+----------+-------------+----------
 (0,1) | 530492 |           |          |          |             | 
 (0,2) | 
  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
`org.postgresql.util.PSQLException: ERROR: deadlock detected` 错误表示在您的PostgreSQL数据库发生了死情况。死是指两个或多个事务互相等待对方释放资源而无法继续执行的情况。 解决死问题的常见方法包括: 1. 分析死日志:在PostgreSQL,您可以查看日志文件以获取有关死的详细信息。查找死日志和相关事务的详细信息可能有助于确定问题的原因和解决方案。 2. 优化事务并发性:通过减少事务之间的竞争来降低出现死的可能性。这可以通过设计更合理的事务逻辑、调整事务隔离级别、降低事务持续时间等方式来实现。 3. 调整数据库配置参数:某些数据库配置参数可以影响死发生的频率。例如,您可以尝试增加`max_locks_per_transaction`参数的值,以便每个事务可以持有更多的。请注意,调整配置参数可能需要谨慎操作,并且需要根据具体情况进行测试和评估。 4. 重新设计应用程序逻辑:有时,死问题是由于应用程序的设计问题引起的。检查应用程序的并发访问模式和使用情况,考虑重新设计或优化以减少死风险。 5. 使用合适的机制:根据具体情况,可以考虑使用不同类型的机制来避免死。例如,使用行级而不是表级,或者使用乐观机制。 请注意,解决死问题可能需要一定的经验和调试技巧。如果您遇到复杂的死问题,建议咨询数据库管理员或PostgreSQL社区以获取更专业的支持和建议。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值