Following ROWIDs Through an Oracle Unique Index Update

查看原文 / View Original 编辑 / Edit

Following ROWIDs Through an Oracle Unique Index Update

I’ve always been amazed by how Oracle Database handles updates to a unique column—performing set-based operations that don’t violate the unique constraint, yet when executed row by row, it temporarily permits duplicates. 我一直对 Oracle 数据库处理唯一列更新的方式感到惊叹——它执行的是不会违反唯一约束的集合操作，但当按行执行时，它却能暂时允许重复值的存在。

SQL> create table franck ( val int unique );
Table created.
SQL> insert into franck values (-1) , (1) ;
2 rows created.
SQL> select val from franck;
VAL
----------
-1
1
SQL> update franck set val=-val;
2 rows updated.
SQL> select val from franck;
VAL
----------
1
-1

From a SQL perspective, this is expected behavior, but not all databases support it without raising an error: 从 SQL 的角度来看，这是预期的行为，但并非所有数据库都能在不报错的情况下支持它：

  • Db2, SQL Server, and Oracle handle it without error.

  • PostgreSQL raises ERROR: duplicate key value violates unique constraint "franck_val_key". This works with a deferred constraint.

  • MySQL or MariaDB raise Duplicate entry '1' for key 'franck.val'.

  • SQLite raises { "code": "SQLITE_CONSTRAINT_UNIQUE" }.

  • MongoDB raises E11000 duplicate key error.

  • Db2、SQL Server 和 Oracle 可以无错处理。

  • PostgreSQL 会抛出 ERROR: duplicate key value violates unique constraint "franck_val_key"。如果使用延迟约束（deferred constraint）则可以成功。

  • MySQL 或 MariaDB 会抛出 Duplicate entry '1' for key 'franck.val'。

  • SQLite 会抛出 { "code": "SQLITE_CONSTRAINT_UNIQUE" }。

  • MongoDB 会抛出 E11000 duplicate key error。

This is surprising because Oracle unique indexes store the indexed columns as the B-tree key and the ROWID as the associated data. Non-unique indexes add the ROWID to the physical key and are required for a deferrable unique constraint to allow temporary duplication before the end of the transaction. So how do non-deferrable unique indexes allow duplication during a single update statement? 这令人惊讶，因为 Oracle 的唯一索引将索引列存储为 B-tree 键，并将 ROWID 作为关联数据存储。非唯一索引则将 ROWID 添加到物理键中，这是可延迟唯一约束在事务结束前允许临时重复所必需的。那么，不可延迟的唯一索引是如何在单条更新语句中允许重复的呢？

In this simple example, I would expect: 在这个简单的例子中，我预期的过程是：

  1. The initial index entries are: (-1): row #1 and (1): row #2.

  2. Updating the first row deletes the first entry (-1): row #1 and adds one with (1): row #1.

  3. Updating the second row deletes the corresponding entry (1): row #2 and adds one with (-1): row #2.

  4. The final non-deleted index entries are: (-1): row #2 and (1): row #1.

  5. 初始索引条目为：(-1): row #1 和 (1): row #2。

  6. 更新第一行会删除第一个条目 (-1): row #1 并添加一个 (1): row #1。

  7. 更新第二行会删除对应的条目 (1): row #2 并添加一个 (-1): row #2。

  8. 最终未被删除的索引条目为：(-1): row #2 和 (1): row #1。

Even if the final state is valid, this execution would raise a duplicate key error at step 2 because two entries have the same value: (1): row #1 and (1): row #2. So Oracle does something smarter. Let’s look at the internals with another example: five rows with values from 1 to 5, and an update that increments them by one should create a temporary violation until the last row is updated. 即使最终状态是有效的，这种执行方式也会在第 2 步引发重复键错误，因为有两个条目具有相同的值：(1): row #1 和 (1): row #2。因此，Oracle 采取了更聪明的做法。让我们通过另一个例子来看看其内部机制：五行数据，值从 1 到 5，执行一次将它们加 1 的更新操作，直到最后一行更新前，应该会产生临时冲突。

Investigation with block dumps

通过块转储（Block Dumps）进行调查

My goal is to observe how a unique B-tree index evolves as Oracle executes a multi-row update that temporarily creates duplicate values. The table contains five rows with values from 1 to 5, and a row-level trigger pauses for five seconds before each row update. This gives me enough time to dump the index leaf block and capture intermediate states in a second session. 我的目标是观察当 Oracle 执行一个会暂时产生重复值的多行更新时，唯一 B-tree 索引是如何演变的。表中包含 1 到 5 的五行数据，并且有一个行级触发器在每次行更新前暂停 5 秒。这给了我足够的时间来转储索引叶块，并在第二个会话中捕获中间状态。

I create the table: 我创建了如下表：

drop table FRANCK purge;
create table FRANCK as select rownum as VAL from xmltable('1 to 5');
create unique index FRANCK on FRANCK ( VAL );
create or replace trigger FRANCK_SLEEP before update on FRANCK for each row begin dbms_session.sleep(5); end; /

I’ve displayed the value in hexadecimal, as well as the ROWID because the index entries are key -> ROWID and I’ll inspect them with a hexadecimal dump. 我将值以十六进制显示，同时也显示了 ROWID，因为索引条目是“键 -> ROWID”结构，我将通过十六进制转储来检查它们。

Each time I want to dump the block and read the entries, I run the following in the session where the variables were defined: 每次我想转储块并读取条目时，我都会在定义了变量的会话中运行以下命令：

alter system checkpoint;
alter system dump datafile &fileno block █
host tail -20 &tracefile | egrep "^row|^col"

I’ll dump the block multiple times while another session runs update FRANCK set VAL = VAL + 1;, showing: the index entry (row# in the dump) with the 6-byte ROWID (data in the dump) and the key (col 0 in the dump). 当另一个会话运行 update FRANCK set VAL = VAL + 1; 时，我会多次转储该块，显示：索引条目（转储中的 row#）、6 字节的 ROWID（转储中的 data）以及键（转储中的 col 0）。

Initial state

初始状态

The index leaf block before the update contains one entry per table row: 更新前的索引叶块包含每个表行的一个条目：

row#0[8021] flag: -------, lock: 0, len=11 data:(6): 00 40 87 69 00 00 col 0; len 2; (2): c1 02
row#1[8010] flag: -------, lock: 0, len=11 data:(6): 00 40 87 69 00 01 col 0; len 2; (2): c1 03
row#2[7999] flag: -------, lock: 0, len=11 data:(6): 00 40 87 69 00 02 col 0; len 2; (2): c1 04
row#3[7988] flag: -------, lock: 0, len=11 data:(6): 00 40 87 69 00 03 col 0; len 2; (2): c1 05
row#4[7977] flag: -------, lock: 0, len=11 data:(6): 00 40 87 69 00 04 col 0; len 2; (2): c1 06

The six-byte data:(6) field is the ROWID stored in the unique index entry. The indexed value is stored in col 0 as it is a single-column index. 6 字节的 data:(6) 字段是存储在唯一索引条目中的 ROWID。由于这是单列索引，索引值存储在 col 0 中。

← 返回新闻列表 / Back to news list