Checklist

• I have verified that the issue exists against the main branch of Celery.
• This has already been asked to the discussions forum first.
• I have read the relevant section in the
  contribution guide
  on reporting bugs.
• I have checked the issues list
  for similar or identical bug reports.
• I have checked the pull requests list
  for existing proposed fixes.
• I have checked the commit log
  to find out if the bug was already fixed in the main branch.
• I have included all related issues and possible duplicate issues
  in this issue (If there are none, check this box anyway).
• I have tried to reproduce the issue with pytest-celery and added the reproduction script below.

Mandatory Debugging Information

• I have included the output of celery -A proj report in the issue.
• I have verified that the issue exists against the main branch of Celery.
• I have included the contents of pip freeze in the issue.
• I have included all the versions of all the external dependencies required
  to reproduce this bug.

Optional Debugging Information

• I have tried reproducing the issue on more than one Python version
  and/or implementation.
• I have tried reproducing the issue on more than one message broker and/or
  result backend.
• I have tried reproducing the issue on more than one version of the message
  broker and/or result backend.
• I have tried reproducing the issue on more than one operating system.
• I have tried reproducing the issue on more than one workers pool.
• I have tried reproducing the issue with autoscaling, retries,
  ETA/Countdown & rate limits disabled.
• I have tried reproducing the issue after downgrading
  and/or upgrading Celery and its dependencies.

Related Issues and Possible Duplicates

Related Issues

• None

Possible Duplicates

• None

Environment & Settings

Celery version: 5.4.0 / 5.5.x / main (issue exists in all versions)

Result Backend: db+mysql://user:pass@host/dbname (SQLAlchemy with MySQL 8.0 / InnoDB)

software -> celery:5.4.0 (opalescent) kombu:5.3.4 py:3.11.0
billiard:4.2.0 py-amqp:5.2.0
platform -> system:Linux arch:64bit
kernel version:5.15.0-91-generic
loader -> celery.loaders.app.AppLoader
settings -> transport:amqp results:db+mysql://user:***@localhost/celery

result_backend: 'db+mysql://user:***@localhost/celery'
result_expires: 86400

Steps to Reproduce

Required Dependencies

• Minimal Python Version: 3.10+
• Minimal Celery Version: Any version using database backend
• Minimal Kombu Version: N/A
• Minimal Broker Version: Any (RabbitMQ, Redis, etc.)
• Minimal Result Backend Version: MySQL 8.0+ with InnoDB storage engine
• Minimal OS and/or Kernel Version: Any Linux distribution
• Minimal Broker Client Version: N/A
• Minimal Result Backend Client Version: SQLAlchemy 2.x, mysqlclient or PyMySQL

Python Packages

celery==5.4.0
kombu==5.3.4
SQLAlchemy==2.0.25
mysqlclient==2.2.0
amqp==5.2.0
billiard==4.2.0

Other Dependencies

Minimally Reproducible Test Case

# celery_app.py
from celery import Celery

app = Celery('tasks')
app.conf.update(
    broker_url='amqp://guest:guest@localhost//',
    result_backend='db+mysql://user:pass@localhost/celery_results',
    result_expires=86400,  # 24 hours
)

@app.task
def sample_task(x):
    return x * 2

-- Check for lock waits
SHOW ENGINE INNODB STATUS;

-- Or query performance_schema
SELECT * FROM performance_schema.data_lock_waits;

EXPLAIN DELETE FROM celery_taskmeta 
WHERE date_done < DATE_SUB(NOW(), INTERVAL 1 DAY);

-- Result shows type=ALL (full table scan) because date_done has no index

Expected Behavior

When cleanup() executes the delete query filtering by date_done, it should:

1. Use an index to efficiently locate expired records
2. Only lock the specific rows being deleted
3. Complete quickly without blocking concurrent _store_result() operations
4. Not cause innodb_lock_wait_timeout errors for other transactions
   The celery_taskmeta and celery_tasksetmeta tables should have an index defined on the date_done column since it's used for the periodic cleanup operation.

Actual Behavior

The cleanup() method in celery/backends/database/__init__.py performs:

session.query(self.task_cls).filter(
    self.task_cls.date_done < (now - expires)).delete()

However, examining models.py, the Task model lacks an index on date_done:

class Task(ResultModelBase):
    __tablename__ = 'celery_taskmeta'
    __table_args__ = {'sqlite_autoincrement': True}  # No index defined here
    
    date_done = sa.Column(sa.DateTime, ...)  # Missing index

• Consequences observed in production:

1. MySQL performs a full table scan (EXPLAIN shows type: ALL)
2. InnoDB acquires row-level locks on every scanned row during the DELETE
3. Concurrent _store_result() calls block waiting for these locks
4. After innodb_lock_wait_timeout (default 50s), transactions fail with: OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')
5. Tasks remain stuck in STARTED state because their final status was never persisted

• MySQL lock monitoring output during cleanup:

---TRANSACTION 12345, ACTIVE 45 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 100, query id 5000 localhost user updating
DELETE FROM celery_taskmeta WHERE date_done < '2026-02-06 04:00:00'
------- TRX HAS BEEN WAITING 45 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 50 page no 100 n bits 200 index PRIMARY

• Suggested Fix
  Add an index to the model definitions:

# In celery/backends/database/models.py

class Task(ResultModelBase):
    __tablename__ = 'celery_taskmeta'
    __table_args__ = (
        sa.Index('ix_celery_taskmeta_date_done', 'date_done'),
        {'sqlite_autoincrement': True},
    )


class TaskSet(ResultModelBase):
    __tablename__ = 'celery_tasksetmeta'
    __table_args__ = (
        sa.Index('ix_celery_tasksetmeta_date_done', 'date_done'),
        {'sqlite_autoincrement': True},
    )

• Current Workaround
  Users can manually add the index:

CREATE INDEX ix_celery_taskmeta_date_done ON celery_taskmeta(date_done);
CREATE INDEX ix_celery_tasksetmeta_date_done ON celery_tasksetmeta(date_done);