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

SQL Anywhere 12.0.0 (中文) » SQL Anywhere 服务器 - SQL 的用法 » 使用事务和隔离级别 » 隔离级别教程

 

教程:锁定的实际含义

在本教程中,会计和销售经理都有涉及 SalesOrder 和 SalesOrderItems 表的任务。会计需要核实根据销售雇员在 2001 年 4 月的销售情况付给他们的佣金支票的金额。销售经理发现有几个订单没有添加到数据库中,想要添加这些订单。

他们的工作演示了幻像锁定。幻像锁是一个共享锁,放置在带有索引的扫描位置上以防止幻像行。当使用隔离级别 3 的事务选择了符合指定条件的行时,数据库服务器将放置防插入锁,以防止其它事务插入也符合指定条件的行。替您放置的锁的数量取决于搜索条件和数据库的设计。

注意

要按照此教程正常工作,则一定不要选择 Interactive SQL 中的 [自动释放数据库锁] 选项。通过选择 [工具] » [选项],然后在左窗格中单击 [SQL Anywhere],可以检查此选项的设置。

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

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

    SET TEMPORARY OPTION isolation_level = 2;
  3. 每个月都会向销售代表支付佣金,所付佣金是他们在该月的销售额乘以一定的百分比。会计正在准备 2001 年 4 月的佣金支票。他的第一项任务是计算各个销售代表在该月的总销售额。

    在会计的窗口中输入以下命令。价格、销售订单信息和雇员数据存储在单独的表中。使用外键关系连接这些表,以将所需信息合并到一起。



    SELECT EmployeeID, GivenName, Surname,
       SUM( SalesOrderItems.Quantity * UnitPrice )
          AS "April sales"
    FROM Employees
       KEY JOIN SalesOrders
       KEY JOIN SalesOrderItems
       KEY JOIN Products
    WHERE '2001-04-01' <= OrderDate
       AND OrderDate < '2001-05-01'
    GROUP BY  EmployeeID, GivenName, Surname
    ORDER BY EmployeeID;
    EmployeeID GivenName Surname April sales
    129 Philip Chin 2160.00
    195 Marc Dill 2568.00
    299 Rollin Overbey 5760.00
    467 James Klobucher 3228.00
    ... ... ... ...
  4. 销售经理发现由 Philip Chin 拿到的一个大订单没有输入数据库。Philip 希望能立即拿到佣金,因此销售经理输入了这个遗漏的订单。该订单是在 4 月 25 日拿到的。

    在销售经理的窗口中,输入以下命令。将销售订单和各项商品输入到单独的表中,因为一个订单可能包含多项商品。应先为销售订单创建条目,然后再将各项商品添加到该订单中。为保持参照完整性,只有在订单已经存在的情况下,数据库服务器才允许事务将各项商品添加到该订单中。

    INSERT into SalesOrders
    VALUES ( 2653, 174, '2001-04-22', 'r1',
          'Central', 129 );
    INSERT into SalesOrderItems
    VALUES ( 2653, 1, 601, 100, '2001-04-25' );
    COMMIT;
  5. 会计不可能知道销售经理刚添加了一个新订单。如果早些将这个新订单添加到数据库中,计算 Philip Chin 四月份的销售额时将包括这个订单。

    在会计的窗口中再次计算四月份的总销售额。使用相同的命令进行计算,您将发现 Philip Chin 在四月份的销售额变为 $4560.00。

    EmployeeID GivenName Surname April sales
    129 Philip Chin 4560.00
    195 Marc Dill 2568.00
    299 Rollin Overbey 5760.00
    467 James Klobucher 3228.00
    ... ... ... ...

    假设会计现在对四月份拿到的所有订单作出标记,以表示已支付了佣金。虽然销售经理刚输入的订单没有计入 Philip 四月份的总销售额中,但在第二次搜索时可能会找到该订单并将其标记为佣金已付。

  6. 处于隔离级别 3 时,数据库服务器放置防插入锁以确保任何其它事务都不能添加符合搜索或选择条件的行。

    在销售经理的窗口中,执行以下语句,删除那个新订单。

    DELETE
    FROM SalesOrderItems
    WHERE ID = 2653;
    DELETE
    FROM SalesOrders
    WHERE ID = 2653;
    COMMIT;
  7. 在会计的窗口中,执行以下两个语句。

    ROLLBACK;
    SET TEMPORARY OPTION isolation_level = 3;
  8. 在会计的窗口中,执行与以前相同的查询。



    SELECT EmployeeID, GivenName, Surname,
       SUM( SalesOrderItems.Quantity * UnitPrice )
          AS "April sales"
    FROM Employees
       KEY JOIN SalesOrders
       KEY JOIN SalesOrderItems
       KEY JOIN Products
    WHERE '2001-04-01' <= OrderDate
       AND OrderDate < '2001-05-01'
    GROUP BY  EmployeeID, GivenName, Surname;

    由于已将隔离级别设置为 3,因此数据库服务器将自动放置防插入锁,以确保销售经理在会计完成其事务之前不能插入四月份的订单商品项。

  9. 返回到销售经理的窗口。通过执行以下命令尝试输入 Philip Chin 的那份遗漏的订单。

    INSERT INTO SalesOrders
    VALUES ( 2653, 174, '2001-04-22',
             'r1','Central', 129 );

    销售经理的窗口将会停止响应,该操作无法完成。在工具栏上,单击 [停止](或选择 [SQL] » [停止]),中断这次输入。

  10. 虽然销售经理不能将订单输入到四月份,但您可能会认为他们还可以将订单输入到五月份。

    将该命令的日期更改为 5 月 5 日,然后重试。

    INSERT INTO SalesOrders
    VALUES ( 2653, 174, '2001-05-05', 'r1',
          'Central', 129 );

    销售经理的窗口再次停止响应。在工具栏上,单击 [停止](或选择 [SQL] » [停止]),中断这次输入。虽然除防止插入所必需的锁以外数据库服务器没有放置多余的锁,但这些锁也可能会干扰多个事务。

    数据库服务器在表索引中放置锁。例如,如果它在一个索引中放置了幻像锁,那么就不能在该索引的前一位置插入新行。但是,如果没有合适的索引,它必须锁定表中的每一行。

    有些情况下,防插入锁可能会阻塞某些在表中插入行的操作,但允许其它操作。

  11. 在本教程的最后,请撤消所有更改,以避免更改 SQL Anywhere 示例数据库。在销售经理的窗口中执行以下语句。

    ROLLBACK;

    在会计的窗口中执行以下语句。

    ROLLBACK;
  12. 关闭这两个 Interactive SQL 实例。