SinoDB数据库隔离级别

运维指南 运维指南
1

  本文主要对SinoDB数据库隔离级别及其设置进行介绍。

1. ANSI SQL-92事务隔离

ANSI 委员会定义了以下级别的事务隔离（SQL-92）：

  • Read uncommitted
  • Read committed
  • Repeatable read
  • Serializable read

  查询的隔离级别决定了查询与其他并发执行的UPDATE，DELETE和INSERT语句所做的修改的隔离程度。SinoDB支持ANSI委员会定义的这四个事务隔离级别。

2. SinoDB动态服务器隔离级别

2.1 隔离级别对比

ANSI 级别 SET TRANSACTION SET ISOLATION
Read uncommitted READ UNCOMMITTED DIRTY READ
Read committed READ COMMITTED COMMITTED READ
未定义 不可用 COMMITTED READ LAST COMMITTED
未定义 不可用 CURSOR STABILITY
Repeatable read REPEATABLE READ REPEATABLE READ
Serializable SERIALIZABLE REPEATABLE READ

  SinoDB 动态服务器除了支持ANSI定义的隔离级别外还支持其他两个隔离级别：CURSOR STABILITY(特定于游标操作)，COMMITTED READ LAST COMMITTED。

2.2 隔离级别控制

SinoDB 动态服务器允许用户使用以下方法控制其查询的隔离级别：

  • SET TRANSACTION
    — 符合 ANSI 标准
    — 支持访问模式(access modes)
    — 每个事务可设置一次

  • SET ISOLATION
    — 不符合 ANSI 标准
    — 不支持访问模式
    — 可在事务中更改

  SinoDB 动态服务器提供两个SQL语句来控制应用程序中的当前隔离级别。第一个语句 SET TRANSACTION 符合ANSI SQL-92规范。第二个语句SET ISOLATION不符合ANSI标准，也不支持访问模式，但允许指定SinoDB特定的隔离级别：CURSOR STABILITY 与 COMMITTED READ LAST COMMITTED。

  SET TRANSACTION和SET ISOLATION之间的主要区别是SET TRANSACTION语句只在事务持续时间内生效。此外，您只能在事务中执行一个SET TRANSACTION语句。

示例：
SET ISOLATION语句允许您更改单个事务中的有效隔离级别，例如：

BEGIN WORK;
SET ISOLATION TO DIRTY READ;
SELECT * FROM customer;
SET ISOLATION TO REPEATABLE READ;
INSERT INTO cust_info;

一旦使用SET ISOLATION语句设置隔离级别后，将一直保持有效到下一个SET ISOLATION语句或到会话结束。

2.3 访问方法

  • 默认情况下，动态服务器事务始终能够读写

  • 要控制访问模式，请使用以下语句：
    SET TRANSACTION READ WRITE;
    SET TRANSACTION READ ONLY;

  • 只读取事务无法：
    — 更新、插入或删除行
    — 添加、删除、更改或重命名数据库对象
    — 更新数据库统计表
    — 授予或撤销权限

ANSI SQL-92定义了读写和只读事务。

2.4 Read Uncommitted

ANSI：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SinoDB:

SET ISOLATION TO DIRTY READ;

1694595385116
1694595385116849×284 23.3 KB

  当隔离级别是Read Uncommitted或DIRTY READ时，数据库服务器在解析查询时不会放置任何锁或检查现有的锁。在检索期间，您可以查看任何行，即使那些包含未提交的更改。

  DIRTY READ脏读取隔离使您的查询可以检索幻像行。

  幻像行是事务插入的行，但事务被回滚而不是提交。虽然幻像行从未提交到数据库，因此从不真正存在 于数据库中，但是对于使用脏读取隔离的任何进程都是可见的。

  DIRTY READ脏读取隔离是唯一可用于非日志记录数据库的隔离级别。

  在以下情况下，DIRTY READ脏读隔离非常有用：：

  • 表是静态的
  • 100%的准确性不如速度和免于争用那么重要
  • 你不能等待释放锁

2.5 Read committed

ANSI：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SinoDB:

SET ISOLATION TO COMMITTED READ;

1694596487154
1694596487154878×365 16.1 KB

  有日志记录的数据库中的查询默认为ANSI read-committed隔离。Read-committed隔离与SinoDB committed-read隔离是同义的。Read-committed隔离可确保读取的所有行都提交到数据库。要执行已提交的读取，数据库服务器将尝试 在读取之前获取行上的共享锁。它不放置锁，而是检查是否可以 获取锁。如果可以的话，可以保证该行存在并且在读取时不被其他进程更新。请记住不能在排它锁定的行上获取共享锁，在更新行时总是这样的情况。

  当您使用committed read扫描行时，您不会查看任何幻像行或脏数据。您知道当前行已提交（至少当您的进程读取它时已提交）。但是，在进程读取该行后，其他进程可以对其更改。

  在以下情况，committed read隔离非常有用：

  • 查找
  • 查询
  • 生成常规信息的报表

2.6 Cursor Stability

SET ISOLATION TO CURSOR STABILITY;

  使用CURSOR STABILITY，游标读取共享锁时在每一行上都会获取共享锁。这个共享锁一直保持到下一行被检索。如果使用游标检索数据，那么共享锁一直保持到执行下一个FETCH。

  在这个级别上，您不仅可以查看提交的行，还可以确保在查看时该行会继续存在。没有其他进程（UPDATE或DELETE）可以在您查看时更改该行。

  您可以使用CURSOR STABILITY隔离级别的SELECT语句：

  • 查找
  • 查询
  • 产生操作数据的报告

  如果设置了CURSOR STABILITY的隔离级别，并且未使用游标，则CURSOR STABILITY的行为方式与READ COMMITTED（从未实际设置共享锁）相同。

2.7 Repeatable Read 和 Serializable Read

ANSI:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SinoDB:

SET ISOLATION TO REPEATABLE READ;

1694597994164
1694597994164855×395 17.5 KB

  在SinoDB数据库中，Repeatable Read和Serializable Read是一样的。Repeatable Read或Serializable Read隔离级别将共享锁放在数据库服务器检查的所有行上； 所有这些锁都一直保持到提交事务。其他用户可以读取数据，但不能以任何方式修改数据。该行将不仅在您查看时继续存在，而且之后在同一事务中重新读取时也将继续存在。

  SinoDB动态服务器创建MODE ANSI数据库时使用的默认隔离级别是REPEATABLE READ。

  当您必须将所有行读取为一个单位或您需要保证值不变时，Repeatable Read是有用的。例如：

  • 关键，聚合计算（如在账户余额中）
  • 从多个表中的联合查询（如在预定系统中）

   REPEATABLE READ保证了数据集在事务期间的一致性。为此，它不仅必须锁定满足查询筛选条件的行，还必须锁定为解析查询而必须读取的任何行和索引键。如果查询执行顺序扫描而不是索引读取，数据库服务器将通过将所需的页锁或行锁替换为表上的单个共享锁来优化扫描。

2.8 COMMITTED READ LAST COMMITTED

SET ISOLATION TO COMMITTED READ LAST COMMITTED;

1694598104107
1694598104107717×317 28.2 KB

  使用“COMMITTED READ LAST COMMITTED” 隔离级别，可以缓解等待其他用户释放锁的问题，以及在不一致状态（在事务过程中）下检索数据的风险。

  当设置了“COMMITTED READ LAST COMMITTED”隔离级别并且另一个进程对数据持有独占行锁时，返回给用户的数据是上次提交时存在的数据。

  此隔离级别也缓解了死锁问题，因为即使设置了锁，用户也会在上次提交时获取数据，而不必等待其他用户持有的锁被释放。

示例：

1694598532481
16945985324811001×455 40 KB

  user1 正在修改user2 想要的行的事务中。在“COMMITTED READ LAST COMMITTED” 隔离级别下，不是等待锁被释放或检索“脏”数据，而是把 c2 上次提交的值将返回给user2，确保数据有效且未被更改。

2.8.1 配置 LAST COMMITTED

使用参数进行设置:

  • USELASTCOMMITTED：
    — 在ONCONFIG配置文件中进行设置，对所有会话有效，可以使用onmode -wf 或者onmode -wm进行动态设置。
    — 通过环境变量进行设置，会覆盖配置参数设置的值。

使用SQL 语句进行设置：:

SET ISOLATION TO COMMITTED READ LAST COMMITTED;
SET ENVIRONMENT USELASTCOMMITTED ‘ALL|COMMITTEDREAD|DIRTY READ|NONE’;

  SET ISOLATION TO COMMITTED READ LAST COMMITTED ;会覆盖环境变量的设置值。
  SET ENVIRONMENT USELASTCOMMITTED;这会话级语句确定当操作遇到锁并且会话隔离级别设置为COMMITTED READ或者DIRTY READ时执行的操作。

设置值说明如下：

  • COMMITTED READ
      数据库服务器在尝试读取Committed Read 或 Read Committed隔离级别中的行时遇到独占锁时，将读取最近提交的数据版本。

  • DIRTY READ
      数据库服务器在尝试读取Dirty Read 或 Read Committed隔离级别中的行时遇到独占锁时，将读取最近提交的数据版本。

  • ALL
      数据库服务器在尝试读取Committed Read，Dirty Read，Read Committed或Read Uncommitted隔离级别中的行时遇到独占锁时，将读取最近提交的数据版本。

  • NONE
      禁用 USELASTCOMMIT 功能。在此设置下，如果会话在尝试读取Committed Read，Dirty Read，Read Committed或Read Uncommitted隔离级别中的行时遇到独占锁，则在提交或回滚持有独占锁的并发事务之前，将不允许事务读取该行。

  使用 COMMITTED READ LAST COMMITTED隔离级别可以降低当两个或多个会话尝试使用行锁访问表中同一行时锁定冲突的风险，从而提高使用Committed Read, Dirty Read, Read Committed或者Read Uncommitted隔离级别的会话中的并发性。

  任何 SPL 例程都可以使用SQL语句在会话期间指定 COMMITTED READ LAST COMMITTED事务隔离级别。这些语句使得 SQL 操作在读取行遇到独占锁时读取上次提交的版本。

  在跨服务器分布式查询中，如果发出查询的会话使用 COMMITTED READ LAST COMMITTED隔离级别，但有一个或多个参与的数据库不支持此“ LAST COMMITTED”功能，则整个事务符合发起事务的会话的Committed Read或者Dirty Read隔离级别， 而不使用COMMITTED READ LAST COMMITTED隔离级别。

2.8.2 使用LAST COMMITTED注意事项

  • 支持：
    – B-tree索引
    – 函数索引

  • 不支持：
    – R-tree索引
    – 通过DataBlade模块访问表
    – 包含collection数据类型列的表
    – 使用虚拟表接口 （VTI） 创建的表
    – 具有页级锁或独占锁的表
    – 无日志表或数据库