PostgreSQL事物隔离级别之读已提交

最新推荐文章于 2024-07-13 18:10:19 发布

MAD-APES

最新推荐文章于 2024-07-13 18:10:19 发布

阅读量373

点赞数 1

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

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

版权

PostgreSQL事物专栏收录该内容

4 篇文章 0 订阅

订阅专栏

读已提交

    PostgreSQL 的默认隔离级别。

    在该隔离级别下，一个查询(没有 FOR UPDATE/SHARE 子句)，查询到的是查询开始之前已经提交的数据，不能查询到未提交和查询过程中其它事务提交的数据。
    本质上查询看到的是查询开始前数据库的一个快照。
    该隔离级别下，一个查询可以看到自身事务之前未提交的数据。
    在同一个事务的两个查询可能查询到的数据并不相同。发生不可重复读。

查询的知识点以及验证

该级别下，查询只能看到已提交的数据，未提交的数据或者查询过程中其他事务提交的数据无法看到

    SELECT * FROM transaction_test;
    id  	name   	age
    2   	lisi   	17
    2   	lisi   	17

    事务A：开启事务，插入一条数据，不提交
        BEGIN;
        INSERT INTO transaction_test VALUES (1, 'zhangsan', 20);

    执行结果：
        BEGIN
        > OK
        > Time: 0s


        INSERT INTO transaction_test VALUES (1, 'zhangsan', 20)
        > Affected rows: 1
        > Time: 0.002s

    事务B: 开启事务，并执行查询。
        BEGIN;
        SELECT * FROM transaction_test;

    查询结果：
        id  	name   	age
        2   	lisi   	17
        2   	lisi   	17

    说明：在查询开始之前未提交的数据，不能被看到。

    -- 查询过程中提交数据的情况不再验证。

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

    开启一个事务：查询一条数据，不提交，然后查询。
        BEGIN;
        INSERT INTO transaction_test VALUES (1, 'zhangsan', 20);

    		SELECT * FROM transaction_test;


    执行结果：
        BEGIN
        > OK
        > Time: 0s


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


        SELECT * FROM transaction_test
        > OK
        > Time: 0.002s

        id    	name     	age
        2	      lisi  	   17
        2	      lisi	     17
        1	      zhangsan   20

        开启另一个事务B：执行查询操作。注意此时上面那个事务还未提交。
            BEGIN;
            SELECT * FROM transaction_test;

        执行结果：
            id  	name   	age
            2   	lisi   	17
            2   	lisi   	17

在同一个事务的两个查询可能查询到的数据并不相同。发生不可重复读。

    开启事务A：先查询，然后开启事务B查询一条数据并提交，然后在事务A中再次执行查询操作。

    执行结果：
        事务A中第一次查询结果：
            id  	name   	age
            2   	lisi   	17
            2   	lisi   	17

        事务B执行插入操作并提交。
            BEGIN;
            INSERT INTO transaction_test VALUES (1, 'zhangsan', 20);
            COMMIT;

        事务A再次执行查询操作：
              id	name	age
              2	lisi	17
              2	lisi	17
              1	zhangsan	20

    说明：在读已提交的隔离模式下，查询操作获取的结果是查询操作前已经提交的数据。

UPDATE DELETE SELECT FOR UPDATE SELECT FOR SHARE 的影响

    执行这些命令，查询数据时和 SELECT 的行为一致。都是查询命令执行前已经提交的数据。

    这些命令在查询数据的时候，可能会被其他并发事务阻塞(如：更新操作，删除操作，或者加锁)。
    阻塞事务可能会回滚或者提交。以下验证。

阻塞事务回滚

UPDATE 操作

      SELECT * FROM transaction_test;
          id	name	age
          2	lisi	17
          2	lisi	17
          1	zhangsan	20

      开启一个事务A，执行更新操作，不提交，也不回滚。
          BEGIN;
    		    UPDATE transaction_test SET name = 'UPDATE TEST' WHERE id = 2;

     执行结果：
         BEGIN
          > OK
          > Time: 0s

          UPDATE transaction_test SET name = 'UPDATE TEST' WHERE id = 2
          > Affected rows: 2
          > Time: 0.002s

    开启一个事务B,执行更新操作，注意要修改同一条数据，并且事务A未提交，未回滚。
        BEGIN;
        UPDATE transaction_test SET name = 'UPDATE TEST' WHERE id = 2;

    执行结果：
        BEGIN
        > OK
        > Time: 0s

    注意：此时事务B的更新操作并没有信息反馈，说明事务B被阻塞了。

    在事务A中执行回滚操作。
        ROLLBACK;

    原本被阻塞的事务B立马又信息反馈：
        UPDATE transaction_test SET name = 'UPDATE TEST' WHERE id = 2
        > Affected rows: 2
        > Time: 165.815s

    所以第一个事务回滚，那么它的作用将被忽略，第二个事务将继续执行它的操作。

    ```
      如果两个事务修改的不是同样的数据会怎么样？？？
      执行过程同上面讲到的。
      但是结果却不一样：
      事务A：
          BEGIN;
          UPDATE transaction_test SET name = 'UPDATE TEST' WHERE id = 1;

          BEGIN
          > OK
          > Time: 0s


          UPDATE transaction_test SET name = 'UPDATE TEST' WHERE id = 1
          > Affected rows: 1
          > Time: 0s

      事务B：
          BEGIN;
          UPDATE transaction_test SET name = 'UPDATE TEST' WHERE id = 2;

          BEGIN
          > OK
          > Time: 0s


          UPDATE transaction_test SET name = 'UPDATE TEST' WHERE id = 2
          > Affected rows: 2
          > Time: 0.001s

      事务A不会阻塞事务B.

    ```

DELETE 操作

    开启一个事务A：执行删除操作，不提交也不回滚。
        BEGIN;
        DELETE FROM transaction_test WHERE id = 2;

        BEGIN
        > OK
        > Time: 0.001s


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

    开启事务B：执行删除操作，注意上次同样条件的数据，事务A保持不提交，不回滚。
        BEGIN;
        DELETE FROM transaction_test WHERE id = 2;

        BEGIN
        > OK
        > Time: 0.001s

    注意：此时事务B的删除操作并没有信息反馈，说明事务B被阻塞了。

    在事务A中执行回滚操作：
        ROLLBACK;

    事务B中立刻又信息反馈：
        DELETE FROM transaction_test WHERE id = 2
        > Affected rows: 2
        > Time: 78.159s

    ```
        两个并发事务删除不同的数据：
            结果是开始执行的事务不会阻塞后面的事务。
    ```

UPDATE DELETE 同上。

阻塞事务提交

删除提交

    开启一个事务A：执行删除操作，但先不提交。
        BEGIN;
        DELETE FROM transaction_test WHERE id = 2;

    执行结果：
        BEGIN
        > OK
        > Time: 0s


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

    开启事务B，执行相同数据更新操作。
        BEGIN;
    		UPDATE transaction_test SET name = 'UPDATE TEST' WHERE id = 2;

    执行结果：
        BEGIN
        > OK
        > Time: 0.002s

    事务B被阻塞。
    此时提交A事务。
        COMMIT;

    B事务返回信息：
        UPDATE transaction_test SET name = 'UPDATE TEST' WHERE id = 2
      > Affected rows: 0
      > Time: 87.423s

    如果事务B执行的是删除同样的数据。同修改一样。

    结论：如果第一个事务进行删除提交，那么第二个事务将忽略该行。

    如果第一个事务影响的数据和第二个事务影响的数据不同，则不会阻塞。
    只有两个事务影响的数据有交叉才会阻塞，并按照结论。

更新提交

第一个事务更新后数据依然满足事务2的查询条件。

    SELECT * FROM transaction_test;
    id	name	age
    1	zhangsan	17
    1	zhangsan	17
    1	zhangsan	17
    2	zhangsan	17

    开启一个事务A:进行更新操作，先不提交。
        BEGIN;
        UPDATE transaction_test SET name = 'UPDATE TEST - transaction 1' WHERE id = 2;

    执行结果：
        BEGIN
        > OK
        > Time: 0.001s


        UPDATE transaction_test SET name = 'UPDATE TEST - transaction 1' WHERE id = 2
        > Affected rows: 1
        > Time: 0.001s

    开启事务B，执行对相同数据的修改操作：
        BEGIN;
        UPDATE transaction_test SET name = 'UPDATE TEST - transaction 2' WHERE id = 2;

    执行结果：
        BEGIN
        > OK
        > Time: 0s

    事务B被阻塞。
    这个时候提交事务A。
    表中的数据如下：
        id	name	age
        1	zhangsan	17
        1	zhangsan	17
        1	zhangsan	17
        2	UPDATE TEST - transaction 1	17

    此时事务B信息反馈：
        UPDATE transaction_test SET name = 'UPDATE TEST - transaction 2' WHERE id = 2
        > Affected rows: 1
        > Time: 69.413s

    提交事务B，表中的数据：
        id	name	age
        1	zhangsan	17
        1	zhangsan	17
        1	zhangsan	17
        2	UPDATE TEST - transaction 2	17

第一个事务更新后数据原来不满足的记录满足事务2的查询条件。

    开启事务A：不提交
        BEGIN;
        UPDATE transaction_test SET id = 2 WHERE name = 'zhangsan';

        BEGIN
        > OK
        > Time: 0.001s


        UPDATE transaction_test SET id = 2 WHERE name = 'zhangsan'
        > Affected rows: 4
        > Time: 0.003s

    开启事务B，此时事务A并没有提交。
        BEGIN;
        UPDATE transaction_test set name='wangwu' WHERE id = 2;

        BEGIN
        > OK
        > Time: 0.001s

    事务B被阻塞，此时提交A，B返回信息：
        UPDATE transaction_test set name='wangwu' WHERE id = 2
        > Affected rows: 1
        > Time: 94.131s

    提交B，受影响的只有一行,即最初发现的行。原本不满足事务B修改操作条件的，即使经过事务A满足了事务B修改操作的条件，也不会被修改。

第一个事务更新提交后数据不再满足事务2的条件。

    表中数据
        id	name	age
        2	AAA	17
        1	zhangsan	17
        1	zhangsan	17
        1	zhangsan	17

    开启事务A：修改操作，不提交。
        BEGIN;
        UPDATE transaction_test SET id = 1 WHERE name = 'zhangsan';


        BEGIN
        > OK
        > Time: 0s


        UPDATE transaction_test SET id = 1 WHERE name = 'zhangsan'
        > Affected rows: 4
        > Time: 0s

    开启事务B：修改id=2的记录。
        BEGIN;
        DELETE FROM transaction_test WHERE id = 2;

        BEGIN
        > OK
        > Time: 0.001s

    事务B被阻塞，这是提交A，事务B返回信息。
        DELETE FROM transaction_test WHERE id = 2
        > Affected rows: 0
        > Time: 108.07s

    修改操作同上。

结论

        第一个事务提交后，如果修改后的记录仍然满足第二个事务的条件(where 子句将会重新计算来看该行被更新后的版本是否满足搜索条件)，则会修改被影响。
        否则将不会受影响。

        要注意的是：对于原来不满足事务B的记录，经过事务A修改满足事务B后提交的，也不会被事务B影响。事务B只作用最初发现的记录。

MAD-APES

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
PostgreSQL事物隔离级别之读已提交

读已提交 PostgreSQL 的默认隔离级别。在该隔离级别下，一个查询(没有 FOR UPDATE/SHARE 子句)，查询到的是查询开始之前已经提交的数据，不能查询到未提交和查询过程中其它事务提交的数据。本质上查询看到的是查询开始前数据库的一个快照。该隔离级别下，一个查询可以看到自身事务之前未提交的数据。在同一个事务的两个查询可能查询到的数据并不相同。发生不可重复读。查询的知识点以及验证该级别下，查询只能看到已提交的数据，未提交的数据或者查询过程中
复制链接

扫一扫