PostgreSQL ISOLATION LEVEL生效的时间似乎在第一次SELECT之后 [英] Time when PostgreSQL ISOLATION LEVEL takes effect seems to be after first SELECT
问题描述
我正在运行PostgreSQL 9.5.3。
我试图理解为什么我发现下面两个例程之间的行为有所不同。我发现这种行为是违反直觉的，但是可能有很好的理由。我只是想知道那是什么。
设置 ISOLATION REPEATABLE READ 似乎没有生效直到第一个 SELECT 语句之后。
这两个例程之间的唯一区别是在例程2中放在多余的 SELECT 1; 语句中，而在例程1中则没有这样做。我在常规2中获得了期望的结果。
请参见，其中我错误地认为我看到的行为与我正在查询的特定表有关。 p>
我修改了 krokodilko 的 answer 来演示我所看到的。谢谢，krokodilko！
这些命令应按列出的顺序依次执行，在两个单独的位置之间来回切换会话。
常规1
会话1：
testdb =＃创建表t1（x int）;
创建表
testdb =＃插入t1值（1），（2），（3）;
插入0 3
会话2：
testdb =＃开始事务隔离级别可重复读取；
开始交易
会议1：
testdb =＃从t1处删除x = 2;
删除1
会话2：
testdb =＃SELECT * FROM t1;
x
---
1
3
（2行）
（为什么我在这里看到会议1的效果？）
会议2：
testdb =＃COMMIT;
提交
会议1：
testdb =＃创建表t1（x int）;
创建表
testdb =＃插入t1值（1），（2），（3）;
插入0 3
会话2：
testdb =＃开始事务隔离级别可重复读取；
开始交易
testdb =＃SELECT 1;
？列？
----------
1
（1行）
（为什么我必须这样做？）
会议1：
testdb =＃从t1处删除x = 2;
删除1
会话2：
testdb =＃SELECT * FROM t1;
x
---
1
2
3
（3行）
（这是我期望看到的！）
会议2：
testdb =＃COMMIT;
COMMIT
testdb =＃SELECT * FROM t1;
x
---
1
3
（2行）
（这也是我期望看到的）
根据文档（重点是我的）：
可重复读取
当前事务的所有语句只能看到在第一个查询之前提交的行或数据修改语句。
我只能猜测这样做的动机，但我认为这是因为直到开始查询数据才变得无关紧要。一旦开始查询，数据是一致的。
I'm running PostgreSQL 9.5.3.
I am trying to understand why I see a difference in behavior between the two routines below. I find this behavior counter-intuitive, but there may be a very good reason for it; I just want to know what it is if so.
Setting ISOLATION LEVEL REPEATABLE READ does not seem to take effect until after the first SELECT statement.
The only difference between the two routines is that in "Routine 2" I put in a superfluous SELECT 1 ; statement, whereas in "Routine 1" I did not do this. I got my desired results in "Routine 2".
See my (overly-lengthy) question that I posted earlier in which I incorrectly assumed the behavior I was seeing was something to do with what particular tables I was querying.
I've modified the routine from krokodilko's answer to demonstrate what I'm seeing. Thanks, krokodilko!
These are meant to be executed serially, in the order listed, switching back and forth between two separate sessions.
Routine 1
Session 1:
testdb=# CREATE TABLE t1( x int ) ;
CREATE TABLE
testdb=# INSERT INTO t1 VALUES (1),(2),(3) ;
INSERT 0 3
Session 2:
testdb=# START TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
START TRANSACTION
Session 1:
testdb=# DELETE FROM t1 WHERE x = 2 ;
DELETE 1
Session 2:
testdb=# SELECT * FROM t1 ;
x
---
1
3
(2 rows)
(why am I seeing the effects from session 1 here?)
Session 2:
testdb=# COMMIT ;
COMMIT
Session 1:
testdb=# CREATE TABLE t1( x int ) ;
CREATE TABLE
testdb=# INSERT INTO t1 VALUES (1),(2),(3) ;
INSERT 0 3
Session 2:
testdb=# START TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
START TRANSACTION
testdb=# SELECT 1 ;
?column?
----------
1
(1 row)
(why should I have to do this?)
Session 1:
testdb=# DELETE FROM t1 WHERE x = 2 ;
DELETE 1
Session 2:
testdb=# SELECT * FROM t1 ;
x
---
1
2
3
(3 rows)
(that's what I expected to see!)
Session 2:
testdb=# COMMIT ;
COMMIT
testdb=# SELECT * FROM t1 ;
x
---
1
3
(2 rows)
(that's also what I expected to see)
According to the docs (emphasis mine):
REPEATABLE READ
All statements of the current transaction can only see rows committed before the first query or data-modification statement was executed in this transaction.
I can only guess the motivation for making it this way, but I think it's because it simply doesn't matter until you start querying for data. Once you start to query the data is consistent.
这篇关于PostgreSQL ISOLATION LEVEL生效的时间似乎在第一次SELECT之后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
