This week, I presented this paper at ICCCN'26. This is joint work with Aleksey Charapko (University of New Hampshire) and my MongoDB colleagues Matt Broadstone, Daniel Gomez Ferro, and Akshat Vig. The paper investigates how to build a metastability tolerant execution control system (ECS) for a database.

Why?

Modern databases are complex networked systems serving mixed workloads: short queries (that want an answer in milliseconds) sitting next to analytics jobs (that want the CPU for multiple seconds). The arrival rate of requests is effectively unbounded, but of course, the server's resources are not. And, unfortunately, elastic scaling does not save you here. Scaling takes minutes, whereas, overload takes seconds. Admission control tries to guard the front door (more on this later), but the component that mediates contention once requests reach the backend is the execution control system (ECS).

Unlike a closed system OS scheduler, which strives for fairness and completeness by giving runtime for every thread, faced with an open environment the ECS can only afford to protect short latency-sensitive queries and shed the excess, pushing the burden of waiting back across the network to the clients. This doesn't mean that long tasks are starved, as they can retry until capacity permits execution.

However, shedding load across a network is risky business. Clients do not see the server slow down, instead they time out and retry aggressively. Moreover, workloads are also unpredictable. A query that looks short may hang on a lock or blow up into a scan. The combination of delayed signals, retries, and misclassification makes the cloud databases a fertile ground for failures.

The specific failure we worry about here is metastability: the system gets pushed into a degraded state, and the degraded state sustains itself even after the original trigger is removed. The mechanisms you build for resilience (the retries and the queues) turn into positive feedback loops after a trigger (overload, cache failure, etc). This is not a rare exotic problem. Since production systems would have already been hardened to handle the obvious failures, what remains is these hard-to-detect emergent failures. The OSDI'22 study, where Aleksey was a coauthor, collected 22 metastable incidents across 11 organizations and found that at least 4 of the 15 major AWS outages in the preceding decade were metastable failures, with durations running from 1.5 to 73 hours. Retries were the sustaining mechanism in more than half of the studied incidents. There is no single reset button in a distributed system, so the ECS must break the feedback loop without things escalating into metastable failures.

What?

The ECS mechanism looks deceptively simple. A ticket is a permit to occupy a thread, and there are fixed ticket pools that cap concurrency. If a task cannot get a ticket, it queues up. There are two queues: high priority for short tasks, low priority for long ones. Since you cannot classify a task's cost a priori (the query optimizer's estimate is merely a suggestion), every task starts in the high-priority queue and gets demoted only when it proves itself long by exceeding the brief execution time assigned to short tasks. The queues are bounded, and when they fill, we shed tasks. Shed happens (pun intended!) either at the entry or at mid-stream at the demotion time.

However, the trouble starts in the execution of the policy, deciding how many tickets each queue gets and where the demotion threshold sits. Production systems are too complex, and metastable failures are too well hidden during normal operation, so these prevent us to tune things by trial and error. So we need to trace how overload propagates through queues and retry logic before deployment.

To address this problem, we (well, Aleksey Charapko) built MESSI (MEtaStability SImulator), a discrete-event simulator written in Go. MESSI models a system as a graph: Logic Nodes hold the decision logic (where this work goes next), and Processors simulate execution (delays for both service time and queuing time). The runtime is scriptable, so you can inject failures, slowdowns, and configuration changes mid-run and watch how the system responds. MESSI proved to be crucial for our exploration of the ECS design space, which is full of interacting variables and hidden feedback loops. Without cheap rapid iteration we would not have isolated the mechanisms that matter for metastability tolerance. (I talked about MESSI earlier last month, when making a case for simulation-driven resilience for agentic data systems.)

We discovered a metastable behavior!

Our first dynamic policy was reasonable-sounding: each queue independently probes its ticket count up and down, keeping changes that improve the ticket-acquisition rate, an easy-to-observe quantity that intuitively tracks throughput.

However, it turns out under overload, the ticket-acquisition metric lies. A ticket bounds wall-clock time, not the CPU time. With one core and two tickets, each task gets 5ms actual runtime in its 10ms window. (You want some concurrency to avoid IO blocking, and to enable CPU to be productive by switching to another task). Now, consider one core and ten tickets: each task gets about 1 ms of CPU and 9 ms of waiting inside its 10 ms window, then each releases the ticket to re-acquire it later.  Although the ticket acquisition rate scaled by 5x here, the actual progress is capped at most at 10 ms of service per 10 ms, no matter how many tickets you issue. That means, the metric was not actually measuring progress, but it was measuring churn.

So under overload, the long queue, which always has a deep wait set under overload, inflated its tickets to pump its metric. The extra threads crowded the shared CPU, which made the short tasks start to queue up, which caused the short queue inflate its own tickets in response. Each policy's corrective action degraded the other's environment, which triggered more corrective action. The escalation stopped only when the long queue hit its static ticket cap. That cap did not fix the feedback loop, but it just put a ceiling on how bad the loop could get. This is how easy it is for a metastability failure to raise out of two individually sensible controllers. Metastability often happens to reasonably designed systems whose parts are reasonable separately.

The fix we applied is to freeze the long queue's tickets at a low static value and probe only the short queue. This leaves a single decision site: with one controller instead of two, there is no race between competing corrections. However, starving the long queue would waste capacity in light load, so we compensated by making the demotion threshold dynamic, again with one rule. If the short queue is empty, raise the demotion threshold by 10% (let longer tasks enjoy high priority while there is room), and if there are any tasks waiting in short queue lower the threshold by 10% (demote more aggressively, protect the fast lane).

Note what changed. The control signal went from a gameable one (acquisition rate, inflatable by churn) to an ungameable one (is anyone actually waiting). And instead of two controllers fighting over a shared resource, there is one controller and one signal.

Admission control can also interfere

A typical deployment puts an admission control service in front of the database, rejecting requests when a latency signal exceeds a limit. Our experiments also found that admission control and the ECS destructively interfered.

Here is the intuition. Admission control cannot tell short tasks from long, so it sheds indiscriminately, dropping exactly the short tasks the ECS exists to protect. Worse, the latency signal it relies on can be corrupted by the ECS: a genuinely short task that accrues queueing delay gets demoted and exits the system labeled long, so the short-task latency metric looks healthy precisely when short tasks are suffering. Therefore the control loop at the admission control and the ECS, reacting at similar speeds to each other's output, can produce a sawtooth oscillation where goodput never reaches what the ECS achieves alone. These two well-meaning defenses, each individually stabilizing, jointly do worse than either.

After identifying the problem, the fixes are easy. Here are what the potential fixes would look like. Stop guessing from the outside, and have the ECS export a distress signal (short tasks hurting, and my own knobs are exhausted), and let admission control reject only while that signal is up. Or make the outer admission control loop deliberately sluggish, an order of magnitude slower than the ECS's own convergence time, so the two controllers cannot destructive interfere in resonance sawtooth manner (i.e., the outer defense should engage only after the inner defense has demonstrably run out of moves). 

So what?

I have two takeaways from the project.

First, performance IS availability. We are used to treating them as separate concerns, one for the performance team's dashboards and one for the postmortems. But metastability erases that boundary, as it can turn a performance problem into an availability problem under certain conditions. These failures live on a spectrum rather than a binary outcome, and traditional formal methods, which excel at safety and correctness, are not built to capture that complexity.

Second, you should simulate before you deploy. Simulations explore many failure modes quickly, and more importantly they surface behaviors you did not think to test for (nobody writes a unit test for "the metric rewards churn"). And simulation is cheap. One person working a few hours per week can model a lot (especially with MESSI). If you don't do the simulations, the alternative is discovering your feedback loops in production.

The composition problem

I want to end with a decompositional framing investigation of the problem. Both failures we described arose from composition. Each component's corrective action degraded its neighbor's operating conditions, and after a shock, neither could stabilize because neither's assumptions held while the other was also trying to "recover". 

Last week, I reviewed a recent line of work that frames metastability exactly this way, as a sin of composition among individually self-stabilizing components. Each component gets a potential function, a measure of its distance from a good state that its corrective actions are supposed to decrease, plus an explicit statement of the environment it assumes while correcting. A metastable fault arises when components are wired so that one's correction raises another's potential, and the fault becomes a failure when the schedule keeps selecting those destabilizing interactions.

Let's reposition our results back through that lens. Ticket-acquisition rate was an invalid potential function: it improved while the true distance from health grew, because churn inflates it. Wait-set occupancy, the signal behind our threshold fix, is a valid one: it is zero exactly when short tasks are fine, and no amount of churn can fake it. And the fix itself is a layered composition: Freezing the long queue's tickets deleted one controller's ability to disturb the shared resource. Gating the threshold-raise on "short wait set empty, and it has stayed empty" means the upper layer acts only after the lower layer has demonstrably converged. That is healing bottom-up.

The same recipe suggests a principled fix for the admission control interference: have the ECS export a distress bit (short tasks hurting and my own knobs are exhausted) instead of letting admission control infer health from a corruptible latency signal, and make the outer loop deliberately slower than the inner loop's convergence time so the two cannot resonate. 

Note that the theoretical framework tells you what properties a good potential function must have, but it cannot provide you one. And it is tricky to find the right metric. We found the right signal by watching the whole system lie to us in simulation. It would not be possible to find it by local reasoning about components. The theory names the sin of composition, but only the simulation catches you when and how that sin manifests.

1. What it is about This investigation began as a performance comparison for different memory allocators. However, during benchmarking, I discovered unexpected effects deserving a more detailed explanation. I hope you find these findings both interesting and useful. Imagine you need to set up a MySQL database server. Every detail is planned: the operating system, … Continued

The post Stored Procedures memory consumption in Percona Server for MySQL appeared first on Percona.

Percona Server for MongoDB 8.3 is available today as a Technical Preview. It is not for production. It is for your lab, your staging cluster, and your benchmark harness – and for sharing with us what works and what does not. Especially if this version is your segue to leverage upcoming full-text and vector search … Continued

The post Percona Server for MongoDB 8.3 Technical Preview Is Now Available appeared first on Percona.

Aaron Bertrand wants you to consider using partitioned tables and the sliding window pattern to help archive old data. That’s a great idea. In fact, I’d like to do that at my own job. I have a truly humungous log table (Terabytes) and its clustered index is already on CreatedDate so it’s a good candidate […]

SQL is quite flexible, making it easy to write a single query that works in two situations: one without a filtering parameter and one with a parameterized filter. For example, I came across a benchmark comparing MongoDB and PostgreSQL handling pagination effectively—by avoiding OFFSET and instead using the last value to fetch the next set of results. The first page typically uses only ORDER BY and LIMIT, while subsequent pages add a WHERE condition to continue from the last value returned.

In the MongoDB version of this benchmark, the filter is handled within the application, which leads to two separate queries for these scenarios.

export async function getOrders(cursor) {
  const match = cursor ? { timestamp: { $gt: new Date(cursor) } } : {};
  const rows = await orders
    .aggregate([
      { $match: match },
      { $sort: { timestamp: 1 } },
      { $limit: PAGE_SIZE },
    ])

The PostgreSQL version uses a single prepared statement. SQL is such a powerful language that it often feels tempting to write it this way:

SELECT *
     FROM orders
     WHERE $1::timestamptz IS NULL OR "timestamp" > $1
     ORDER BY "timestamp" ASC
     LIMIT ${PAGE_SIZE}

If $1 is NULL, the predicate evaluates to true for every row, effectively disabling the filter and returning the first rows in timestamp order without applying any filter. When $1 has a value, it filters the results using that specific value, enabling a more targeted search.

However, expressing both use cases in a single generic predicate may result in an execution plan that is suboptimal for some parameter value.

I gave it a try:

drop table if exists orders;

create table orders (
  order_id text primary key,
  "timestamp" timestamptz not null
);

create index idx_orders_timestamp
  on orders("timestamp");

insert into orders
select
    'ORD-'||g,
    '2025-01-01'::timestamptz + g * interval '1 minute'
from generate_series(1, 5000000) as g;

analyze orders;

prepare getorders(timestamptz, int) as
select * from orders
     where $1::timestamptz is null or "timestamp" > $1
     order by "timestamp" asc limit $2
;

explain (analyze, buffers, settings, costs off)
 execute getorders(date'2026-01-01'::timestamptz, 10);

Here is the execution plan:

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit (actual time=0.028..0.031 rows=10.00 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using idx_orders_timestamp on orders (actual time=0.027..0.029 rows=10.00 loops=1)
         Index Cond: ("timestamp" > '2026-01-01 00:00:00+00'::timestamp with time zone)
         Index Searches: 1
         Buffers: shared hit=4
 Planning Time: 0.117 ms
 Execution Time: 0.042 ms

This looks great because the planner was able to evaluate date'2026-01-01'::timestamptz is null during planning and simplify the predicate. The index scan is efficient, reading just about rows=10.00 entries.

Let's see what happens when PostgreSQL uses a generic plan instead of a custom one:

set plan_cache_mode to force_generic_plan;

explain (analyze, buffers, settings, costs off)
 execute getorders(date'2026-01-01'::timestamptz, 10);

It's still an Index Scan, but without an Index Cond to narrow the scan range - the predicate is an after-scan Filter:

                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Limit (actual time=71.945..71.948 rows=10.00 loops=1)
   Buffers: shared hit=4786
   ->  Index Scan using idx_orders_timestamp on orders (actual time=71.943..71.945 rows=10.00 loops=1)
         Filter: (($1 IS NULL) OR ("timestamp" > $1))
         Rows Removed by Filter: 525600
         Index Searches: 1
         Buffers: shared hit=4786
 Settings: plan_cache_mode = 'force_generic_plan'
 Planning Time: 0.030 ms
 Execution Time: 71.971 ms

Since the generic plan must work for all possible values of $1, PostgreSQL cannot transform the predicate into an Index Cond on "timestamp". As a result, it reads 525600 unnecessary index entries, which are then removed by the filter.

While the default setting plan_cache_mode = 'auto' often works well, it relies on a heuristic: PostgreSQL executes the statement a few times with custom plans, then compares the estimated cost of a generic plan with the average estimated cost of the custom plans. The decision is based on estimates rather than actual execution times, which means it may occasionally select a generic plan even when a custom plan would perform better for a particular workload.

One alternative is to rewrite the predicate so that PostgreSQL can always generate an index condition.

reset plan_cache_mode;

prepare getorders_better(timestamptz, int) as
select * from orders
     where timestamp > coalesce($1, '-infinity'::timestamptz)
     order by "timestamp" asc limit $2
;

With the new query, there is always an Index Cond. The main difference is whether the scan range starts at -infinity or at the supplied parameter value:

postgres=# explain execute getorders_better(null::timestamptz, 10)
;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.78 rows=10 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..174194.43 rows=5000000 width=19)
         Index Cond: ("timestamp" > '-infinity'::timestamp with time zone)
(3 rows)

postgres=# explain execute getorders_better(date'2026-01-01'::timestamptz, 10);
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.78 rows=10 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..155802.94 rows=4471972 width=19)
         Index Cond: ("timestamp" > '2026-01-01 00:00:00+00'::timestamp with time zone)
(3 rows)

postgres=# set plan_cache_mode to force_generic_plan;
SET

postgres=# explain execute getorders_better(null::timestamptz, 10)
;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..5807.41 rows=166667 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..58070.11 rows=1666667 width=19)
         Index Cond: ("timestamp" > COALESCE($1, '-infinity'::timestamp with time zone))
(3 rows)

postgres=# explain execute getorders_better(date'2026-01-01'::timestamptz, 10);
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..5807.41 rows=166667 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..58070.11 rows=1666667 width=19)
         Index Cond: ("timestamp" > COALESCE($1, '-infinity'::timestamp with time zone))
(3 rows)

This revised query is effective because the Index Scan is optimal for pagination with order by "timestamp" asc limit $2, even when a null parameter is used. However, this isn't always the case, as a generic plan must estimate selectivity without knowing the actual parameter values. In some situations, this can lead to poor cardinality estimates and suboptimal plan choices.

I used prepared statements because they are the standard way to execute parameterized queries while avoiding SQL injection. However, you might face the same problem if you construct a custom query using a hardcoded value while keeping the generic predicate. For instance, you could include your parameters in a WITH clause's common table expression.

postgres=# explain
with param as ( select
 date'2026-01-01'::timestamptz as p1
)
select * from orders, param
     where p1::timestamptz is null or "timestamp" > p1
     order by "timestamp" asc limit 10
;

                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.90 rows=10 width=27)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..210363.45 rows=4467609 width=27)
         Filter: ((('2026-01-01'::date)::timestamp with time zone IS NULL) OR ("timestamp" > ('2026-01-01'::date)::timestamp with time zone))
(3 rows)

Since ((('2026-01-01'::date)::timestamp with time zone IS NULL) is provably false, the OR expression could theoretically be simplified to a single predicate. However, PostgreSQL currently does not perform this simplification. The optimizer source code explicitly notes that OR branches proven to be always false could theoretically be removed, but this optimization is not currently implemented:

Currently, when processing OR expressions, we only return true when all of the OR branches are always false. This could perhaps be expanded to remove OR branches that are provably false. This may be a useful thing to do as it could result in the OR being left with a single arg. That's useful as it would allow the OR condition to be replaced with its single argument which may allow use of an index for faster filtering on the remaining condition.

This situation mirrors what happens when a prepared statement is executed using a generic plan. When using generic predicates, such as p1::timestamptz is null or "timestamp" > p1, which contain branches that might be false depending on parameter values, the planner cannot safely eliminate those branches to get a sargable condition. The same workaround applies: if you understand the specific parameter characteristics, write a more targeted query to enable index usage, maybe one per scenario.

For workloads where parameter values can lead to radically different optimal plans, relying on plan_cache_mode = auto may not always produce the desired result. In such cases, forcing custom plans with plan_cache_mode = force_custom_plan can be a simple way to guarantee that the optimizer considers the actual parameter values on every execution.

The final example shows that PostgreSQL's failure to convert OR expressions into indexable predicates extends beyond generic plans. Even when the value is known at planning time via a single-row CTE and not a parameter, PostgreSQL treats the OR expression as a filter instead of simplifying it. This indicates a broader optimizer limitation in OR-clause simplification that you should know and address when writing the SQL query.

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.

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:

• Db2, SQL Server, and Oracle handle it without error.
• PostgreSQL raises ERROR: duplicate key value violates unique constraint "franck_val_key", DETAIL: Key (val)=(1) already exists. 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 collection: test.franck index: val_1 dup key: { val: 1 }

db.franck.createIndex({ val: 1 }, { unique: true });
db.franck.insertMany([ { val: -1 }, { val: 1 } ]);
db.franck.updateMany({},[ { $set: { val: {$multiply:[ "$val",-1 ]} } } ]);
MongoServerError: Plan executor error during update :: caused by :: E11000 duplicate key error collection: test.franck index: val_1 dup key: { val: 1 }

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? 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

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.

Investigation with 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.

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;
/
show errors

Here are my table's rows in the order they are scanned:

SQL> column "DUMP(VAL,16)" format a18
SQL> column "DUMP(ROWID,16)" format a39
SQL> select val, dump(val,16), dump(rowid,16)
     from FRANCK 
;

       VAL DUMP(VAL,16)       DUMP(ROWID,16)
---------- ------------------ ---------------------------------------
         1 Typ=2 Len=2: c1,2  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,0
         2 Typ=2 Len=2: c1,3  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,1
         3 Typ=2 Len=2: c1,4  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,2
         4 Typ=2 Len=2: c1,5  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,3
         5 Typ=2 Len=2: c1,6  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,4

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.

I locate the index block and my session's trace file:

alter session set tracefile_identifier = 'franck';

column fileno    new_value fileno
column block     new_value block
column tracefile new_value tracefile

select header_file as fileno ,
       header_block+1 as block
   from dba_segments
    where segment_type='INDEX'
      and segment_name='FRANCK'
      and owner=sys_context('USERENV', 'CURRENT_SCHEMA')
;

select value as tracefile
 from v$diag_info
 where name = 'Default Trace File'
;

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).

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.

To help with the hexadecimal values, I've put them in a table to show the table rows and their index entries, with values in hexadecimal and decimal:

Running the update

I run the update in another session, and thanks to the trigger, it waits 5 seconds between each updated row:

update FRANCK set val = val + 1
;

The update starts the scan and processes the first table row, where the value 1 (c1 02) is incremented to 2 (c1 03).

Oracle first locates the index entry for the old value, acquires an exclusive lock on the row (the lock: 2 references ITL slot 2, whose transaction information identifies the modifying transaction), and marks it as deleted (-D- flag):

row#0[8021] flag: ---D---, lock: 2, 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

At this intermediate point, the old entry has been deleted, and no entry references the row containing the new value:

The next dump is interesting. Oracle does not add another entry for value 2 (c1 03). Instead, it updates the existing entry #1, which already has the right key c1 03 but for a different row. The old ROWID of entry #1 is replaced:

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 00
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

There are now two index entries for rowid 69 00 00, one deleted and one current:

Notice that there is no longer any entry for rowid 69 00 01 because Oracle has changed the ROWID stored in the existing key for value 2 (c1 03) entry from 69 00 01 to 69 00 00. This is why there's no duplicate key situation: all keys in the index are unique.

If the update stops after modifying only the first row, it can create duplicate key issues because the first two rows share the same value. It can also cause logical corruption since the second row lacks an index entry. One possible approach is for Oracle to track these uniqueness conflicts as violation counts during statement processing and confirm that none persist at the end. This strategy would enable Oracle to proceed, expecting that further updates will restore consistency.

The update then continues with the next table row. Rowid 69 00 01 changes from 2 to 3.

I guess that Oracle attempts to locate the entry with key value 2 (c1 03) pointing to 69 00 01 for deletion. However, it finds the key but associated with a different ROWID. Since there's nothing to delete, it leaves it as-is but decreases the violation counter, which then returns to zero, because the current update removes the value that was a duplicate.

As we have seen before, Oracle updates the index entry for the new value c1 04 = 3 to point to rowid 69 00 01:

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 01
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

Currently, there is an index entry for 69 00 01 with the value 3 (c1 04), but no entry exists for row 69 00 02, so continuing with my guess, the violation counter is incremented.

With this pattern, the ROWID associated with each key shifts one row earlier. The same pattern applies to keys 4 and 5. The violation counter may be decremented and then incremented again, remaining at 1.

After processing the row containing the value 4 (c1 05), the key with value 5 (c1 06) points to rowid 69 00 03. The violation counter remains at 1.

Once the row with value 5 (c1 06) is processed, Oracle encounters a situation where no existing key is reused. According to my guess, the violation counter decreases because the previous value no longer matches the current row. The new key value is 6 (c1 07), which did not previously exist. Only then is a new index entry created:

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 06

row#5[7966] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 07

The final state is:

All rows and index entries are consistent, and the violation counter is back to zero. The index structure I examined never had duplicate keys in the block dumps. Oracle maintains key uniqueness by reusing entries and updating their stored ROWIDs. The method Oracle uses to monitor unresolved uniqueness conflicts until the statement finishes isn't clear from these dumps, but a violation counter appears to be a possible implementation.

UNIQUE vs non-UNIQUE index

Without grasping these internals, one might assume a UNIQUE index isn't necessary for a UNIQUE constraint. However, now we see the performance benefit of this update pattern: it modifies existing index entries rather than deleting the old value and inserting the new one. I tested this with one million rows:

set linesize 200 pagesize 1000
drop table FRANCK purge;
create table FRANCK as select rownum as VAL from xmltable('1 to 1000000');
create unique index FRANCK on FRANCK ( VAL );
update FRANCK set val = val + 1 ;
analyze index FRANCK validate structure;
select height, br_rows, lf_rows, del_lf_rows, br_blks, lf_blks
 from index_stats
;

The index statistics display branch and leaf blocks along with entries. Following the update statement that affected all rows, the unique index only added a single deleted leaf entry (DEL_LF_ROWS):

    HEIGHT    BR_ROWS    LF_ROWS DEL_LF_ROWS    BR_BLKS    LF_BLKS
---------- ---------- ---------- ----------- ---------- ----------
         3       2220    1000001           1          5       2221

I tested the same process using a non-unique index:

set linesize 200 pagesize 1000
drop table FRANCK purge;
create table FRANCK as select rownum as VAL from xmltable('1 to 1000000');
create index FRANCK on FRANCK ( VAL );
update FRANCK set val 
                                        by Franck Pachot