Repair WooCommerce core: database bloat and maintenance
Expired transients bloat wp_options
Every checkout writes a small, short lived value into the options table so WooCommerce and its Stripe gateway can keep track of things in progress, like a lock that stops one payment from being processed twice. Most of the time that value expires and is quietly forgotten. It is never deleted. On a store that has been running for a year, that forgetting adds up to tens of thousands of dead rows sitting in wp_options, most of them set to load on every single page. Here is why WordPress never cleans them up on its own, and a small script that finds the ones that are safe to clear.
A WordPress transient is only deleted when something asks WordPress for that same key after it has expired. One off keys, like a Stripe checkout lock tied to a single PaymentIntent, are never asked for again, so they never get swept and just sit in wp_options forever. Run a small Python or Node.js job on a schedule that checks each order's saved PaymentIntent against Stripe, and once Stripe confirms the payment is fully settled, clears the matching lock so the row is safe to purge. Full code, tests, and a dry run guard are below.
The problem in plain words
A transient is WordPress's way of caching something for a little while. It is stored as two rows in wp_options, one holding the value and one holding when it should expire. The idea is that the next time your code asks for that transient, WordPress checks the expiry, and if it has passed, deletes both rows and returns nothing, as if the value had never been cached.
That cleanup only happens on request. If nothing ever asks for that specific key again, WordPress has no reason to look at it, so the two rows just stay in the table, expired but never removed. WooCommerce and its Stripe gateway create a lot of transients that are meant to be checked exactly once, like a lock keyed to one PaymentIntent id. Once that payment finishes, nothing will ever ask for that key again. It is now dead weight, and it usually carries autoload=yes, which means WordPress loads it into memory on every single page view, whether anyone needs it or not.
Why it happens
The WordPress transients API is documented as self cleaning, but that cleanup is lazy by design, it only runs when the same key is requested. A few reasons this quietly turns into real bloat on a WooCommerce store:
- The WooCommerce Stripe gateway writes a lock transient per PaymentIntent to stop a webhook and a page redirect from processing the same payment twice. Once the order settles, that exact key is never looked up again.
- Many of these transients are set with a long timeout, sometimes hours, so even the lazy cleanup path has no chance to trigger before the shopper has long since checked out.
- By default a transient with no explicit autoload setting is stored with
autoload=yes, so WordPress reads it into memory on every request until it is deleted, whether it is expired or not. - A store that never runs WP-CLI's
transientcommands or a maintenance plugin has no process that ever looks at these rows again, so they accumulate for as long as the store has been live.
This has been reported for years across large WooCommerce and WordPress installs, where wp_options grows to hundreds of megabytes and every page load pays the cost of loading rows nobody will ever read again. See the citations at the end for the background threads.
You cannot fix this by waiting. An expired transient only gets deleted the next time the exact same key is requested, and a one off lock keyed to a single PaymentIntent will never be requested again. The only way to remove it is a job that actively looks for locks whose purpose has already been served and clears them.
The fix, as a flow
We do not touch checkout and we do not run raw SQL, since this job only has WooCommerce REST API and Stripe API access. Instead we walk recent orders, read the saved PaymentIntent id off each one, and ask Stripe whether that intent is fully settled. If it is, and the order still carries the store's own checkout lock flag, that lock has no reason left to exist, so we clear it and log the exact wp_options key so the site's own cleanup job or WP-CLI can sweep it out in bulk.
Build it step by step
Get access to both systems
You need a Stripe secret key and a WooCommerce REST API key pair (a consumer key and a consumer secret) with read and write access to orders. Create the WooCommerce key under WooCommerce, Settings, Advanced, REST API. Keep every value in environment variables, never in the file.
pip install stripe requests
export STRIPE_SECRET_KEY="sk_live_..."
export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export LOOKBACK_DAYS="30"
export DRY_RUN="true" # start safe, change to false to writenpm install stripe
export STRIPE_SECRET_KEY="sk_live_..."
export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export LOOKBACK_DAYS="30"
export DRY_RUN="true" // start safe, change to false to writeRead the saved PaymentIntent off each order
WooCommerce saves the Stripe PaymentIntent id as order meta, usually under _stripe_intent_id. Some older orders only have it as the transaction_id, so check both and only trust a value that looks like a PaymentIntent id, one starting with pi_.
def intent_id_of(order):
for meta in order.get("meta_data") or []:
if meta.get("key") == "_stripe_intent_id" and meta.get("value"):
return meta["value"]
tid = order.get("transaction_id")
return tid if tid and tid.startswith("pi_") else Noneexport function intentIdOf(order) {
for (const meta of order.meta_data || []) {
if (meta.key === "_stripe_intent_id" && meta.value) return meta.value;
}
const tid = order.transaction_id;
return tid && tid.startsWith("pi_") ? tid : null;
}Ask Stripe if the intent is fully settled
Retrieve the PaymentIntent from Stripe. A status of succeeded or canceled means Stripe is done with it for good, so any lock that existed only to protect that one payment can never be needed again. Anything still in progress, like requires_action or processing, should be left alone.
import stripe
def get_intent(intent_id):
if not intent_id:
return None
try:
return stripe.PaymentIntent.retrieve(intent_id)
except stripe.error.InvalidRequestError:
return Noneasync function getIntent(intentId) {
if (!intentId) return null;
try {
return await stripe.paymentIntents.retrieve(intentId);
} catch {
return null;
}
}Decide, with one pure function
Keep the decision in its own function that takes an order and a Stripe intent and returns an action. A pure function like this needs no network to test, which we do later. The rule is simple. If the order has no lock, skip it. If Stripe has no record of the intent, or the intent is still in progress, skip it. Only when the intent is fully settled do we clear the lock.
SETTLED_INTENT_STATUSES = {"succeeded", "canceled"}
LOCK_META_KEY = "_stripe_checkout_lock"
def lock_value_of(order):
for meta in order.get("meta_data") or []:
if meta.get("key") == LOCK_META_KEY and meta.get("value"):
return meta["value"]
return None
def decide(order, intent):
lock = lock_value_of(order) if order is not None else None
if not lock:
return ("skip", "no checkout lock on this order, nothing to clear")
if intent is None:
return ("skip", "no matching Stripe PaymentIntent, leave the lock alone")
if intent.get("status") not in SETTLED_INTENT_STATUSES:
return ("skip", "PaymentIntent is still in progress, the lock may still be needed")
return ("clear", f"PaymentIntent is {intent['status']}, the lock is stale")const SETTLED_INTENT_STATUSES = new Set(["succeeded", "canceled"]);
const LOCK_META_KEY = "_stripe_checkout_lock";
export function lockValueOf(order) {
for (const meta of order.meta_data || []) {
if (meta.key === LOCK_META_KEY && meta.value) return meta.value;
}
return null;
}
export function decide(order, intent) {
const lock = order ? lockValueOf(order) : null;
if (!lock) return ["skip", "no checkout lock on this order, nothing to clear"];
if (!intent) return ["skip", "no matching Stripe PaymentIntent, leave the lock alone"];
if (!SETTLED_INTENT_STATUSES.has(intent.status)) {
return ["skip", "PaymentIntent is still in progress, the lock may still be needed"];
}
return ["clear", `PaymentIntent is ${intent.status}, the lock is stale`];
}Clear the lock and log the transient key
When the action is clear, write an empty value over the order's lock meta through the REST API, then add an order note so the shop manager can see what happened and why. We also log the exact wp_options key the lock corresponds to, so a maintenance job or a WP-CLI command can sweep the actual expired rows out in bulk.
def transient_key_for(intent_id):
return f"_transient_wc_stripe_lock_{intent_id}"
def clear_lock(order):
requests.put(
f"{WOO_URL}/wp-json/wc/v3/orders/{order['id']}",
json={"meta_data": [{"key": LOCK_META_KEY, "value": ""}]},
auth=AUTH, timeout=30,
).raise_for_status()
requests.post(
f"{WOO_URL}/wp-json/wc/v3/orders/{order['id']}/notes",
json={"note": "Cleared a stale Stripe checkout lock left over from a finished "
"payment. The matching wp_options transient can now be purged."},
auth=AUTH, timeout=30,
).raise_for_status()export function transientKeyFor(intentId) {
return `_transient_wc_stripe_lock_${intentId}`;
}
async function clearLock(order) {
await woo(`/orders/${order.id}`, {
method: "PUT",
body: JSON.stringify({ meta_data: [{ key: LOCK_META_KEY, value: "" }] }),
});
await woo(`/orders/${order.id}/notes`, {
method: "POST",
body: JSON.stringify({
note: "Cleared a stale Stripe checkout lock left over from a finished payment. " +
"The matching wp_options transient can now be purged.",
}),
});
}Wire it together with a dry run guard
The loop ties every piece together. Notice the dry run guard. On the first few runs, leave DRY_RUN on so the script only reports what it would clear. Read the output, trust it, then switch it off to let it write. Run it once a day with cron, since this bloat builds up slowly.
Always start with DRY_RUN=true. This job writes to real orders, so you want to see its plan before it acts. Once the report looks right for a day, turn it off.
The full code
Here is the complete job in one file for each language. It reads settings from the environment, logs what it does, respects the dry run flag, and is safe to run again and again because it only ever touches an order whose lock has already served its purpose.
View this code on GitHub Full runnable folder with tests in the woocommerce-fixes repo.
"""Clear the WooCommerce Stripe checkout locks that are left behind as expired
transients in wp_options. Read only by default. Run on a schedule.
"""
import os
import logging
import stripe
import requests
from requests.auth import HTTPBasicAuth
logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("clear_stale_checkout_locks")
stripe.api_key = os.environ["STRIPE_SECRET_KEY"]
WOO_URL = os.environ["WOO_STORE_URL"].rstrip("/")
AUTH = HTTPBasicAuth(os.environ["WOO_CONSUMER_KEY"], os.environ["WOO_CONSUMER_SECRET"])
LOOKBACK_DAYS = int(os.environ.get("LOOKBACK_DAYS", "30"))
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"
SETTLED_INTENT_STATUSES = {"succeeded", "canceled"}
LOCK_META_KEY = "_stripe_checkout_lock"
def intent_id_of(order):
for meta in order.get("meta_data") or []:
if meta.get("key") == "_stripe_intent_id" and meta.get("value"):
return meta["value"]
tid = order.get("transaction_id")
return tid if tid and tid.startswith("pi_") else None
def lock_value_of(order):
for meta in order.get("meta_data") or []:
if meta.get("key") == LOCK_META_KEY and meta.get("value"):
return meta["value"]
return None
def transient_key_for(intent_id):
return f"_transient_wc_stripe_lock_{intent_id}"
def decide(order, intent):
lock = lock_value_of(order) if order is not None else None
if not lock:
return ("skip", "no checkout lock on this order, nothing to clear")
if intent is None:
return ("skip", "no matching Stripe PaymentIntent, leave the lock alone")
if intent.get("status") not in SETTLED_INTENT_STATUSES:
return ("skip", "PaymentIntent is still in progress, the lock may still be needed")
return ("clear", f"PaymentIntent is {intent['status']}, the lock is stale")
def get_intent(intent_id):
if not intent_id:
return None
try:
return stripe.PaymentIntent.retrieve(intent_id)
except stripe.error.InvalidRequestError:
return None
def recent_orders():
page = 1
after = f"{__import__('datetime').date.today() - __import__('datetime').timedelta(days=LOOKBACK_DAYS)}T00:00:00"
while True:
r = requests.get(
f"{WOO_URL}/wp-json/wc/v3/orders",
params={"after": after, "per_page": 50, "page": page},
auth=AUTH, timeout=30,
)
r.raise_for_status()
batch = r.json()
if not batch:
return
for order in batch:
yield order
page += 1
def clear_lock(order):
requests.put(
f"{WOO_URL}/wp-json/wc/v3/orders/{order['id']}",
json={"meta_data": [{"key": LOCK_META_KEY, "value": ""}]},
auth=AUTH, timeout=30,
).raise_for_status()
requests.post(
f"{WOO_URL}/wp-json/wc/v3/orders/{order['id']}/notes",
json={"note": "Cleared a stale Stripe checkout lock left over from a finished "
"payment. The matching wp_options transient can now be purged."},
auth=AUTH, timeout=30,
).raise_for_status()
def run():
cleared = 0
for order in recent_orders():
intent = get_intent(intent_id_of(order))
action, reason = decide(order, intent)
if action != "clear":
continue
key = transient_key_for(intent_id_of(order))
log.info("Order %s: %s. transient key %s. %s", order["id"], reason, key,
"would clear" if DRY_RUN else "clearing")
if not DRY_RUN:
clear_lock(order)
cleared += 1
log.info("Done. %d order(s) %s.", cleared, "to clear" if DRY_RUN else "cleared")
if __name__ == "__main__":
run()/**
* Clear the WooCommerce Stripe checkout locks that are left behind as expired
* transients in wp_options. Read only by default. Run on a schedule.
*/
import Stripe from "stripe";
import { pathToFileURL } from "node:url";
const stripe = new Stripe(process.env.STRIPE_SECRET_KEY || "sk_test_dummy");
const WOO_URL = (process.env.WOO_STORE_URL || "https://example.com").replace(/\/$/, "");
const AUTH = "Basic " + Buffer.from(
`${process.env.WOO_CONSUMER_KEY || "ck_dummy"}:${process.env.WOO_CONSUMER_SECRET || "cs_dummy"}`
).toString("base64");
const LOOKBACK_DAYS = Number(process.env.LOOKBACK_DAYS || 30);
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";
const SETTLED_INTENT_STATUSES = new Set(["succeeded", "canceled"]);
const LOCK_META_KEY = "_stripe_checkout_lock";
export function intentIdOf(order) {
for (const meta of order.meta_data || []) {
if (meta.key === "_stripe_intent_id" && meta.value) return meta.value;
}
const tid = order.transaction_id;
return tid && tid.startsWith("pi_") ? tid : null;
}
export function lockValueOf(order) {
for (const meta of order.meta_data || []) {
if (meta.key === LOCK_META_KEY && meta.value) return meta.value;
}
return null;
}
export function transientKeyFor(intentId) {
return `_transient_wc_stripe_lock_${intentId}`;
}
export function decide(order, intent) {
const lock = order ? lockValueOf(order) : null;
if (!lock) return ["skip", "no checkout lock on this order, nothing to clear"];
if (!intent) return ["skip", "no matching Stripe PaymentIntent, leave the lock alone"];
if (!SETTLED_INTENT_STATUSES.has(intent.status)) {
return ["skip", "PaymentIntent is still in progress, the lock may still be needed"];
}
return ["clear", `PaymentIntent is ${intent.status}, the lock is stale`];
}
async function woo(path, options = {}) {
const res = await fetch(`${WOO_URL}/wp-json/wc/v3${path}`, {
...options,
headers: { "Content-Type": "application/json", Authorization: AUTH, ...(options.headers || {}) },
});
if (!res.ok) throw new Error(`Woo ${path} returned ${res.status}`);
return res.json();
}
async function getIntent(intentId) {
if (!intentId) return null;
try {
return await stripe.paymentIntents.retrieve(intentId);
} catch {
return null;
}
}
async function* recentOrders() {
const after = new Date(Date.now() - LOOKBACK_DAYS * 86400000).toISOString();
let page = 1;
while (true) {
const batch = await woo(`/orders?after=${after}&per_page=50&page=${page}`);
if (!batch.length) return;
for (const order of batch) yield order;
page++;
}
}
async function clearLock(order) {
await woo(`/orders/${order.id}`, {
method: "PUT",
body: JSON.stringify({ meta_data: [{ key: LOCK_META_KEY, value: "" }] }),
});
await woo(`/orders/${order.id}/notes`, {
method: "POST",
body: JSON.stringify({
note: "Cleared a stale Stripe checkout lock left over from a finished payment. " +
"The matching wp_options transient can now be purged.",
}),
});
}
export async function run() {
let cleared = 0;
for await (const order of recentOrders()) {
const intent = await getIntent(intentIdOf(order));
const [action, reason] = decide(order, intent);
if (action !== "clear") continue;
const key = transientKeyFor(intentIdOf(order));
console.log(`Order ${order.id}: ${reason}. transient key ${key}. ${DRY_RUN ? "would clear" : "clearing"}`);
if (!DRY_RUN) await clearLock(order);
cleared++;
}
console.log(`Done. ${cleared} order(s) ${DRY_RUN ? "to clear" : "cleared"}.`);
}
if (import.meta.url === pathToFileURL(process.argv[1]).href) {
run().catch((err) => { console.error(err); process.exit(1); });
}Add a test
The decision rule is the part most worth testing, because it decides whether a real order gets written to. Because we kept decide pure, the test needs no network and no Stripe account. It just feeds in plain objects and checks the action.
from clear_stale_checkout_locks import decide
def intent(**over):
base = {"status": "succeeded", "id": "pi_1"}
base.update(over)
return base
def test_clear_when_lock_present_and_intent_settled():
order = {"meta_data": [{"key": "_stripe_checkout_lock", "value": "1"}]}
assert decide(order, intent())[0] == "clear"
def test_skip_when_no_lock():
order = {"meta_data": []}
assert decide(order, intent())[0] == "skip"
def test_skip_when_no_intent():
order = {"meta_data": [{"key": "_stripe_checkout_lock", "value": "1"}]}
assert decide(order, None)[0] == "skip"
def test_skip_when_intent_still_in_progress():
order = {"meta_data": [{"key": "_stripe_checkout_lock", "value": "1"}]}
assert decide(order, intent(status="requires_action"))[0] == "skip"import { test } from "node:test";
import assert from "node:assert/strict";
import { decide } from "./clear-stale-checkout-locks.js";
const intent = (over = {}) => ({ status: "succeeded", id: "pi_1", ...over });
test("clear when lock present and intent settled", () => {
const order = { meta_data: [{ key: "_stripe_checkout_lock", value: "1" }] };
assert.equal(decide(order, intent())[0], "clear");
});
test("skip when no lock", () => {
assert.equal(decide({ meta_data: [] }, intent())[0], "skip");
});
test("skip when no intent", () => {
const order = { meta_data: [{ key: "_stripe_checkout_lock", value: "1" }] };
assert.equal(decide(order, null)[0], "skip");
});
test("skip when intent still in progress", () => {
const order = { meta_data: [{ key: "_stripe_checkout_lock", value: "1" }] };
assert.equal(decide(order, intent({ status: "requires_action" }))[0], "skip");
});Case studies
The options table nobody had looked at in three years
A store running since 2023 had never once cleared its transients. The wp_options table had grown past 400,000 rows, most of them expired Stripe checkout locks with autoload=yes, and every admin page load was measurably slower because of it.
Running the job in dry run mode first surfaced the exact pattern of stale keys. A one time WP-CLI sweep guided by that list, followed by the job running weekly, kept the table from ever growing back to that size.
A flash sale that left thousands of dead locks in one weekend
During a big promotion, checkout volume spiked and a good number of shoppers abandoned carts mid payment. Each abandoned attempt still left its lock transient behind, since the lock is written before the outcome is known.
The team ran the job the following week, cleared every lock whose PaymentIntent had since settled or been canceled by Stripe, and set it to run nightly so the next sale would not leave the same mess.
After this runs on a schedule, expired checkout locks stop accumulating, and the site's own cleanup job has a clear list of transient keys it can safely remove. The options table stops growing for no reason, autoload stays small, and every page load skips reading rows that nobody was ever going to use again.
FAQ
Why do expired transients stay in the wp_options table?
WordPress only deletes an expired transient when something asks for that exact key again. A one off key like a Stripe checkout lock for a single PaymentIntent is never asked for a second time, so the row and its timeout row just sit in wp_options forever, usually with autoload set to yes.
Can I just run the built in clear transients tool?
It helps but it usually misses plugin specific one off keys such as per PaymentIntent locks, since many of those were written with a custom prefix instead of the standard transient API helpers the cleanup tools scan for.
Is it safe to clear a Stripe checkout lock with a script?
Yes, once Stripe confirms the matching PaymentIntent is fully settled, meaning succeeded or canceled. At that point the lock can never be needed again. Start in dry run mode to review the list before it writes anything.
Related field notes
Citations
On the problem:
- WordPress Developer Resources: Transients API, and how expiry is only checked when the transient is fetched. developer.wordpress.org/apis/transients
- WordPress support: wp_options growing very large from expired transients that are never cleaned up. wordpress.org/support
- WP-CLI handbook: the transient command, including how to delete all expired transients in bulk. developer.wordpress.org/cli/commands/transient
On the solution:
- Stripe API: retrieve a PaymentIntent and read its current status. docs.stripe.com/api/payment_intents/retrieve
- Stripe docs: the PaymentIntent lifecycle and which statuses mean the intent is finished. docs.stripe.com/payments/paymentintents/lifecycle
- WooCommerce REST API: update an order's meta data and add an order note. woocommerce.github.io/woocommerce-rest-api-docs
Stuck on a tricky one?
If you have a bug in WooCommerce, WooCommerce Subscriptions, or the WooCommerce Stripe gateway that you would rather hand off, this is the kind of work I do. Message me and we can work through it together.
Did this clean up your database?
If this shaved rows off your options table or sped up your admin, you can buy me a coffee. It is the best way to keep these field notes free and growing.
Buy me a coffee on Ko-fi