【PostgreSQL】守护数据安全：事务与数据完整性管理

目录

事务管理：确保操作的原子性

事务的概念与重要性

事务的启动与提交

事务的回滚（ROLLBACK）（

数据一致性与隔离级别

隔离级别的解释

设置隔离级别

错误处理与事务的高级策略

异常处理（SAVEPOINT & EXCEPTION）

自动提交与手动控制

注意

并发控制与锁

悲观锁(Pessimistic Lock)

乐观锁(Optimistic Lock)

---

事务管理：确保操作的原子性

    事务是数据库操作的基本单位，确保数据的一致性和完整性，本节深入探讨事务的定义、启动与结束。

事务的概念与重要性

事务是一系列操作的集合，这些操作要么全部成功，要么全部失败，保持数据的原子性。理解事务对于防止数据不一致至关重要。

事务的启动与提交

• BEGIN：显式开启一个新的事务。

BEGIN;
INSERT INTO accounts (balance) VALUES (1000);

• COMMIT：提交事务，永久保存更改。

  COMMIT;

  事务的回滚（ROLLBACK）（

• 当事务中的一部分操作失败时，使用ROLLBACK撤销所有已做的更改。

  /*由于错误，账户余额减少的操作被撤销，保持数据的原始状态。*/
  BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
  -- 假设这里出现了一个错误
  ROLLBACK;

  数据一致性与隔离级别

  理解事务的隔离级别是处理并发操作时保持数据一致性的关键。

  隔离级别的解释

• READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE：从低到高依次提供更强的隔离性，但也可能影响并发性能。

• 示例说明隔离级别差异：

  -- 假设有两个事务T1和T2同时操作账户表
  -- 在SERIALIZABLE级别下，T1的读取不会看到T2未提交的更改，确保数据的一致性。

  设置隔离级别

• 显示当前会话的隔离级别：

SHOW transaction_isolation;

• 修改隔离级别：

  SET SESSION transaction_isolation TO 'REPEATABLE READ';

  错误处理与事务的高级策略

  掌握如何在遇到错误时优雅地处理事务，以及使用保存点和异常块等高级特性。

  异常处理（SAVEPOINT & EXCEPTION）

• 使用SAVEPOINT标记事务中的特定点，以便在遇到错误时回滚到该点而不是整个事务。

  BEGIN;
  SAVEPOINT before_update;
  UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
  -- 如果更新失败
  ROLLBACK TO SAVEPOINT before_update;

  自动提交与手动控制

     在PostgreSQL中，事务的管理对于数据一致性和可靠性至关重要。以下是关于PostgreSQL自动提交与手动控制事务的区别：

特性	自动提交模式	手动提交模式
默认行为	默认开启（大多数情况下，除非特定配置更改）	需要手动开启，某些版本或配置下可能为默认
事务边界	每个SQL语句执行完后自动提交，形成独立事务	事务开始于BEGIN，结束于COMMIT或ROLLBACK
控制粒度	粗粒度，每个操作自动完成	细粒度，允许一组操作作为一个整体提交或回滚
数据修改即时性	数据修改立即生效，无法撤销单个语句	数据修改直到COMMIT才对外可见，可控制回滚
示例代码	直接执行SQL语句，如 INSERT INTO table VALUES (...);	BEGIN; <SQL操作>; COMMIT; 或 ROLLBACK;
适用场景	快速操作，无需复杂事务逻辑或高度一致性的简单应用	需要精确控制事务边界，保证数据完整性和一致性

注意

• 自动提交简化了编程模型，减少了忘记提交事务的风险，但可能不适合需要多个操作原子性完成的场景。
• 手动提交提供了更细粒度的控制，适用于需要执行多条SQL语句且这些语句必须全部成功或全部失败的情况。
• 在PostgreSQL中，可以通过设置AUTOCOMMIT参数来改变默认行为，例如使用SET AUTOCOMMIT TO OFF;来关闭自动提交，进入手动提交模式。

并发控制与锁

    在PostgreSQL中，悲观锁和乐观锁是两种不同的并发控制策略，用于处理多用户环境下对共享资源的访问，以避免数据不一致性和并发冲突。

悲观锁(Pessimistic Lock)

     悲观锁假定在事务处理过程中数据很可能发生并发冲突，因此采取一种预防性的锁定策略。在开始读取或修改数据前，悲观锁会先锁定数据，确保在整个事务期间，没有其他事务能够修改这些数据。这种方式可以有效防止并发冲突，但可能会降低系统的并发性能，因为资源被锁定期间，其他需要访问这些资源的事务会被阻塞等待。

在PostgreSQL中，可以使用SELECT ... FOR UPDATE或SELECT ... FOR SHARE语句显式地申请悲观锁。这些锁会在事务结束时自动释放，无论是通过COMMIT还是ROLLBACK。例如：

BEGIN;
SELECT * FROM my_table WHERE id = 1 FOR UPDATE;
-- 进行一些业务逻辑处理...
COMMIT;

这段代码会锁定id为1的行，阻止其他事务修改或删除这行数据，直到当前事务结束。

乐观锁(Optimistic Lock)

乐观锁则假设在事务处理过程中数据并发冲突的概率较低，因此它不会一开始就锁定数据。相反，乐观锁会在事务开始时记录数据的一个版本标识（通常是通过在表中添加一个版本字段，如version），然后在事务提交前检查数据的版本是否发生变化。如果版本未变，说明没有冲突，事务可以正常提交；如果有其他事务已经修改了数据（即版本号不同），则当前事务通常会回滚，由应用程序决定如何处理这种情况（如重新尝试事务）。

在PostgreSQL中，乐观锁的实现依赖于应用程序的逻辑，通常涉及在更新时检查数据版本。例如：

• 读取数据时，同时读取版本字段：

SELECT * FROM my_table WHERE id = 1;

• 更新数据时，比较版本字段：

  BEGIN;
  UPDATE my_table 
  SET column = value, version = version + 1 
  WHERE id = 1 AND version = original_version_from_read;
  
  -- 如果影响行数为0，表示版本不匹配，可能有并发修改
  GET DIAGNOSTICS result = ROW_COUNT;
  IF result = 0 THEN
      -- 处理并发冲突，如回滚事务或重新尝试
  END IF;
  COMMIT;

      在这个例子中，version字段在更新时会增加，更新语句只有在原始读取的版本与数据库中的版本相匹配时才会执行成功，从而检测并发修改。

      悲观锁适用于并发冲突频繁、对数据一致性和实时性要求较高的场景，而乐观锁适用于并发冲突较少、追求高并发性能的场景。选择哪种锁策略需根据实际应用场景权衡。