7. 管理对数据的并发访问

到目前为止，我们使用的都是仅由我们自己使用的表。实际上，在多用户机器上，数据通常会在不同用户之间共享。这引出了一个问题：谁可以使用某个表，以及以何种权限（查询、插入、删除、追加等）？

7.1. 创建 Firebird 用户

在使用 IB-Expert 时，我们是以 SYSDBA 用户身份登录的。该信息可在与 DBMS 建立的连接属性中找到：

在右侧，我们可以看到登录用户为 [SYSDBA]。但我们看不到其密码 [masterkey]。[SYSDBA] 是 Firebird 中的特殊用户：它对 DBMS 管理的所有对象拥有完全权限。您可以在 IBExpert 中通过 [工具 / 用户管理] 选项或以下图标创建新用户：

这将打开用户管理窗口：

点击 [添加] 按钮即可创建新用户：

让我们创建以下用户：

用户名	密码
ADMIN1	admin1
ADMIN2	admin2
SELECT1	select1
SELECT2	select2
更新1	update1
更新2	更新2

7.2. 授予用户访问权限

数据库归创建它的用户所有。到目前为止，我们创建的数据库都属于用户 [SYSDBA]。为了说明权限的概念，让我们在用户 [ADMIN1, admin1] 身份下创建（数据库 / 创建数据库）一个新数据库：

并将其注册为别名 DBACCES (ADMIN1)。使用别名可让您使用不同的用户名连接到同一数据库，从而在 IBExpert 数据库资源管理器中更容易识别它们。 :

现在，让我们创建以下两个表：TA 和 TB：

表 TA

表 TB

这些表之间没有关联。

使用 IB-Expert，让我们创建第二个连接到 [DBACCES] 数据库的连接，这次使用名称 [ADMIN2 / admin2]。为此，我们将使用 [数据库 / 注册数据库] 选项：

选择 DBACCES(ADMIN2) 并打开 SQL 编辑器（Shift + F12）：

我们将有机会使用多种连接访问同一个数据库 [DBACCES]。每种连接都会对应一个 SQL 编辑器。在 [1] 中，SQL 编辑器会显示所连接数据库的别名。请利用此信息判断您当前所在的 SQL 编辑器。这一点非常重要，因为我们将创建对数据库对象具有不同访问权限的连接。

让我们查询 TA 表的内容：

我们收到以下错误信息：

这是什么意思？[DBACCESS] 数据库是由用户 [ADMIN1] 创建的，因此归该用户所有。只有该用户才能访问此数据库中的各种对象。他们可以使用 SQL GRANT 命令向其他用户授予访问权限。该命令有多种语法，其中一种如下：

语法	GRANT 权限1, 权限2, ...| ALL PRIVILEGES ON 表/视图 授予 user1, user2, ...| 公共 [ WITH GRANT OPTION ]
操作	向特定用户或所有用户（PUBLIC）授予表或视图上的特定访问权限或所有权限（ALL PRIVILEGES）。WITH GRANT OPTION 子句允许被授予权限的用户进一步将这些权限授予其他用户。

可授予的权限包括以下内容：

DELETE	在表或视图上使用 DELETE 命令的权限。
INSERT	对表或视图使用 INSERT 命令的权限
SELECT	对表或视图使用SELECT命令的权限
UPDATE	对表或视图使用 UPDATE 命令的权限。可通过以下语法将此权限限制为特定列：GRANT update (col1, col2, ...) ON table/view TO user1, user2, ...| PUBLIC [WITH GRANT OPTION]

让我们授予用户 [ADMIN2] 对表 TA 的 SELECT 权限。只有表所有者才能授予此权限，即本例中的 [ADMIN1]。切换到 DBACCES(ADMIN1) 连接并打开一个新的 SQL 编辑器（Shift+F12）：

从现在开始，我们将在这两个 SQL 编辑器之间切换。要在它们之间切换，您可以使用 [Windows] 菜单选项：

上图中，我们可以看到两个 SQL 编辑器，每个都与特定用户相关联。让我们回到 SQL 编辑器（ADMIN1），并输入以下命令：

然后使用 COMMIT 进行提交：

现在，让我们切换到 ADMIN2 用户的编辑器，重新运行之前失败的 SELECT 语句：

我们收到以下错误信息：

用户 [ADMIN2] 仍然没有查看 [TA] 表的权限。事实上，用户的权限似乎是在登录时加载的。因此，[ADMIN2] 仍将拥有与首次登录时相同的权限，即没有任何权限。让我们验证一下。请让用户 [ADMIN2] 注销：

• 选择其连接
• 右键单击该连接并选择 [断开与数据库的连接] 选项，或按 (Shift + Ctrl + D) 执行注销操作

如果弹出对话框提示执行 [COMMIT]，请执行 [COMMIT]。然后通过选择上方的 [重新连接] 选项重新连接用户 [ADMIN2]。完成后，返回 SQL 编辑器（ADMIN2）并重新运行之前失败的 SELECT 查询：

随后将得到以下结果：

这次，由于表所有者 ADMIN1 授予了 SELECT 权限，ADMIN2 可以查看 TA 表。通常，这是他们拥有的唯一权限。让我们验证一下。仍在 SQL 编辑器（ADMIN2）中：

右侧屏幕显示，ADMIN2 没有对 TA 表的 DELETE 权限。

让我们回到 SQL 编辑器（ADMIN1），为用户 ADMIN2 授予更多权限。我们依次执行以下两条命令：

• 第一条命令授予用户 ADMIN2 对 [TA] 表的完全访问权限，并允许其向他人授予权限（WITH GRANT OPTION）
• 第二条命令用于验证前一条命令

完成上述操作后，与之前一样，请刷新 [ADMIN2] 用户的连接（断开/重新连接），然后在 SQL 编辑器（ADMIN2）中输入以下命令：

ADMIN2 已成功从 TA 表中删除了所有行。现在让我们使用 ROLLBACK 撤销此次删除操作：

现在让我们验证 ADMIN2 是否能够对 TA 表授予权限。

现在，让我们以 [SELECT1 / select1] 的用户名（即之前创建的用户之一）连接到 [DBACCES] 数据库（数据库 / 注册数据库），然后双击 [数据库资源管理器] 中创建的链接：

切换到此新连接，并打开一个新的 SQL 编辑器（Shift + F12），输入以下命令：

SELECT1 用户确实对 TA 表拥有 SELECT 权限。他们能否将此权限授予 SELECT2 用户？

该操作失败是因为用户 SELECT1 未获得授予 SELECT 权限的权限，而该权限是其从用户 ADMIN2 处获得的。要实现这一点，用户 ADMIN2 必须在其 SQL GRANT 语句中使用 WITH GRANT OPTION 子句。授予权限的规则很简单：

• 用户只能授予其已获得的权限，而不能授予更多权限
• 且只有当其获得的权限包含 [WITH GRANT OPTION] 特权时，才能将其授予他人

已授予的权限可通过 REVOKE 语句撤销：

语法	REVOKE 权限1, 权限2, ...| ALL PRIVILEGES ON 表/视图 FROM user1, user2, ...| PUBLIC
操作	撤销用户（user1, user2, ...）或所有用户（PUBLIC）对该表或视图的访问权限（privilege1）或所有权限（ALL PRIVILEGES）。

让我们试一试。返回 ADMIN2 SQL 编辑器，撤销我们授予用户 SELECT1 的 SELECT 权限：

让我们先断开 SELECT1 用户的会话，然后重新连接。接着，在 SQL 编辑器（SELECT1）中，查询 TA 表的内容：

SELECT1 用户确实已失去对 TA 表的 SELECT 权限。请注意，授予此权限的是 ADMIN2，撤销权限的也是 ADMIN2。如果由 ADMIN1 尝试撤销该权限，虽然不会报错，但我们可以看到 SELECT1 用户仍保留了 SELECT 权限。

可以通过以下语法将权限授予所有人：GRANT 权限 ON 表/视图 TO PUBLIC。让我们将 TA 表的 SELECT 权限授予所有人。我们可以使用 ADMIN1 或 ADMIN2 来执行此操作。我们将使用 ADMIN2：

现在，让我们使用用户 USER1 / user1 连接到数据库：

使用 DBACCES(USER1) 连接后，打开一个新的 SQL 编辑器（Shift + F12），并输入以下命令：

用户 USER1 确实对 TA 表拥有 SELECT 权限。

7.3. 事务

7.3.1. 隔离级别

接下来，我们将从数据库对象的访问权限问题转向这些对象的并发访问问题。假设两位用户都拥有对某个数据库对象（例如一张表）的充分访问权限，并希望同时使用该对象。此时会发生什么情况？

每个用户都在事务中进行操作。事务是一系列以“原子性”方式执行的 SQL 语句：

• 要么所有操作都成功
• 要么其中一个失败，此时所有先前操作都会被回滚

最终，事务中的操作要么全部成功应用，要么全部未被应用。当用户能够控制事务时（如本文档所述），他们可以使用 COMMIT 语句提交事务，或使用 ROLLBACK 语句回滚事务。

每个用户都在属于自己的事务中进行操作。不同用户之间通常存在四个隔离级别：

• 未提交读
• 已提交读
• 可重复读
• 可串行化

未提交读

此隔离级别也被称为“脏读”。以下是一个在此模式下可能发生的情况示例：

1. 用户 U1 在表 T 上启动事务
2. 用户 U2 在同一张表 T 上启动事务
3. 用户 U1 修改了表 T 中的行，但尚未提交
4. 用户 U2 “看到”了这些更改，并根据所见内容做出决策
5. 该用户使用 ROLLBACK 回滚其事务

我们可以看到，在第 4 步中，用户 U2 基于数据做出了决策，而这些数据后来被证明是错误的。

已提交读取

此隔离级别可避免上述问题。在此模式下，步骤4中的用户U2将无法“看到”用户U1对表T所做的更改。只有在U1提交事务后，U2才能看到这些更改。

在此模式下（也称为“不可重复读”），可能会出现以下情况：

1. 用户 U1 在表 T 上启动一个事务
2. 用户 U2 在同一张表 T 上启动事务
3. 用户 U2 执行 SELECT 语句，以获取满足特定条件的 T 表行中 C 列的平均值
4. 用户 U1 修改（UPDATE）表 T 中列 C 的某些值，并提交（COMMIT）更改
5. 用户 U2 重复执行步骤 3 中的 SELECT 语句。他们会发现，由于 U1 进行的修改，列 C 的平均值已经发生了变化。

现在，用户 U2 只能看到 U1 “提交”的更改。但在同一事务中，两个相同的操作（步骤 3 和 5）却产生了不同的结果。术语“不可重复读”指的就是这种情况。对于希望获得表 T 一致视图的人来说，这种情况会带来问题。

可重复读

在此隔离级别下，只要用户始终处于同一事务中，其数据库读取操作就保证得到相同的结果。用户操作的是一张快照，该快照永远不会反映其他事务所做的更改，即使这些更改已被提交。只有当用户自己通过 COMMIT 或 ROLLBACK 结束事务后，才会看到这些更改。

然而，此隔离级别尚不完美。 在上述操作 3 之后，用户 U2 查询的行已被锁定。在操作 4 期间，用户 U1 将无法修改（UPDATE）这些行中 C 列的值。但他们可以插入新行（INSERT）。如果部分新增行满足操作 3 中测试的条件，由于这些新增行的存在，操作 5 得出的平均值将与操作 3 中的结果不同。

为解决这一新问题，必须切换至“可串行化”隔离级别。

可串行化

在此隔离级别下，事务之间完全相互隔离。它确保同时执行的两个事务的结果，与它们依次执行时完全一致。为了实现这一结果，在操作 4 中，当用户 U1 尝试插入会改变其 SELECT 结果的行时，系统将阻止其操作。 系统将显示一条错误消息，告知用户无法执行插入操作。只有在用户 U2 提交其事务后，该操作才变得可行。

并非所有数据库管理系统（DBMS）都支持这四种 SQL 事务隔离级别。Firebird 提供以下隔离级别：

• 快照（snapshot）：默认隔离模式。对应于 SQL 标准的“可重复读”模式。
• 已提交读（committed read）：对应于 SQL 标准的“已提交读”模式

此隔离级别通过 SET TRANSACTION 命令设置：

语法	SET TRANSACTION [READ WRITE | READ ONLY] [WAIT|NOWAIT] 隔离级别[快照 | 已提交读]
功能	带下划线的关键字为默认值 读写：事务可以读写 只读：事务仅可读取 WAIT：若两个事务发生冲突，无法完成操作的事务将等待直至另一个事务提交。该事务将无法再执行 SQL 语句。 NOWAIT：无法完成操作的事务不会被阻塞。它会收到一条错误消息，并可继续工作。 隔离级别 [快照 | 已提交读]：隔离级别

让我们试一试。在 SQL 编辑器（ADMIN1）中，输入以下 SQL 命令：

我们看到该操作未获授权。我们不知道原因……

IB-Expert 还支持通过另一种方式设置隔离模式。右键单击 DBACCES(ADMIN1) 连接，选择 [数据库注册信息] 选项：

右侧屏幕显示了 [事务] 选项。这将允许我们设置事务隔离级别。在此，我们将它设置为 [快照]。对 DBACCES(ADMIN2) 连接也进行同样的设置。

7.3.2. 快照模式

让我们来探讨一下快照隔离级别，这是 Firebird 的默认隔离模式。当用户启动事务时，系统会生成数据库的快照。随后，用户便在此快照上进行操作。因此，每个用户都在各自的数据库快照上工作。如果用户对快照进行了修改，其他用户是无法看到的。只有当进行修改的用户通过 COMMIT 提交后，其他用户才能看到这些更改。

可能存在两种情况：

• 一个用户正在读取表（SELECT），而另一个用户正在修改表（INSERT、UPDATE、DELETE）
• 两个用户都想同时修改该表

7.3.2.1. 一致性读取原则

假设两个用户 U1 和 U2 正在操作同一张表 TAB：

      --------+----------+--------+-------+----------------------
              T1a       T2a      T1b     T2b 

用户 U1 的事务始于时间 T1a，终于时间 T1b。

用户 U2 的事务始于时间 T2a，终于时间 T2b。

U1 基于在时间 T1a 截取的 TAB 快照进行操作。在 T1a 到 T1b 之间，他们修改了 TAB。其他用户在时间 T1b 之前无法访问这些修改，直到 U1 执行 COMMIT 操作。

U2 基于在时间 T2a 截取的 TAB 快照进行操作，该快照与 U1 使用的快照相同（前提是期间没有其他用户修改了原始数据）。他无法“看到”用户 U1 对 TAB 可能做出的更改。他只能在时间 T1b 时看到这些更改。

让我们通过 [DBACCES] 数据库来说明这一点。我们将让两个用户 [ADMIN1] 和 [ADMIN2] 同时工作。切换到 DBACCES(ADMIN1) 连接，并在 ADMIN1 的 SQL 编辑器中执行以下操作：

ADMIN1 已修改了表 TA 的第 2 行，但尚未提交（COMMIT）该操作。随后，用户 ADMIN2 对表 TA 执行了一条 SELECT 语句（我们切换到 ADMIN2 的 SQL 编辑器）。此时，我们处于示例中的时间点 T2a 之前。

回到 ADMIN1 的 SQL 编辑器，该编辑器将提交更新：

回到 ADMIN2 的 SQL 编辑器，重新运行 SELECT 语句：

ADMIN2 可以看到 ADMIN1 所做的更改。在快照模式下，一个事务在其他事务完成之前，是看不到这些事务所做的更改的。

7.3.2.2. 两个事务对同一数据库对象的并发修改

让我们以会计为例：U1 和 U2 正在处理账目。U1 从账户 X 借记金额 S，并向账户 Y 贷记相同金额。他将分几个步骤完成此操作：

      --------+----------+--------+-------+----------------------
              T1a       T1b     T1c      T1d 

用户 U1 在时间 T1a 开始事务，在时间 T1b 从账户 comptex 扣款，在时间 T1c 向账户 comptey 入账，并在时间 T1d 提交这两项操作。此外，假设用户 U2 也想执行同样的操作，其事务在时间 T2a 开始，并按照以下方案在时间 T2d 结束：

      --------+----------+----+----+-------+------+-----+-------+---------
              T1a       T1b  T2a   T1c     T2b   T1d   T2c    T2d

在时间点 T2，为 U2 生成账户表的快照。根据快照原则，该快照是一致的。由于 U1 尚未提交其事务，因此 U2 看到的是 comptex 和 comptey 账户的初始状态。

假设 comptex 的初始余额为 1,000 欧元，且用户 U1 和 U2 都希望从该账户中扣款 100 欧元。

• 在时间点 T1b，U1 从 comptex 账户中扣款 100 欧元，使其余额变为 90 欧元。该交易要到时间点 T1d 才会提交。
• 在时间 T2b，U2 看到 comptex 的余额为 1,000 欧元（一致性读取原则），并从该账户中扣除 100 欧元，使其余额变为 90 欧元。
• 最终，在时间点 T2d，当所有操作均通过验证后，comptex 的余额将为 90 欧元，而非预期的 80 欧元。

解决此问题的方案是，在 U1 完成其交易之前，禁止 U2 修改 comptex。因此，U2 将被阻塞直至时间 T1d。快照模式提供了这一机制。

让我们通过 DBACCES 数据库来说明这一点。ADMIN1 在其 SQL 编辑器中启动了一个事务（ADMIN1）：

我们首先执行了COMMIT，以确保正在开始一个新事务。然后删除了第4行。该事务尚未提交。

随后，ADMIN2 在其 SQL 编辑器中启动了一个事务（ADMIN2）：

右侧屏幕显示，ADMIN2 试图修改第 4 行。系统提示其无法进行此操作，因为其他人已修改该行但尚未提交更改。

让我们回到 SQL 编辑器（ADMIN1）执行 COMMIT 操作：

让我们回到 SQL 编辑器（ADMIN2）再次运行 UPDATE 命令：

尽管第 4 行已不存在，但 UPDATE 操作仍成功完成，如下面的 SELECT 语句所示。正是在这一刻，ADMIN2 发现该行已不存在。

7.3.2.3. 可重复读模式

现在让我们说明“可重复读”模式。该隔离级别由“快照”模式提供。它确保事务在读取数据库时始终获得相同的结果。

首先，我们使用 ADMIN2 的 SQL 编辑器进行操作：

现在让我们切换到 ADMIN1 的 SQL 编辑器：

用户 ADMIN1 添加了两行数据并提交了事务。现在让我们回到 SQL 编辑器（ADMIN2），重新运行 SELECT SUM 语句：

我们可以看到，尽管这些行已通过 COMMIT 提交，但 ADMIN2 并未看到 ADMIN1 添加的行。SELECT SUM 返回的结果与添加行之前相同。这就是可重复读（Repeatable Read）的原则。

现在，仍在 SQL 编辑器（ADMIN2）中，让我们使用 COMMIT 提交事务，然后再次运行 SELECT SUM：

现在，ADMIN1 添加的行已被计入。

7.3.3. 已提交读模式

现在让我们演示一下“已提交读”模式。该隔离级别与快照隔离级别类似，但在“可重复读”方面有所不同。

首先，我们将两个连接的事务隔离级别进行更改。

• 断开两个用户 ADMIN1 和 ADMIN2 的连接
• 我们将它们事务的隔离级别进行修改

• 重新连接用户 ADMIN1 和 ADMIN2

现在，我们将重新审视之前用于说明“可重复读”的示例，以展示其行为已发生变化。首先，让我们使用 ADMIN2 的 SQL 编辑器进行操作：

现在让我们切换到 ADMIN1 的 SQL 编辑器：

用户 ADMIN1 已添加了两行数据并提交了事务。现在让我们回到 SQL 编辑器（ADMIN2）中，重新运行 SELECT SUM 语句：

SELECT SUM 查询返回的结果与 ADMIN1 插入数据之前的结果不同。这就是快照模式与读已提交模式之间的区别。