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 的用法 » 创建数据库 » 使用事务和隔离级别 » 隔离级别教程

 

教程:脏读

以下教程举例说明并发执行多个事务时可能会出现的一种不一致情况。一家小商品销售公司的两位雇员同时访问公司的数据库。第一个人是公司的销售经理。第二个人是会计。

销售经理想要将公司销售的 T 恤衫的价格提高 $0.95,但在 SQL 语言的语法方面有点儿小问题。同时,会计正要计算当前存货的零售额,以便将它包含到他自愿在下次召开管理会议时提交的报告中。但销售经理却不知道这个情况。

提示

对数据库进行以下变更之前,为谨慎起见,建议使用 SELECT 代替 UPDATE 对更改进行测试。

注意

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

在本示例中,假设两个雇员的角色,且他们并发使用 SQL Anywhere 示例数据库。

  1. 启动 Interactive SQL。

  2. 在 [连接] 窗口中,以销售经理的身份连接到 SQL Anywhere 示例数据库:

    • 在 [ODBC 数据源名称] 字段中,选择 [SQL Anywhere 11 Demo]。

    • 单击 [高级] 选项卡,在 [ConnectionName] 字段中键入 Sales Manager

    • 单击 [确定]。

  3. 再启动一个 Interactive SQL 的实例。

  4. 在 [连接] 窗口中,以会计的身份连接到 SQL Anywhere 示例数据库:

    • 在 [ODBC 数据源名称] 字段中,选择 [SQL Anywhere 11 Demo]。

    • 单击 [高级] 选项卡,在 [ConnectionName] 字段中键入 Accountant

    • 单击 [确定]。

  5. 作为销售经理,将所有 T 恤衫的价格提高 $0.95:

    • 在 [Sales Manager] 窗口中,执行以下命令:

      UPDATE Products
         SET UnitPrice = UnitPrice + 95
         WHERE Name = 'Tee Shirt';
      SELECT ID, Name, UnitPrice
         FROM Products;

    结果为:

    ID name UnitPrice
    300 Tee Shirt 104.00
    301 Tee Shirt 109.00
    302 Tee Shirt 109.00
    400 Baseball Cap 9.00
    ... ... ...

    您很快就发现应输入 0.95 而不是 95,但在还未能改正错误之前,会计在另一个办公室访问了数据库。

  6. 公司的会计担心存货会占用太多资金。作为会计,执行以下命令计算所有库存商品的总零售额:

    SELECT SUM( Quantity * UnitPrice )
     AS Inventory
       FROM Products;

    结果为:

    Inventory
    21453.00

    可惜的是,计算出的结果并不准确。由于销售经理不小心将 T 恤衫的价格提高了 $95,而结果是用错误的价格计算得到的。这个错误演示了一种被称作脏读的典型不一致类型。作为会计,您访问了销售经理已经输入但尚未提交的数据。

    您可以清除脏读和隔离级别和一致性中介绍的其它不一致。

  7. 作为销售经理,回退第一次所做的更改,然后输入正确的 UPDATE 命令,即可改正错误。检查新值是否正确。

    ROLLBACK;
    UPDATE Products
    SET UnitPrice = UnitPrice + 0.95
    WHERE NAME = 'Tee Shirt';
    COMMIT;
    ID name UnitPrice
    300 Tee Shirt 9.95
    301 Tee Shirt 14.95
    302 Tee Shirt 14.95
    400 Baseball Cap 9.00
    ... ... ...
  8. 会计并不知道他计算出的金额不正确。您可以在会计的窗口中再次执行 SELECT 语句查看正确的值。

    SELECT SUM( Quantity * UnitPrice )
     AS Inventory
       FROM Products;
    Inventory
    6687.15
  9. 在销售经理的窗口中完成事务。销售经理应输入 COMMIT 语句使更改成为永久更改,但您应该执行 ROLLBACK,以避免更改 SQL Anywhere 示例数据库的本地副本。

    ROLLBACK;

由于数据库服务器并发处理销售经理的工作和会计的工作,因此会计在不知情的情况下从数据库中得到了错误的信息。

使用快照隔离避免脏读

使用快照隔离时,其它数据库连接只能看到响应其查询的已提交数据。将隔离级别设置为语句快照或快照,会防止发生脏读的可能性。会计可以使用快照隔离以确保执行查询时只能看到已提交的数据。

  1. 启动 Interactive SQL。

  2. 在 [连接] 窗口中,以销售经理的身份连接到 SQL Anywhere 示例数据库:

    • 在 [ODBC 数据源名称] 字段中,选择 [SQL Anywhere 11 Demo]。

    • 单击 [高级] 选项卡,在 [ConnectionName] 字段中键入 Sales Manager

    • 单击 [确定] 进行连接。

  3. 执行以下语句为数据库启用快照隔离:

    SET OPTION PUBLIC.allow_snapshot_isolation = 'ON';
  4. 再启动一个 Interactive SQL 的实例。

  5. 在 [连接] 窗口中,以会计的身份连接到 SQL Anywhere 示例数据库:

    • 在 [ODBC 数据源名称] 字段中,选择 [SQL Anywhere 11 Demo]。

    • 单击 [高级] 选项卡,在 [ConnectionName] 字段中键入 Accountant

    • 单击 [确定]。

  6. 作为销售经理,将所有 T 恤衫的价格提高 $0.95:

    • 在标题为 Sales Manager 的窗口中,执行以下命令:

      UPDATE Products
      SET UnitPrice = UnitPrice + 0.95
      WHERE Name = 'Tee Shirt';
    • 使用销售经理制定的新 T 恤衫价格,计算所有库存商品的总零售额:

      SELECT SUM( Quantity * UnitPrice )
       AS Inventory
         FROM Products;

      结果为:

      Inventory
      6687.15
  7. 作为会计,执行以下命令计算所有库存商品的总零售额:由于此事务使用快照隔离级别,因此仅计算已提交到数据库的数据的结果。

    SET OPTION isolation_level = 'Snapshot';
    SELECT SUM( Quantity * UnitPrice )
     AS Inventory
       FROM Products;

    结果为:

    Inventory
    6538.00
  8. 作为销售经理,执行以下语句将您的更改提交到数据库:

    COMMIT;
  9. 作为会计,执行以下语句查看当前存货的更新后的零售额:

    COMMIT;
    SELECT SUM( Quantity * UnitPrice )
     AS Inventory
       FROM Products;

    结果为:

    Inventory
    6687.15

    由于用于会计的事务的快照以第一次读取操作开始,因此您必须执行 COMMIT 以结束事务,并允许会计在快照事务开始后查看对数据所做的更改。请参见了解快照事务

  10. 作为销售经理,执行以下语句撤销对 T 恤衫价格的更改并将 SQL Anywhere 示例数据库恢复到其原始状态:

    UPDATE Products
    SET UnitPrice = UnitPrice - 0.95
    WHERE Name = 'Tee Shirt';
    COMMIT;