PostgreSQL 学习笔记 - 事务和锁-极客日记

事务的概念

在数据库中，事务是指一组相关的数据库操作，这些操作作为一个整体进行。在事务中，如果一个操作失败，那么整个事务都将被回滚。这确保了数据的完整性和一致性，因为如果一个操作失败，整个事务都将被回滚到之前的状态。

在 PostgreSQL 中，可以使用 BEGIN、COMMIT 和 ROLLBACK 语句来管理事务。BEGIN 语句开始一个新的事务，COMMIT 语句提交当前事务，并将其更改持久保存到数据库中，ROLLBACK 语句撤消当前事务中的所有更改。

以下是一个示例，展示如何使用事务来插入数据到数据库中：

BEGIN;
INSERT INTO users (name, email) VALUES ('John Doe', 'johndoe@example.com');
INSERT INTO orders (user_id, amount) VALUES (1, 100.00);
COMMIT;

在这个例子中，我们开始了一个新的事务，插入了一些数据，然后提交了事务。如果有任何问题，我们可以回滚这个事务，这将撤消所有更改。

事务的隔离级别

在并发访问的情况下，多个事务可以同时访问数据库。这可能会导致一些问题，比如脏读（dirty read）、不可重复读（non-repeatable read）和幻读（phantom read）。为了解决这些问题，PostgreSQL 使用了隔离级别的概念。

隔离级别定义了一个事务能够看到其他事务中的更改的程度。PostgreSQL 支持四个隔离级别：

READ UNCOMMITTED：事务可以读取其他事务未提交的更改，可能会出现脏读、不可重复读和幻读的问题。
READ COMMITTED：事务只能读取其他事务已提交的更改，可以解决脏读问题，但可能会出现不可重复读和幻读的问题。
REPEATABLE READ：事务在开始时创建一个一致性快照，并在整个事务期间使用该快照来读取数据。这可以解决脏读和不可重复读的问题，但可能会出现幻读的问题。
SERIALIZABLE：事务以一种连续的方式运行，就像它们是按顺序运行的一样。这可以解决所有并发问题，但可能会导致性能下降，因为事务必须等待其他事务完成。

在 PostgreSQL 中，默认隔离级别是 READ COMMITTED。可以使用 SET TRANSACTION ISOLATION LEVEL 语句来设置事务的隔离级别。例如，以下是如何将事务隔离级别设置为 REPEATABLE READ：

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- 在此处执行事务操作
COMMIT;

锁的概念

在并发访问的情况下，多个事务可以同时访问相同的数据。这可能会导致一些问题，如数据竞争、死锁等。为了解决这些问题，PostgreSQL 使用了锁的概念。

锁是一种机制，用于控制对共享资源的访问。在 PostgreSQL 中，有多种类型的锁可用，如共享锁（Shared Lock）、排他锁（Exclusive Lock）等。

共享锁：多个事务可以同时持有共享锁，但不能同时持有排他锁。共享锁用于读取共享资源，如读取表中的数据。
排他锁：只有一个事务可以持有排他锁，且其他事务不能同时持有任何类型的锁。排他锁用于修改共享资源，如更新表中的数据。
在 PostgreSQL 中，可以使用 SELECT ... FOR UPDATE 和 SELECT ... FOR SHARE 语句来获取锁。例如，以下是如何获取一个行的排他锁：

SELECT * FROM users WHERE id = 1 FOR UPDATE;

在这个例子中，我们使用 FOR UPDATE 子句来获取一个行的排他锁。这将阻止其他事务同时获取相同的锁，直到当前事务完成。

锁的粒度

在 PostgreSQL 中，锁的粒度可以是行级别或表级别。表级别锁定整个表，而行级别锁只锁定特定的行。

在进行并发访问时，通常需要权衡锁定的粒度。如果使用表级别锁，则在操作期间可能会阻止其他事务访问整个表。但如果使用行级别锁，则可能会导致大量的锁竞争，从而影响性能。

PostgreSQL 还提供了一种称为间隙锁（Gap Lock）的特殊锁定类型，用于锁定不存在的行。这可以防止其他事务在查询期间插入新行。间隙锁的使用可以防止幻读的问题。

示例代码
在下面的示例中，我们将使用一个简单的表来演示事务和锁的使用。以下是创建表的 SQL 语句：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    quantity INTEGER NOT NULL
);

在这个表中，我们将存储产品的名称和数量。接下来，我们将插入一些示例数据：

INSERT INTO products (name, quantity) VALUES ('Product A', 10);
INSERT INTO products (name, quantity) VALUES ('Product B', 20);
INSERT INTO products (name, quantity) VALUES ('Product C', 30);

接下来，我们将使用一个简单的 ruby 脚本来演示如何使用事务和锁。在这个脚本中，我们将使用 pg 库来连接到 PostgreSQL 数据库，并执行一些事务和锁定操作。
我们将连接到名为 mydb 的数据库，并使用一个简单的 products 表，其中包含产品的名称和数量。

require 'pg'

# 连接到 PostgreSQL 数据库
conn = PG.connect(
  host: 'localhost',
  dbname: 'mydb',
  user: 'myuser',
  password: 'mypassword'
)

begin
  # 开始一个新事务
  conn.transaction do |c|

    # 查询第一行并获取排他锁
    c.exec_params('SELECT * FROM products WHERE id = $1 FOR UPDATE', [1])

    # 等待 5 秒钟
    sleep(5)

    # 更新第一行的数量
    c.exec_params('UPDATE products SET quantity = quantity - $1 WHERE id = $2', [1, 1])

  end

rescue PG::Error => e
  # 回滚事务
  conn.exec('ROLLBACK')
  puts "Error: #{e.message}"

else
  # 提交事务
  conn.exec('COMMIT')

ensure
  # 关闭数据库连接
  conn.close
end

在这个脚本中，我们首先使用 PG.connect 方法连接到数据库。然后，我们使用 conn.transaction 块开始一个新的事务。

在事务块中，我们首先使用 c.exec_params 方法查询 ID 为 1 的产品，并获取了一个排他锁。我们使用 $1 和 $2 占位符，这样可以避免 SQL 注入攻击。

然后，我们使用 sleep(5) 方法模拟了一个长时间的操作，例如处理大量数据或执行其他复杂操作。在这段时间内，其他事务将无法访问相同的数据行。

最后，我们使用 c.exec_params 方法更新 ID 为 1 的产品的数量，将其减少 1。我们再次使用 $1 和 $2 占位符来避免 SQL 注入攻击。

如果在事务中发生了错误，我们使用 conn.exec('ROLLBACK')` 方法回滚事务。否则，我们使用 conn.exec('COMMIT') 方法提交事务。最后，我们使用 conn.close 方法关闭数据库连接。

注意，如果在一个事务中获取了锁，并且在锁定期间执行了其他操作（例如睡眠），则锁将一直保持到事务结束为止，即使锁定的数据行已经被修改或删除。因此，使用锁时需要小心，避免出现死锁等问题。

目录CONTENT

PostgreSQL 学习笔记 - 事务和锁

事务的概念

事务的隔离级别

锁的概念

锁的粒度

评论区