Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (中文) » SQL Anywhere 服务器 - SQL 的用法 » 创建数据库 » 使用事务和隔离级别 » 隔离级别教程

 

教程:幻像行

在本教程中,您会发现将出现一个幻像行。

注意

要按照此教程正常工作,则一定不要选择 Interactive SQL 中的 [自动释放数据库锁] 选项([工具] » [选项] » [SQL Anywhere])。

  1. 启动两个 Interactive SQL 的实例。请参见教程:非可重复读取的第 1 步到第 4 步。

  2. 在 [Sales Manager] 窗口中执行以下命令,将隔离级别设置为 2。

    SET TEMPORARY OPTION isolation_level = 2;
  3. 在 [Accountant] 窗口中执行以下命令,将隔离级别设置为 2。

    SET TEMPORARY OPTION isolation_level = 2;
  4. 在 [Accountant] 窗口中输入以下命令,列出所有部门。

    SELECT * FROM Departments
    ORDER BY DepartmentID;
    DepartmentID DepartmentName DepartmentHeadID
    100 R & D 501
    200 Sales 902
    300 Finance 1293
    400 Marketing 1576
    500 Shipping 703
  5. 销售经理决定设立一个新部门,专门管理国外市场。Philip Chin(EmployeeID 为 129)将作为这个新部门的经理。

    INSERT INTO Departments
       (DepartmentID, DepartmentName, DepartmentHeadID)
       VALUES(600, 'Foreign Sales', 129);
    COMMIT;

    最后一个命令为这个新部门创建了一个新条目。这个条目作为新的一行出现在销售经理窗口中的表的底部。

    在 [Sales Manager] 窗口中输入以下命令,列出所有部门。

    SELECT *
    FROM Departments
    ORDER BY DepartmentID;
    DepartmentID DepartmentName DepartmentHeadID
    100 R & D 501
    200 Sales 902
    300 Finance 1293
    400 Marketing 1576
    500 Shipping 703
    600 Foreign Sales 129
  6. 但是,会计并不知道这个新部门。处于隔离级别 2 时,数据库服务器通过放置锁来确保所有行都不会发生更改,但却不会通过放置锁来防止其它事务插入新行。

    会计只有再次执行 SELECT 命令才能发现新行。在会计的窗口中,再次执行 SELECT 语句。您将看到新行已附加到表中。

    SELECT *
    FROM Departments
    ORDER BY DepartmentID;
    DepartmentID DepartmentName DepartmentHeadID
    100 R & D 501
    200 Sales 902
    300 Finance 1293
    400 Marketing 1576
    500 Shipping 703
    600 Foreign Sales 129

    出现的新行被称作幻像行,这是因为,从会计的角度来看,该行就象幻影那样,不知从何而来。会计的连接处于隔离级别 2。在该级别上,数据库服务器只在会计使用的行上获取锁。而对其它行则不进行任何控制,因此没有任何限制阻止销售经理插入新行。

  7. 会计希望在将来避免这种奇异的事情发生,因此他将其当前事务的隔离级别提高到级别 3。为会计输入以下命令。

    SET TEMPORARY OPTION isolation_level = 3;
    SELECT *
    FROM Departments
    ORDER BY DepartmentID;
  8. 而销售经理想要再增加一个部门来处理针对大型企业合作伙伴的销售业务。在销售经理的窗口中执行以下命令。

    INSERT INTO Departments
     (DepartmentID, DepartmentName, DepartmentHeadID)
       VALUES(700, 'Major Account Sales', 902);

    销售经理的窗口在执行该命令的过程中将会暂停,因为会计放置的锁阻止了该命令。在工具栏上,单击 [中断 SQL 语句](或选择 [SQL] » [停止]),中断这次输入。

  9. 为了避免更改 SQL Anywhere 示例数据,您应该回退未完成的插入 Major Account Sales 部门行的事务,并使用另一个事务删除 Foreign Sales 部门。

    1. 在销售经理的窗口中执行以下命令,回退最后的未完成事务:

      ROLLBACK;
    2. 同样在销售经理的窗口中,执行以下两条语句,删除以前插入的行并提交此操作。

      DELETE FROM Departments
      WHERE DepartmentID = 600;
      COMMIT;
解释

在会计将他的隔离级别提高到级别 3 并再次选择了 Departments 表中的所有行之后,数据库服务器在该表中的每一行上都放置了防插入锁,并且添加了一个额外的幻像锁以防止在该表的末尾插入新行。当销售经理尝试在该表的末尾插入新行时,就是这最后一个锁阻塞了她的命令。

注意,即使销售经理的连接仍处于隔离级别 2,她的命令也会被阻塞。数据库服务器按照每个事务的隔离级别和语句的要求放置防插入锁(与读锁定类似)。一旦放置这些锁定,所有其它并发事务必须都遵从。

有关锁定的详细信息,请参见锁定的工作方式

使用快照隔离避免幻像行

您可以使用快照隔离级别维护与隔离级别 3 相同的一致性,而且不会造成任何种类的阻塞。销售经理的命令未被阻塞,会计也不会看到幻像行。

如果尚未执行教程:幻像行中的第 1 步到第 4 步,请执行这些步骤。这些步骤介绍了如何启动两个 Interactive SQL 实例。

  1. 通过执行以下命令为会计启用快照隔离。

    SET OPTION PUBLIC. allow_snapshot_isolation = 'On';
    SET TEMPORARY OPTION isolation_level = snapshot;
  2. 在 [Accountant] 窗口中输入以下命令,列出所有部门。

    SELECT * FROM Departments
    ORDER BY DepartmentID;
    DepartmentID DepartmentName DepartmentHeadID
    100 R & D 501
    200 Sales 902
    300 Finance 1293
    400 Marketing 1576
    500 Shipping 703
  3. 销售经理决定设立一个新部门,专门管理国外市场。Philip Chin(EmployeeID 为 129)将作为这个新部门的经理。

    INSERT INTO Departments
       (DepartmentID, DepartmentName, DepartmentHeadID)
       VALUES(600, 'Foreign Sales', 129);
    COMMIT;

    最后一个命令为这个新部门创建了一个新条目。这个条目作为新的一行出现在销售经理窗口中的表的底部。

    SELECT * FROM Departments
    ORDER BY DepartmentID;
    DepartmentID DepartmentName DepartmentHeadID
    100 R & D 501
    200 Sales 902
    300 Finance 1293
    400 Marketing 1576
    500 Shipping 703
    600 Foreign Sales 129
  4. 会计可以再次执行他的查询,并且不会看到新行,这是因为事务尚未结束。

    SELECT *
    FROM Departments
    ORDER BY DepartmentID;
    DepartmentID DepartmentName DepartmentHeadID
    100 R & D 501
    200 Sales 902
    300 Finance 1293
    400 Marketing 1576
    500 Shipping 703
  5. 而销售经理想要再增加一个部门来处理针对大型企业合作伙伴的销售业务。在销售经理的窗口中执行以下命令。

    INSERT INTO Departments
     (DepartmentID, DepartmentName, DepartmentHeadID)
       VALUES(700, 'Major Account Sales', 902);

    销售经理的更改未被阻塞,因为会计在使用快照隔离。

  6. 会计必须结束他的快照事务,以查看销售经理提交到数据库的更改。

    COMMIT;
       SELECT * FROM Departments
       ORDER BY DepartmentID;

    现在会计看到了 Foreign Sales 部门,但没有看到 Major Account Sales 部门。

    DepartmentID DepartmentName DepartmentHeadID
    100 R & D 501
    200 Sales 902
    300 Finance 1293
    400 Marketing 1576
    500 Shipping 703
    600 Foreign Sales 129
  7. 为了避免更改 SQL Anywhere 示例数据,您应该回退未完成的插入 Major Account Sales 部门行的事务,并使用另一个事务删除 Foreign Sales 部门。

    1. 在销售经理的窗口中执行以下命令,回退最后的未完成事务:

      ROLLBACK;
    2. 同样在销售经理的窗口中,执行以下两条语句,删除之前插入的行并提交此操作。

      DELETE FROM Departments
      WHERE DepartmentID = 600;
      COMMIT;