How a 4GB WooCommerce Sessions Table Was Deadlocking Checkout on Every Cleanup Run

A client's WooCommerce store — around 300 orders a day, running on a 4-core VPS with CloudPanel, PHP 8.2-FPM, Nginx, and MariaDB 10.11 — had developed a pattern nobody could explain. Twice a day, for roughly two to five minutes, the checkout page would either hang or throw a generic database error. Then it would recover on its own. No code changes, no deploys, no traffic spikes. Just two brief windows of chaos every 24 hours.

The hosting provider had checked the usual suspects: disk space, memory, PHP-FPM workers. Everything looked fine. The site had been doing this for weeks before anyone connected the dots between the timing and the outages.

The Pattern in the Error Logs

I SSH'd in and pulled the MariaDB error log, filtering for the last week:

grep -i "deadlock" /var/log/mysql/error.log | tail -20

Deadlocks. Dozens of them. All on the same table: wp_woocommerce_sessions. And they clustered around two times each day — roughly 00:00 and 12:00 UTC.

That timing was the giveaway. WooCommerce schedules a cleanup cron called woocommerce_cleanup_sessions to run twice daily. It fires a DELETE query to purge expired sessions. On a healthy store with a few hundred sessions, that query finishes in milliseconds. On this store, it was triggering deadlocks that blocked checkout for minutes.

Finding the Bloat

I checked the table size:

SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
       table_rows
FROM information_schema.tables
WHERE table_name = 'wp_woocommerce_sessions';

+------------------------------+----------+------------+
| table_name                   | total_mb | table_rows |
+------------------------------+----------+------------+
| wp_woocommerce_sessions      |  4,218   |     74,612 |
+------------------------------+----------+------------+

4.2GB for a sessions table. That is not normal. A healthy sessions table for a store this size should be a few megabytes at most.

I counted how many sessions had actually expired:

SELECT
  COUNT(CASE WHEN session_expiry < UNIX_TIMESTAMP() THEN 1 END) AS expired,
  COUNT(CASE WHEN session_expiry >= UNIX_TIMESTAMP() THEN 1 END) AS active
FROM wp_woocommerce_sessions;

+---------+--------+
| expired | active |
+---------+--------+
|  69,441 |  5,171 |
+---------+--------+

Over 69,000 expired sessions sitting in the table. Only 5,171 were active. The cleanup cron was supposed to purge these, but it was clearly failing — or worse, it was trying and causing the deadlocks in the process.

Why the Cleanup Was Destroying Checkout

The WC_Session_Handler::cleanup_sessions() method runs this query:

DELETE FROM wp_woocommerce_sessions
WHERE session_expiry < UNIX_TIMESTAMP();

Simple enough. But here is the problem: the wp_woocommerce_sessions table only has indexes on session_id (primary key) and session_key (unique). There is no index on session_expiry.

Without an index, MariaDB has to do a full table scan to find the 69,000 rows that match session_expiry < UNIX_TIMESTAMP(). On a 4GB table with 74,000 rows of serialised cart data, that scan takes seconds — not milliseconds. While the DELETE is running, it holds row-level locks on every row it touches. InnoDB's gap locking means it can also lock gaps between rows, blocking INSERT operations on the same table.

Every customer who loads the checkout page during that window triggers a session write. WooCommerce calls WC_Session_Handler::save_data(), which runs an INSERT ... ON DUPLICATE KEY UPDATE on the same table. That insert hits the locks from the cleanup DELETE, waits, and if the lock wait timeout expires, the customer gets a database error. If two transactions deadlock each other — the cleanup deleting a row that the checkout insert is waiting on, and vice versa — MariaDB kills one of them. Usually the customer's transaction.

I confirmed this by checking the InnoDB status during one of the cleanup windows:

mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 20 "LATEST DETECTED DEADLOCK"

The output showed exactly what I expected: a DELETE on wp_woocommerce_sessions waiting for a lock held by an INSERT, and the INSERT waiting for a lock held by the DELETE.

Why the Sessions Had Accumulated

The cleanup cron was registered — I verified with WP-CLI:

wp cron event list --fields=hook,next_run_relative | grep session

woocommerce_cleanup_sessions    2 hours 14 minutes

It was scheduled and running. But it was only partially succeeding. The cleanup method in WooCommerce (prior to version 10.1) deletes expired sessions in a single unbounded query. On a table this large, the query either timed out or deadlocked before finishing. Each run would delete some rows, deadlock, and stop. Meanwhile, new sessions accumulated faster than the cleanup could purge them. A classic backlog spiral.

The session_value column made things worse. It stores serialised PHP arrays containing full cart contents — product IDs, quantities, shipping calculations, applied coupons. For a store with 300+ SKUs and complex shipping rules, a single session could be 50-100KB of serialised data. Multiply that by 74,000 rows and you get a 4GB table.

The Fix: Three Steps

Step 1: Manual Cleanup in Batches

Deleting 69,000 rows in one query would deadlock again. I ran it in batches of 1,000, with a short sleep between each batch to let checkout transactions through:

while true; do
  DELETED=$(mysql -N -e "DELETE FROM wp_woocommerce_sessions WHERE session_expiry < UNIX_TIMESTAMP() LIMIT 1000; SELECT ROW_COUNT();")
  echo "Deleted: $DELETED"
  if [ "$DELETED" -eq 0 ]; then
    break
  fi
  sleep 0.5
done

This took about four minutes to clear all 69,000 expired sessions without a single deadlock. Batched deletes with a LIMIT clause only lock the rows they touch, and the 500ms pause gives concurrent transactions a window to complete.

Step 2: Add the Missing Index

This is the fix that WooCommerce should have shipped years ago. The session_expiry column has no index, so every cleanup query does a full table scan. Adding the index is a one-liner:

ALTER TABLE wp_woocommerce_sessions ADD INDEX idx_session_expiry (session_expiry);

On the now-cleaned table (5,171 rows, ~180MB after OPTIMIZE TABLE), this completed in under a second. After the index, the cleanup query's EXPLAIN plan changed from a full table scan to an index range scan — exactly what it should be.

I verified the improvement:

EXPLAIN DELETE FROM wp_woocommerce_sessions
WHERE session_expiry < UNIX_TIMESTAMP();

Before the index: type: ALL, rows: 74612. After the index: type: range, rows: 12, key: idx_session_expiry.

Step 3: Reclaim the Disk Space

InnoDB does not release disk space when you delete rows. The table was still occupying 4GB on disk even though it only contained 5,000 rows. I ran:

OPTIMIZE TABLE wp_woocommerce_sessions;

This rebuilt the table and reclaimed the space. The table dropped from 4,218MB to 42MB.

Preventing It From Happening Again

Monitoring the Table Size

I added a simple cron job on the server to alert if the sessions table grows beyond 500MB:

#!/bin/bash
# /etc/cron.daily/check-wc-sessions

SIZE=$(mysql -N -e "SELECT ROUND((data_length + index_length) / 1024 / 1024) FROM information_schema.tables WHERE table_name = 'wp_woocommerce_sessions';" wp_production)

if [ "$SIZE" -gt 500 ]; then
  echo "WooCommerce sessions table at ${SIZE}MB" \
    | mail -s "Alert: WC sessions bloat on $(hostname)" [email protected]
fi

Verifying the Cleanup Cron

The cleanup cron can silently disappear if WooCommerce is deactivated and reactivated, or if a database migration goes wrong. I check it monthly:

wp cron event list --fields=hook,next_run_relative | grep woocommerce_cleanup_sessions

If it is missing, you can re-register it:

wp cron event schedule woocommerce_cleanup_sessions now twicedaily

WooCommerce 10.1+ Changes

If you are running WooCommerce 10.1 or later (released August 2025), session management has improved significantly. All WooCommerce cron jobs — including session cleanup — now run through Action Scheduler instead of WP-Cron. Session expiration is capped at 30 days, and logged-in user sessions are stored in the sessions table instead of being duplicated in usermeta.

WooCommerce 10.3 introduced an experimental feature that clears empty sessions for guest visitors — sessions where nothing has been added to the cart. This reduces the volume of sessions created by browsing traffic that never converts. You can enable it via add_filter( 'woocommerce_clear_empty_sessions', '__return_true' ); — but test on staging first, as it may affect plugins that use session cookies for non-cart purposes.

Even with these improvements, the missing index on session_expiry has not been added to the WooCommerce schema as of version 10.7. If you are running any version of WooCommerce, add it manually.

How to Check Your Own Store

Run this query to see if you have a problem:

SELECT
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
  table_rows,
  (SELECT COUNT(*) FROM wp_woocommerce_sessions WHERE session_expiry < UNIX_TIMESTAMP()) AS expired_sessions
FROM information_schema.tables
WHERE table_name = 'wp_woocommerce_sessions';

If expired_sessions is more than a few hundred, or size_mb is over 100, you have a cleanup problem. Check your indexes:

SHOW INDEX FROM wp_woocommerce_sessions;

If you do not see an index on session_expiry, add one. Then run the batched cleanup script above. Your checkout will thank you.

---

Stop Firefighting. Start Maintaining.

I manage 70+ WordPress sites for agencies and businesses. Whether you need ongoing maintenance, emergency support, or a one-off performance fix — I can help.

View Maintenance Plans | Get in Touch