PostgreSQL事务隔离读取未提交 [英] PostgreSQL Transaction Isolation READ UNCOMMITTED

问题描述

我想尝试使用PostgreSQL与pgadmin进行事务隔离。首先，我在BEGIN中插入了一条新记录，但没有COMMIT。

I want to try transaction isolation using PostgreSQL with pgadmin. First I inserted a new record inside BEGIN but without COMMIT.

BEGIN;
INSERT INTO my_table(id,value) VALUES (1,'something');

//UNCOMMITTED

然后，我尝试读取未提交的数据

Then, I tried to read the uncommitted data

BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM my_table
COMMIT;

但是我找不到新记录。

But I couldn't find the new record. What's wrong?

推荐答案

PostgreSQL不支持脏读（读取未提交 ）。正如@a_horse_with_no_name指出的那样，手册说：

PostgreSQL does not support dirty reads (READ UNCOMMITTED). As @a_horse_with_no_name pointed out, the manual says:

SQL标准定义了一个附加级别，即 READ UNCOMMITTED 。在PostgreSQL中， READ UNCOMMITTED 被视为 READ COMMITTED 。

The SQL standard defines one additional level, READ UNCOMMITTED. In PostgreSQL READ UNCOMMITTED is treated as READ COMMITTED.

这符合标准，即数据库必须将不受支持的隔离级别视为最强的支持级别。

This is fitting with the rule in the standard that the database must treat unsupported isolation levels as the strongest supported level.

在PostgreSQL中从正在进行的事务中读取未提交的元组的受支持方法。如果有的话，您将能够获得诸如主键重复值和普遍混乱之类的东西，那么它就不会很有用。

There is no supported way to read uncommitted tuples from an in-progress transaction in PostgreSQL. If there was you'd be able to get things like duplicate values for primary keys and general chaos so it wouldn't be very useful anyway.

有 进行中的事务可以进行通信和相互影响的几种方式：

There are a few ways in-progress transactions can communicate and affect each other:

• 通过共享客户端应用程序（当然）


• SEQUENCE （和 SERIAL ）更新立即发生，而不是在提交时发生


• 建议锁定


• 正常的行和表锁定，但在读已提交可见性规则之内


• UNIQUE 和 EXCLLUSION 约束

• Via a shared client application (of course)
• SEQUENCE (and SERIAL) updates happen immediately, not at commit time
• advisory locking
• Normal row and table locking, but within the rules of READ COMMITTED visibility
• UNIQUE and EXCLUSION constraints

使用超级用户专用调试工具（例如 pageinspect ，但前提是您真正了解数据存储的内幕。仅适用于数据恢复和调试。您会在十六进制输出的墙上看到多个版本的数据。

It's possible to see uncommitted tuple data using superuser-only debug facilities like pageinspect, but only if you really understand the innards of the datastore. It's suitable for data recovery and debugging only. You'll see multiple versions of data in a wall of hexadecimal output.

这篇关于PostgreSQL事务隔离读取未提交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！