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

SAP Sybase SQL Anywhere 16.0 (中文) » SQL Anywhere 服务器 - SQL 用法 » 事务和隔离级别 » 隔离级别教程

 

教程:了解幻像锁

在本教程中,会计和销售经理都有涉及 SalesOrder 和 SalesOrderItems 表的任务。会计需要核实付给销售人员的佣金支票的金额,而销售经理发现有些订单缺失,想要添加这些订单。

前提条件

必须拥有 SELECT ANY TABLE、INSERT ANY TABLE 和 DELETE ANY TABLE 系统特权。

此教程假定您已以销售经理和会计的身份连接到示例数据库。请参见教程:为隔离级别教程设置情形

注释

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

上下文和注释

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

 任务
  1. 在 [Sales Manager] 和 [Accountant] 窗口中分别执行以下语句,将隔离级别设置为 2:

    SET TEMPORARY OPTION isolation_level = 2;
  2. 每个月都会向销售代表支付佣金,所付佣金是他们在该月的销售额乘以一定的百分比。会计正在准备 2001 年 4 月的佣金支票。他的第一项任务是计算各个销售代表在该月的总销售额。价格、销售订单信息和雇员数据存储在单独的表中。使用外键关系连接这些表,以将所需信息合并到一起。

    作为会计,执行以下语句:



    SELECT EmployeeID, GivenName, Surname,
       SUM( SalesOrderItems.Quantity * UnitPrice )
          AS "April sales"
    FROM GROUPO.Employees
       KEY JOIN GROUPO.SalesOrders
       KEY JOIN GROUPO.SalesOrderItems
       KEY JOIN GROUPO.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
    ... ... ... ...
  3. 销售经理发现由 Philip Chin 拿到的一个大订单没有输入数据库。Philip 希望能立即拿到佣金,因此销售经理输入了这个遗漏的订单。该订单是在 4 月 25 日拿到的。

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

    INSERT into GROUPO.SalesOrders
    VALUES ( 2653, 174, '2001-04-22', 'r1',
          'Central', 129 );
    INSERT into GROUPO.SalesOrderItems
    VALUES ( 2653, 1, 601, 100, '2001-04-25' );
    COMMIT;
  4. 会计不可能知道销售经理刚添加了一个新订单。如果早些将这个新订单添加到数据库中,计算 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 四月份的总销售额中,但在第二次搜索时可能会找到该订单并将其标记为佣金已付。

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

    销售经理执行以下语句,删除新订单:

    DELETE
    FROM GROUPO.SalesOrderItems
    WHERE ID = 2653;
    DELETE
    FROM GROUPO.SalesOrders
    WHERE ID = 2653;
    COMMIT;
  6. 会计执行以下语句:

    ROLLBACK;
    SET TEMPORARY OPTION isolation_level = 3;
  7. 执行以下查询:



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

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

  8. 销售经理执行以下语句,尝试输入 Philip Chin 的那份遗漏的订单:

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

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

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

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

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

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

    数据库服务器在表索引中放置锁。例如,如果它在一个索引中放置了幻像锁,那么就不能在该索引的前一位置插入新行。但是,如果没有合适的索引,它必须锁定表中的每一行。有些情况下,防插入锁可能会阻塞某些在表中插入行的操作,但允许其它操作。

  10. 为避免更改 SQL Anywhere 示例数据库,应回退对 SalesOrders 表所做的更改。在 [Sales Manager] 和 [Accountant] 窗口中,执行以下语句:

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

结果

您已完成幻像锁工作原理的相关教程。

 另请参见