delete_transient() cannot remove a timeout row whose value row is missing, and delete_expired_transients() never sees it

[robbsie]

When _transient_timeout_<name> exists in the options table without its matching
_transient_<name> row, no API in core can remove it, and the row can be created
by core itself.

Tested against WordPress 7.0.3, PHP 8.3, MariaDB 10.11, no persistent object cache.

How the row is created

set_transient() writes the timeout row before the value row, in two separate
statements with no transaction (wp-includes/option.php:1550 and 1552):

if ( $expiration ) {
    $autoload = false;
    add_option( $transient_timeout, time() + $expiration, '', false );
}

$result = add_option( $transient_option, $value, '', $autoload );

If the request dies between the two writes — fatal error, execution timeout,
memory exhaustion, dropped connection — the timeout row is already committed and
the value row never arrives.

Reproduced without touching the database directly, by triggering exit from an
added_option hook fired for the timeout row:

set_transient( 'crash', 'value', 3600 );

// request aborts here, directly after the timeout row

resulting state: 1 row
    _transient_timeout_crash    1786455787

delete_transient( 'crash' ) => false
rows after: 1

Truncation at varchar(191) is a second path to the same state and is already
tracked in #15058 and #58903.

Why delete_transient() cannot remove it

wp-includes/option.php:1396-1400

$result = delete_option( $option );

if ( $result ) {
    delete_option( $option_timeout );
}

$option is the value row. delete_option() returns false at option.php:1215
because the row does not exist, so line 1399 is never reached and the timeout
row stays. delete_transient() returns false and will do so on every subsequent
call for the same name.

delete_site_transient() has the identical construction at option.php:2529.

Measured, single site:

set tr_a, tr_b, tr_c                          6 rows
DELETE _transient_tr_b (value row only)       5 rows
delete_transient() on all three               1 row

delete_transient( 'tr_a' ) => true
delete_transient( 'tr_b' ) => false
delete_transient( 'tr_c' ) => true

remaining: _transient_timeout_tr_b

Identical result for site transients, and on multisite in wp_sitemeta.

Why the daily cleanup does not remove it either

delete_expired_transients() joins the options table against itself
(option.php:1645), where the timestamp comes from time() and is inserted by
$wpdb->prepare():

DELETE a, b FROM wp_options a, wp_options b
WHERE a.option_name LIKE '_transient_%'
  AND a.option_name NOT LIKE '_transient_timeout_%'
  AND b.option_name = CONCAT( '_transient_timeout_', SUBSTRING( a.option_name, 12 ) )
  AND b.option_value < 1786455787

An orphaned timeout row has no a side, so it never appears in the result set.
This is independent of the timestamp. Measured with four cases side by side:

pair, expired      removed   (the function does work)
pair, valid        kept      (correct)
orphan, expired    stays
orphan, future     stays

Same result via wp cron event run delete_expired_transients.

The one code path that does clean it up

get_transient() removes the row at option.php:1465-1469, where both
delete_option() calls run unconditionally:

$timeout = get_option( $transient_timeout );

if ( false !== $timeout && $timeout < time() ) {
    delete_option( $transient_option );
    delete_option( $transient_timeout );
    $value = false;
}

So the row disappears if two conditions meet: the timeout has expired, and
something calls get_transient() with exactly that name. It becomes permanent
when nothing reads that name any more — which is the normal case once the plugin
that created the transient has been uninstalled.

This matches dd32's explanation in #12782 comment 1 from 2010: timeout entries
are only removed when a transient expires and is read.

Scope

Only affects installations without a persistent object cache. With a drop-in
active, transients never reach wp_options. Rows already present from before the
drop-in was installed are a separate problem: delete_transient() takes the cache
branch at option.php:1391 and no longer touches the options table at all, and
delete_expired_transients() returns immediately at option.php:1639 unless
$force_db is set, which the daily cron event never does.

History

The construction dates back to [13911] (April 2010), which introduced deletion
of the timeout row in the first place — in wp-includes/functions.php at the time —
including the if ( $result ) guard:

-   $result = delete_option( $option );
+   $option_timeout = '_transient_timeout_' . $transient;
+   $option = '_transient_' . $transient;
+   $result = delete_option( $option );
+   if ( $result )
+       delete_option( $option_timeout );

The guard has never been revisited. Diffs of option.php against 4.9, 6.0, 6.6
and 6.8 show the relevant code unchanged.

Relation to existing tickets

#12782 — fixed in 3.0, introduced this code. Describes the opposite starting
point: the value row was deleted successfully and the timeout row was not touched
at all.

#58904 — falsy transient names producing literal _transient_ rows. A different
cause; the self-join is not discussed there.

#33561, #44977, #52345 — the mirrored case: value row without timeout row. That
state is an expiry problem, and it is removable through the API —
delete_transient() returns true and deletes the row. This ticket is about the
other direction, which is harmless for behaviour but unremovable.

Possible directions

Dropping the if ( $result ) guard would change the documented return value of
delete_transient(), which would then report success without having deleted a
complete transient. Alternatives worth discussing: deleting the timeout row
unconditionally while keeping the return value tied to the value row, or adding
a LEFT JOIN pass to delete_expired_transients() for rows without a partner.

Happy to supply a patch and unit tests once there is a preferred direction.

[robbsie]

Some additional data for this ticket.

Verified against [13911] (1 April 2010, wp-includes/functions.php at the
time): that changeset introduced deletion of the timeout row in the first
place, and the if ( $result ) guard came with it — lines 686 and 687 in
r13911, and 3590/3591 for the site transient branch. It has not been
revisited since. Diffs of option.php against 4.9, 6.0, 6.6 and 6.8 show
the relevant code unchanged.

dd32's explanation in #12782 comment 1, on the same day, already describes
what is now the only remaining cleanup path: timeout entries are removed
when a transient expires and is read, not when it is deleted explicitly.

On row size: measured across the _transient_timeout_ rows of a live
installation, 61 bytes minimum, 63 average, 101 maximum (option_name +
option_value). option_value is always a 10-character Unix timestamp, and
option_name is capped by the varchar(191) column, so 201 bytes is the
structural upper bound per row. Individually negligible; the problem is
that the count only ever grows.

Longer write-up with the measurements and the ticket history:
​https://turbopress.de/verwaiste-transients-wordpress/ (German)

Still happy to supply a patch and unit tests once there is a preferred
direction on the guard.