PostgreSQL事物隔离级别之可重复读

最新推荐文章于 2023-12-01 10:16:35 发布

MAD-APES

最新推荐文章于 2023-12-01 10:16:35 发布

阅读量1.6k

点赞数 2

分类专栏： PostgreSQL事物文章标签：数据库 postgresql

本文链接：https://blog.csdn.net/ergdfhrt/article/details/121648760

版权

PostgreSQL事物专栏收录该内容

4 篇文章 0 订阅

订阅专栏

可重复读

    开启可重复读事务
    BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    PostgreSQL 在该隔离级别下不会出现 幻读。

    使用这个级别的应用必须准备好由于序列化失败而重试事务。

知识点

    该隔离级别下，查询获取的是事务开始前提交的数据。
    要注意的是：
        读已提交获取的查询命令开始时已提交的数据，一个事务中前后查询到的数据可能不一致。
        可重复读获取的是事务开始前已提交的数据，同一个事务中前后查询获取到的数据是一致的。注意是看到的是事务中第一个非事务控制语句看到快照，而不是事务中当前语句看到额快照。

    在该隔离级别下，查询看不到事务开始前未提交的数据，也看不到其他并发事务在该事务执行过程中提交的数据。

    在该隔离级别下，一个查询可以看到该事务前面未提交的数据。

看到的是事务中第一个非事务控制语句的一个快照。

场景一

    数据表中原有的数据：SELECT * FROM transaction_test;
        id	name	age
        20	TEST	10


    开启一个事务A：只是执行 GEGIN TRANSACTION IOSLATION LEVEL REPEATABLE READ; 不要提交，后面还有操作。
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
        > OK
        > Time: 0.001s

    开启事务B：执行插入操作并提交。
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        INSERT INTO transaction_test VALUES (20, 'TEST----', 20);
        COMMIT;


        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
        > OK
        > Time: 0s


        INSERT INTO transaction_test VALUES (20, 'TEST----', 20)
        > Affected rows: 1
        > Time: 0s


        COMMIT
        > OK
        > Time: 0.002s

    此时在事务A中执行查询操作。结果如下。
        id	name	age
        20	TEST	10
        20	TEST----	20

场景二

    数据库表中的数据：SELECT * FROM transaction_test;
        id	name	age
        20	TEST	10
        20	TEST----	20

    开启事务A，只执行一下操作
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        SELECT * FROM transaction_test;

    返回结果：
        id	name	age
        20	TEST	10
        20	TEST----	20

    开启事务B，插入一条数据，并提交。
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        INSERT INTO transaction_test VALUES (120, 'YYYYYYY', 120);
        COMMIT;

        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
        > OK
        > Time: 0.001s


        INSERT INTO transaction_test VALUES (120, 'YYYYYYY', 120)
        > Affected rows: 1
        > Time: 0.001s


        COMMIT
        > OK
        > Time: 0.002s

    开启事务C查询，结果如下：
        id	name	age
        20	TEST	10
        20	TEST----	20
        120	YYYYYYY	120

    此时在事务A中再次执行查询：结果如下：
        id	name	age
        20	TEST	10
        20	TEST----	20

场景三

    数据库表中原有数据：
        id	name	age
        20	TEST	10
        20	TEST----	20
        120	YYYYYYY	120

    开启事务A，并执行如下操作，不提交。
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        SELECT pg_sleep(10);
        SELECT * FROM transaction_test;

    查询结果如下：
        id	name	age
        20	TEST	10
        20	TEST----	20
        120	YYYYYYY	120

    开启事务B，执行插入操作并提交。注意该步操作必须在pg_sleep执行结束前完成。

    开启事务C，执行查询操作：
        id	name	age
        20	TEST	10
        20	TEST----	20
        120	YYYYYYY	120
        265	香港加油	355

    事务A中再次执行查询操作：    查询结果如下：
        id	name	age
        20	TEST	10
        20	TEST----	20
        120	YYYYYYY	120

结论

    可重复读的隔离级别看到的记录，不是事务开始时的快照，而是事务中第一个非事务控制语句执行时的快照。

在该隔离级别下，查询看不到事务开始前未提交的数据，也看不到其他并发事务在该事务执行过程中提交的数据。

    数据库表中没有数据。
    开启事务A：执行插入操作，不提交。
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        INSERT INTO transaction_test VALUES (265, '香港加油', 355);

        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
        > OK
        > Time: 0.001s


        INSERT INTO transaction_test VALUES (265, '香港加油', 355)
        > Affected rows: 1
        > Time: 0.002s

    开启事务B，执行查询操作：
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        SELECT * FROM transaction_test;

    查询无结果。

    此时提交事务A。再在事务B中执行查询操作。依然没有结果。

在该隔离级别下，一个查询可以看到该事务前面未提交的数据。

    开启事务，执行以下操作：

        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        SELECT * FROM transaction_test;

        INSERT INTO transaction_test VALUES (265, '香港加油', 355);

        SELECT * FROM transaction_test;
   执行结果如下：
        第一个查询一句没有记录。
        第二个查询语句结果如下：
            id	name	age
            265	香港加油	355

UPDATE DELETE SELECT FOR UPDATE SELECT FOR SHARE

    对于这些命令，在查询数据时的行为和 SELECT 完全一致。即只看到事务开始时已经提交的行。

    但在找到目标行时，这些记录可能已经被其他并发的事务阻塞，譬如更新、删除、加锁。

    第一个阻塞事务对第二个被阻塞事务的影响如下。

第一个阻塞事务回滚。

场景一

    数据库表中已有数据：
        id	name	age
        265	香港加油	355

    开启事务A：执行如下操作，先不回滚。
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        UPDATE transaction_test SET name = '中国加油' WHERE id = 265;

    结果如下：
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
        > OK
        > Time: 0.001s


        UPDATE transaction_test SET name = '中国加油' WHERE id = 265
        > Affected rows: 1
        > Time: 0.007s

    开启事务B，执行同样数据的修改操作。
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        UPDATE transaction_test SET name = '中国加油-YYDS' WHERE id = 265;

    结果如下：
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
        > OK
        > Time: 0.004s
    注意此时，事务B中的修改操作并没有返回信息。

    在事务A中执行回滚操作。事务B中的修改操作立刻返回信息如下：
        UPDATE transaction_test SET name = '中国加油-YYDS' WHERE id = 265
        > Affected rows: 1
        > Time: 92.628s

场景2

    数据库中原有数据：
        id	name	age
        265	中国加油-YYDS	355
        1	香港加油	1

    开启事务A执行以下操作：
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        DELETE FROM transaction_test WHERE id = 1;

    结果如下：
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
        > OK
        > Time: 0.001s


        DELETE FROM transaction_test WHERE id = 1
        > Affected rows: 1
        > Time: 0.001s

    开启事务B，执行以下操作。
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        UPDATE transaction_test SET name = '中国加油' WHERE id = 1;

    结果如下：
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
        > OK
        > Time: 0.001s
    注意此时事务B中的修改操作没有返回信息，被阻塞了。在事务A中执行回滚操作。
    事务B中的修改操作立刻返回信息：
        UPDATE transaction_test SET name = '中国加油' WHERE id = 1
        > Affected rows: 1
        > Time: 147.549s

    提交事务B后，数据表中数据如下：
        id	name	age
        265	中国加油-YYDS	355
        1	中国加油	1

结论

    第一个阻塞事务回滚后，该事务的作用将被忽略，第二个事务可以继续更新最初发现的行。
    注意两个事务中的修改操作，受影响的行有交叉时才会发生阻塞。

第一个事务提交。

场景1 – 更新提交

    数据表原有数据：
        id	name	age
        265	中国加油-YYDS	355
        1	中国加油	1

    开启事务A，执行以下操作，先不提交。
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        UPDATE transaction_test SET name = '中国加油-YYDS' WHERE id = 1;

        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
        > OK
        > Time: 0s


        UPDATE transaction_test SET name = '中国加油-YYDS' WHERE id = 1
        > Affected rows: 1
        > Time: 0s

    开启事务B，执行以下操作
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        UPDATE transaction_test SET name = '中国加油-YYDS-2' WHERE id = 1;

        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
        > OK
        > Time: 0s

    此时事务B的修改操作没有信息返回，被阻塞了。
    提交事务A。事务B的修改操作立刻返回信息如下：
        UPDATE transaction_test SET name = '中国加油-YYDS-2' WHERE id = 1
        > ERROR:  could not serialize access due to concurrent update(由于并发更新，无法序列化访问)

        > Time: 164.355s

    事务B如果执行的是删除操作，同样的结果。

场景2 – 删除提交

    表中原有数据：
        id	name	age
        1	中国加油-YYDS	1
        20	TEST	10

    开启事务A执行以下操作
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        DELETE FROM transaction_test WHERE id = 1;

        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
        > OK
        > Time: 0s


        DELETE FROM transaction_test WHERE id = 1
        > Affected rows: 1
        > Time: 0.001s

    开启事务B，执行以下操作：
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        UPDATE transaction_test SET name = '中国加油-YYDS' WHERE id = 1;

        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
        > OK
        > Time: 0s

    事务B中的更新操作没有信息返回，被阻塞了。此时提交事务A。事务B中的更新操作返回如下信息：
        UPDATE transaction_test SET name = '中国加油-YYDS' WHERE id = 1
        > ERROR:  could not serialize access due to concurrent update

        > Time: 69.744s

场景3 – 事务A修改提交后，事务B加锁

    开启事务A：执行以下操作，先不提交。
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        DELETE FROM transaction_test WHERE id = 20;

    结果如下：
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
        > OK
        > Time: 0.001s


        DELETE FROM transaction_test WHERE id = 20
        > Affected rows: 2
        > Time: 0s

    开启事务B,执行加锁操作：
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        SELECT * FROM transaction_test  FOR UPDATE;

    结果如下：
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
        > OK
        > Time: 0.003s

    注意事务B的 SELECT FOR UPDATE 操作没有信息返回，说明事务B被阻塞了。这是提交事务A。事务B立刻返回如下信息。
        SELECT * FROM transaction_test  FOR UPDATE
        > ERROR:  could not serialize access due to concurrent update
        > Time: 5.819s

场景4 – 只是锁住提交

    数据表中原有数据：
        id	name	age
        20	TEST	10

    开启事务A执行以下操作，不提交。
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        SELECT * FROM transaction_test  FOR UPDATE;

    结果如下：
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        SELECT * FROM transaction_test  FOR UPDATE;

            id	name	age
            20	TEST	10

    开启事务B，执行如下操作：
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        DELETE FROM transaction_test WHERE id = 20;

    结果如下：
        BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
        > OK
        > Time: 0s

    注意此时事务B中的删除操作没有信息返回，被阻塞。提交事务A。事务B中的删除操作立刻返回信息：
        DELETE FROM transaction_test WHERE id = 20
        > Affected rows: 1
        > Time: 710.841s

    提交事务B。符合条件的数据正在被删除。

结论

    在可重复读的隔离级别中，第一个事务阻塞第二个事务，只有两个事务作用的行有交叉才会发生阻塞。
    第一个事务不论是修改提交或者是删除提交，被阻塞的第二个事务都不能修改、删除或者锁住第一个事务所影响的行。并返回 ERROR:  could not serialize access due to concurrent update。
    第一个事务如果是仅仅锁着然后提交释放锁(select for update/share)，则第二个被阻塞的事务可以进行作用最初发现的行。

注意事项

    使用这个事务隔离级别的应用必须重试因序列化失败导致失败的事务。
    即：返回 ERROR:  could not serialize access due to concurrent update(由于并发更新，无法序列化访问) 这个错误，应当中断当前事务，并从头重试整个事务。

    只读事务永远不会发生序列化冲突。

MAD-APES

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
PostgreSQL事物隔离级别之可重复读

可重复读开启可重复读事务 BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; PostgreSQL 在该隔离级别下不会出现幻读。使用这个级别的应用必须准备好由于序列化失败而重试事务。知识点该隔离级别下，查询获取的是事务开始前提交的数据。要注意的是：读已提交获取的查询命令开始时已提交的数据，一个事务中前后查询到的数据可能不一致。可重复读获取的是事务开始前已提交
复制链接

扫一扫