暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 社区博客 | PostgreSQL 事务

社区博客 | PostgreSQL 事务

KunlunBase 昆仑数据库 2024-05-13
492

点击上方蓝字关注我们





导读

在日常操作中，对于一组相关操作，通常需要其全部成功或全部失败。在关系型数据库中，将这组相关操作称为事务。


原文作者：CSDN博主[脑子进水养啥鱼？]


关键词：PostgreSQL、事务、ACID



01  
事务的ACID特性

事务具有的四个特性简称为 ACID：

  • 原子性（Atomicity）：保证事务中的操作要么全部成功，要么全部失败，不会只成功一部分。
  • 一致性（Consistency）：数据修改的有效性，并且遵循一定的业务规则。
  • 隔离性（Isolation）：决定了并发事务之间的可见性和相互影响程度。
  • 持久性（Durability）：确保已经提交的事务必须永久生效。


02  
事务的使用

2.1 自动提交事务
    postgres=# \echo :AUTOCOMMIT
    on
    postgres=# create table accounts(id serial primary key,user_name varchar(50),balance numeric(10,4));
    CREATE TABLE
    postgres=# alter table accounts add constraint bal_check check(balance >= 0);
    ALTER TABLE
    postgres=# insert into accounts(user_name, balance) values ('usera', 6000);
    INSERT 0 1
    postgres=# table accounts;
     id | user_name |  balance
    ----+-----------+-----------
      1 | usera     | 6000.0000
    (1 row)

    2.2 手动提交事务
      postgres=# \set AUTOCOMMIT off
      postgres=# \echo :AUTOCOMMIT
      off
      postgres=# begin;
      BEGIN
      postgres=*# insert into accounts(user_name, balance) values ('userb', 0);
      INSERT 0 1
      postgres=*# table accounts;
       id | user_name |  balance
      ----+-----------+-----------
        1 | usera     | 6000.0000
        2 | userb     |    0.0000
      (2 rows)




      postgres=*# commit;
      COMMIT
      postgres=# table accounts;
       id | user_name |  balance
      ----+-----------+-----------
        1 | usera     | 6000.0000
        2 | userb     |    0.0000
      (2 rows)

      2.3 手动回滚事务
        postgres=# begin;
        BEGIN
        postgres=*# insert into accounts(user_name, balance) values ('userc', 2000);
        INSERT 0 1
        postgres=*# table accounts;
         id | user_name |  balance
        ----+-----------+-----------
          1 | usera     | 6000.0000
          2 | userb     |    0.0000
          3 | userc     | 2000.0000
        (3 rows)




        postgres=*# rollback;
        ROLLBACK
        postgres=# table accounts;
         id | user_name |  balance
        ----+-----------+-----------
          1 | usera     | 6000.0000
          2 | userb     |    0.0000
        (2 rows)

        2.4 手动回滚事务至保存点
          postgres=# begin;
          BEGIN
          postgres=*# insert into accounts(user_name, balance) values ('userc', 2000);
          INSERT 0 1
          postgres=*# savepoint sv1;
          SAVEPOINT
          postgres=*# insert into accounts(user_name, balance) values ('userd', 0);
          INSERT 0 1
          postgres=*# table accounts;
           id | user_name |  balance
          ----+-----------+-----------
            1 | usera     | 6000.0000
            2 | userb     |    0.0000
            4 | userc     | 2000.0000
            5 | userd     |    0.0000
          (4 rows)




          postgres=*# rollback to sv1;
          ROLLBACK
          postgres=*# commit;
          COMMIT
          postgres=# table accounts;
           id | user_name |  balance
          ----+-----------+-----------
            1 | usera     | 6000.0000
            2 | userb     |    0.0000
            4 | userc     | 2000.0000
          (3 rows)


          03  
          并发与隔离

          多个用户访问相同数据时，可能导致如下问题：

          • 脏读（dirty read）：一个事务能够读取其他事务未提交的修改。
          • 不可重复读（nonrepeatable read）：一个事务读取某个记录后，再次读取该记录时数据发生了改变（被其他事务修改并提交）。
          • 幻读（phantom read）：一个事务按照某个条件查询一些数据后，再次执行相同查询时结果的数量发生了变化（另一个事务增加或者删除了某些数据并且完成提交）。幻读和非重复读有点类似，都是由于其他事务修改数据导致的结果变化。
          • 更新丢失（lost update）：当两个事务同时读取某一记录，然后分别进行修改提交，就会造成先提交的事务的修改丢失。

          为了解决并发问题， SQL 标准定义了 4 种不同的事务隔离级别（从低到高）：

          • Read Uncommitted（读未提交）：最低的隔离级别，实际上就是不隔离，任何事务都可以看到其他事务未提交的修改；该级别可能产生各种并发异常。不过，PostgreSQL 消除了 Read Uncommitted 级别时的脏读，因为它的实现等同于 Read Committed。
          • Read Committed（读已提交）：一个事务只能看到其他事务已经提交的数据，解决了脏读问题，但是存在不可重复读、幻读和更新丢失问题。这是 PostgreSQL 的默认隔离级别。
          • Repeated Read（可重复读）：一个事务对于同某个数据的读取结果不变，即使其他事务对该数据进行了修改并提交；不过如果其他事务删除了该记录，则无法再查询到数据（幻读）。SQL 标准中的可重复读可能出现幻读，但是 PostgreSQL 在可重复读级别消除了幻读。
          • Serializable（可串行化）：最高的隔离级别，事务串行化执行，没有并发。

          修改隔离级别示例：
            --显示数据库隔离级别
            show transaction_isolation;




            --事务中修改隔离级别
            begin;
            set transaction isolation level {serializable|repeatable read|read committed|read uncommitted};
            ……
            ……
            commit;


            04  
            两阶段提交

            PostgreSQL 数据库支持两阶段提交协议（默认禁用此功能，仅在分布式架构中使用）。在分布式系统中，事务中往往包含了多台数据库上的操作，虽然单台数据库的操作能够保证原子性，但多台数据库之间的原子性就需要通过两阶段提交来实现了，两阶段提交是实现分布式事务的关键。

            两阶段提交协议有如下 5 个步骤:

            • 应用程序先调用各台数据库做一些操作，但不提交事务。然后应用程序调用事务协调器（该协调器可能也是由应用自己实现的）中的提交方法。

            • 事务协调器将联络事务中涉及的每台数据库，并通知它们准备提交事务，这是第一阶段的开始，此时 PostgreSQL 中调用 PREPARE TRANSACTION 命令。

            • 各台数据库接收到 PREPARE TRANSACTION 命令后，PostgreSQL 会将已准备好提交的信息写入持久存储区中，如果无法完成此，会直接返回失败给事务协调器。

            • 事务协调器接收所有数据库的响应。

            • 在第二阶段，如果任何一个数据库在第一阶段返回失败，则事务协调器将会发一个回滚命令 “ROLLBACK PREPARED” 给各台数据库。如果所有数据库的响应都是成功的，则向各台数据库发送 COMMIT PREPARED 命令，通知各台数据库事务成功。

            示例：
              --修改 max_prepared_transactions 参数，重启数据库生效
              show max_prepared_transactions;
              alter system set max_prepared_transactions = 10;
              pg_ctl restart
              show max_prepared_transactions;




              --创建测试表
              create table testtab01(id int primary key);




              --开启事务，插入数据并进行第一阶段提交
              postgres=# begin;
              BEGIN
              postgres=*# insert into testtab01 values(1);
              INSERT 0 1
              postgres=*# PREPARE TRANSACTION 'osdba_global_trans_0001';
              PREPARE TRANSACTION
              postgres=# table testtab01;
               id
              ----
              (0 rows)




              --重启数据库后进行第二阶段提交
              [postgres@localhost ~]$ pg_ctl restart
              waiting for server to shut down.... done
              server stopped
              waiting for server to start....2023-07-24 17:01:33.206 CST [10882] LOG:  00000: redirecting log output to logging collector process
              2023-07-24 17:01:33.206 CST [10882] HINT:  Future log output will appear in directory "logs".
              2023-07-24 17:01:33.206 CST [10882] LOCATION:  SysLogger_Start, syslogger.c:674
               done
              server started
              [postgres@localhost ~]$ psql
              psql (13.6)
              Type "help" for help.




              postgres=# table testtab01;
               id
              ----
              (0 rows)




              postgres=# COMMIT PREPARED 'osdba_global_trans_0001';
              COMMIT PREPARED
              postgres=# table testtab01;
               id
              ----
                1
              (1 row)


              原文作者：CSDN博主[脑子进水养啥鱼？]
              原文链接：
              https://xiaosonggong.blog.csdn.net/article/details/131940967


              END

              为促进团队内外的沟通联系，我们Klustron团队的bbs论坛开始上线，欢迎各位同学使用！（链接：https://forum.klustron.com/，或者点击文末“阅读原文”，即可跳转）

              论坛目前是测试版，可能还存在不稳定的现象，欢迎各位老师、朋友共享信息，如果遇到问题还请谅解。

              欢迎大家下载和安装Klustron数据库集群，并免费使用（无需注册码）。

              Klustron 完整软件包下载：
              http://downloads.klustron.com/

              如需购买请邮箱联系sales_vip@klustron.com，有相关问题欢迎添加下方小助手微信联系🌹

              产品文档

              Klustron 快速入门：
              https://doc.klustron.com/zh/Klustron_Instruction_Manual.html

              Klustron 快速体验指南：
              https://doc.klustron.com/zh/Klustron_Quickly_Guide.html

              Klustron 功能体验范例：
              https://doc.klustron.com/zh/Klustron-function-experience-example.html

              Klustron 产品使用和测评指南：
              https://doc.klustron.com/zh/product-usage-and-evaluation-guidelines.html


               点击👆上方，关注获取源代码及技术信息~







              kunlunbasepostgresql数据库事务隔离级别事务
              文章转载自KunlunBase 昆仑数据库，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

              评论