Postgresql锁机制(表锁和行锁)

本文详细解析PostgreSQL中的各种锁类型,包括表锁和行锁,解释如何使用LOCK TABLE命令来控制数据的一致性和并发访问,以及不同锁模式之间的冲突和应用策略。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

表锁

LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]

lockmode包括以下几种:

ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

LOCK TABLE命令用于获取一个表锁,获取过程将阻塞一直到等待的锁被其他事务释放。如果使用NOWAIT关键字则如果获取不到锁,将不会等待而是直接返回,放弃执行当前指令并抛出一个错误(error)。一旦获取到锁,将一直持有锁直到事务结束。(没有主动释放锁的命令,锁总是会在事务结束的时候被释放)。

当使用自动获取锁的模式的时候,PostgreSQL总是尽可能地使用限制最小的模式。LOCK TABLE命令使我们可以自己定义锁的限制大小。比如一个应用程序使用事务在读提交(Read Committed isolation level)模式下需要保证数据库的数据在事务期间保持稳定,于是可以使用SHARE锁模式在读取前对表进行加锁。这可以防止并发的数据改变并且可以保证后续的事务对这个表的读取不会读到没有提交的数据,因为SHARE锁和由写入事务持有的ROW EXCLUSIVE锁是冲突的,所以对于想要使用SHARE锁对表进行加锁的事务,将会等到它之前所有持有该表的ROW EXCLUSIVE锁的事务commit或者是roll back。因此,一旦获取了表的SHARE锁,将不会有没有提交的数据,同样也不会有其他事务能够对表数据进行改变,直到当前事务释放SHARE锁。

为了在REPEATABLE READ(重复读)模式和SERIALIZABLE(序列化)模式下实现同样的效果,必须在任何查询和修改语句之前加上LOCK TABLE。在执行第一句SELECT语句或者修改数据语句前,重复读和序列化模式中一个事务的的数据视图将会被存储为快照。在这种情况下,事务申明的表锁同样可以避免并发的修改,但是并不能保证该事务能够读取到最新提交的数据。

如果一个事务想要修改表中的数据,应该使用SHARE ROW EXCLUSIVE(共享行排他)锁而不是SHARE锁。共享行排他锁将能够保证在同一时间只有当前事务能够运行。不加这个锁的话可能会造成死锁:两个事务同时想要获取SHARE锁,并且接下来又想要同时获取ROW EXCLUSIVE锁去进行数据更新(注意:同一个事务获取的两种不同的锁不会造成冲突,所以对于同一个事务,它可以在获取SHARE锁之后再次获取ROW EXCLUSIVE,当然是在没有其他事务获取SHARE锁的情况下)。为了避免死锁,应该保证所有的事务获取同一对象的锁的顺序是一致的,同时如果在同一个对象上想要获取多个锁,则总是应该先获取限制最大的锁。

ACCESS SHARE(访问共享锁)

只与ACCESS EXCLUSIVE锁冲突。

SELECT命令会在当前查询的表上获取一个ACCESS SHARE锁。总的来说,任何只读操作都会获取该锁。

ROW SHARE(行共享锁)

和EXCLUSIVE锁和ACCESS EXCLUSIVE锁冲突。

SELECT FOR UPDATE或者SELECT FOR SHARE命令会在目标表上获取该锁,并且所有被引用但是没有FOR UPDATE的表上会加上ACCESS SHARED锁。

ROW EXCLUSIVE(行排他锁)

和SHARE,SHARE ROW EXCLUSIVE和ACCESS EXCLUSIVE锁冲突。

UPDATE,DELETE和INSERT会在目标表上获取该锁,总的来说,任何对数据库数据进行修改的命令会获取到该锁。

SHARE UPDATE EXCLUSIVE(共享更新排他锁)

和SHARE UPDATE EXCLUSIVE,SHARE ROW EXCLUSIVE,EXCLUSIVE和ACCESS EXCLUSIVE冲突,该锁可以保护表防止并发的(schema)改变和VACUUM(释放空间)命令。

VACUUM,ANALYZE,CREATE INDEX CONCURRENTLY和ALTER TABLE VALIDATE以及其他ALTER TABLE类的命令会获取该锁。

SHARE(共享锁)

和ROW EXCLUSIVE,SHARE UPDATE EXCLUSIVE,SHARE ROW EXCLUSIVE,EXCLUSIVE和ACCESS EXCLUSIVE锁冲突。该锁保护一个表防止并发的数据改变。

由CREATE INDEX命令获得。

SHARE ROW EXCLUSIVE(行共享排他锁)

和ROW EXCLUSIVE,SHARE UPDATE EXCLUSIVE,SHARE,SHARE ROW EXCLUSIVE,EXCLUSIVE以及ACCESS EXCLUSIVE锁冲突,该锁用于保护一个表防止并发的数据改变,同时是自排他的,所以在同一时间只有同一个session可以持有该锁。

该锁不会被PGSQL的任何命令自动获取。

EXCLUSIVE(排它锁)

和ROW SHARE,ROW EXCLUSIVE,SHARE UPDATE EXCLUSIVE,SHARE,SHARE ROW EXCLUSIVE,EXCLUSIVE和ACCESS EXCLUSIVE锁冲突。该锁只允许并发的ACCESS SHARE锁,只有只读操作能在一个事务持有排他锁的时候进行并发操作。

ACCESS EXCLUSIVE(访问排他锁)

和所有的锁都冲突,该锁保证只有持有锁的事务能够访问当前表。

被DROP TABLE,TRUNCATE,REINDEX,CLUSTER,VACUUM FULL和REFRESH MATERIALIZED VIEW命令自动获取。有很多种形式的ALTER TABLE命令可以获取该锁,它同样也是LOCK TABLE命令默认的锁级别。

只有ACCESS EXCLUSIVE锁可以防止一个SELECT语句。

注意

一段获取锁,只有当事务结束的时候才会释放,但是如果一个锁是在一个savepoint(保存点)之后被获取,则当这个保存点回滚的时候这个锁会被马上释放。

在这里插入图片描述

行锁

除了表锁,PgSQL还提供了行锁。一个事务可以获取相互冲突的两种行锁,包括在子事务中,但是两个事务不能同时在同一行获取相互冲突的两种锁。

FOR UPDATE

FOR UPDATE锁使得SELECT语句可以获取行锁用于更新数据。这使得该行可以防止被其他的事务获取锁或者进行更改删除操作,也就是说其他事务的操作会被阻塞直到当前事务结束;同样的,SELECT FOR UPDATE命令会等待直到前一个事务结束。在REPEATABLE模式或者SERIALIZABLE模式下,如果一个将要被上锁的行在事务开始之前被删除了,则会返回一个error。

FOR UPDATE锁同样可以被DELETE命令获取,以及UPDATE命令当使用在确定的行用来修改数据的时候也会获取到该锁。目前当使用确定的唯一索引时使用UPDATE命令可以获取到该锁(部分索引和联合索引暂时不支持),但是未来可能会改变这种设计。

FOR NO KEY UPDATE

和FOR UPDATE命令类似,但是对于获取锁的要求更加宽松一些,在同一行中不会阻塞SELECT FOR KEY SHARE命令。同样在UPDATE命令的时候如果没有获取到FOR UPDATE锁的情况下会获取到该锁。

FOR SHARE

和FOR NO KEY UPDATE命令类似,不同点在于这个锁是一个共享锁而不是之前的锁一样是排他锁,所以这个锁会阻塞UPDATE,DELETE,SELECT FOR UPDATE或者SELECT FOR NO KEY UPDATE,但是不会阻塞SELECT FOR SHARE或者SELECT FOR KEY SHARE。

FOR KEY SHARE

和FOR SHARE表现类似,但是对加锁的要求更加宽松,SELECT FOR UPDATE会被阻塞但是SELECT FOR NO KEY UPDATE不会被阻塞。KEY SHARE模式下的锁会阻塞其他事务的DELETE或者是改变KEY值的UPDATE语句,但是对于其他的UPDATE或者是SELECT FOR NO KEY UPDATE,SELECT FOR SHARE以及SELECT FOR KEY SHARE则不会阻塞。

在这里插入图片描述

<think>好的,我现在需要解释数据库锁机制的原理。首先,我应该回顾一下数据库的基本概念。数据库是为了处理并发操作时可能出现的冲突,比如多个事务同时修改同一数据导致的数据不一致问题。 接下来,我需要确定的分类。常见的类型有共享(S排他(X)。共享允许其他事务读但不能写,排他则阻止其他事务的读写。这部分需要详细说明,但要注意不要混淆两者。 然后,要考虑的粒度。数据库可以定不同层次的数据,比如、页等。粒度越小,并发度越高,但管理开销也越大。需要举例说明不同粒度的应用场景。 还要提到的兼容性,解释不同之间的共存情况。比如,共享之间是兼容的,但共享排他不兼容。这有助于理解为什么某些操作会被阻塞。 接下来,死的问题必须涉及。当两个或多个事务相互等待对方释放时,就会发生死数据库通常有死检测恢复机制,比如超时回滚或等待图算法。需要简单说明这些机制的工作原理。 此外,乐观悲观的区别也很重要。悲观假设冲突会发生,先加再操作;乐观则先操作,提交时检查冲突。这里可以对比两者的适用场景,比如高并发下的不同策略。 最后,总结锁机制的作用,即确保事务的ACID特性,特别是隔离性一致性。可能还需要提到现代数据库的MVCC(多版本并发控制)作为锁机制的补充或替代,但MVCC的具体内容可以简要带过,避免偏离主题。 在组织内容时,要确保逻辑清晰,分点说明,每个部分用例子或实际场景帮助理解。需要检查技术术语的准确性,比如排他共享的正确定义,避免错误。同时,语言要通俗易懂,适合不同层次的读者理解。</think>数据库锁机制是确保数据一致性与事务隔离性的核心技术,其原理可通过以下六个层面逐步解释: 1. **基本作用原理** 当事务A访问数据时,系统会像在图书馆借书一样给数据贴标签:共享(S)允许其他人阅读但不修改,排他(X)则独占数据使用权。例如: ```sql -- 共享查询(允许其他读操作) SELECT * FROM books WITH(SHARELOCK) WHERE id=1 -- 排他更新(阻止其他所有操作) UPDATE books SET stock=9 WHERE id=1 ``` 2. **粒度维度** - 定单条记录(如修改某用户余额) - 页级定4KB数据页(适合批量更新) - :全定(结构变更时使用) 现代数据库如Oracle采用实现细粒度控制,而MySQL的InnoDB通过Next-Key防止幻读。 3. **兼容矩阵机制** 当不同请求相遇时: ``` | S请求 | X请求 现有S | 允许 | 拒绝 现有X | 拒绝 | 拒绝 ``` 这种机制确保:当财务正在计算总账(持有S)时,允许报读取,但禁止金额修改(X需等待)。 4. **升级与转换策略** 事务可能动态调整级别: - 升级:当单个事务定超过5000时,SQL Server会自动升级为 - 转换:UPDATE操作先将S转换为X才能修改数据 5. **死解决方案** 银转账死案例: 事务A:账户1 → 请求账户2 事务B:账户2 → 请求账户1 数据库通过: - 等待图检测(每50ms扫描) - 代价计算(回滚修改量小的事务) - 超时机制(默认5分钟) 6. **多版本并发控制(MVCC)** PostgreSQL通过以下结构实现无读取: ```c struct HeapTupleHeaderData { TransactionId t_xmin; // 插入事务ID TransactionId t_xmax; // 删除事务ID ItemPointerData t_ctid; // 版本链指针 } ``` 读操作访问历史版本,写操作创建新版本,有效降低冲突。 实际应用中,银核心系统通常采用+MVCC的组合方案,在保证数据一致性的同时,将吞吐量提升3-5倍。开发时需注意:事务尽量短小,访问资源顺序一致,合理设置隔离级别(如RC级别减少持有时间)。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值