Timeout and 500 error when unsubscribing from email issue thread notification

Summary

Clicking on the Unsubscribe from this thread link (e.g. https://gitlab.com/-/sent_notifications/REDACTED/unsubscribe) in issue update email notifications usually results in a 500 error due to db query timeout:

    "json": {
      "action": "unsubscribe",
      "component": "gitlab",
      "controller": "SentNotificationsController",
      "correlation_id": "9b8dc5b67208508b-ATL",
...
      "exception": {
        "backtrace": [
          "activerecord (7.2.3) lib/active_record/connection_adapters/postgresql_adapter.rb:899:in `exec_params'",
          "activerecord (7.2.3) lib/active_record/connection_adapters/postgresql_adapter.rb:899:in `block (2 levels) in exec_no_cache'",
          "activerecord (7.2.3) lib/active_record/connection_adapters/abstract_adapter.rb:1024:in `block in with_raw_connection'",
          "activesupport (7.2.3) lib/active_support/concurrency/null_lock.rb:9:in `synchronize'",
          "activerecord (7.2.3) lib/active_record/connection_adapters/abstract_adapter.rb:993:in `with_raw_connection'",
...
        "cause_class": "PG::QueryCanceled",
        "class": "ActiveRecord::QueryCanceled",
        "message": "PG::QueryCanceled: ERROR:  canceling statement due to statement timeout\n",
        "sql": "/*application:web,correlation_id:9b8dc5b67208508b-ATL,endpoint_id:SentNotificationsController#unsubscribe,db_config_database:gitlabhq_production,db_config_name:main_replica*/ SELECT \"p_sent_notifications\".* FROM \"p_sent_notifications\" WHERE \"p_sent_notifications\".\"partition\" = $1 AND \"p_sent_notifications\".\"reply_key\" = $2"
      },

Steps to reproduce

Try to unsubscribe from issue notifications via link in email.

Example Project

https://gitlab.com/gitlab-com/support/support-team-meta

What is the current bug behavior?

Query times out, 500 error, unsubscribe fails.

What is the expected correct behavior?

Successful unsubscription from issue.

Relevant logs and/or screenshots

See above.

Output of checks

Results of GitLab environment info

Expand for output related to GitLab environment info 

 (For installations with omnibus-gitlab package run and paste the output of: \`sudo gitlab-rake gitlab:env:info\`)  (For installations from source run and paste the output of: \`sudo -u git -H bundle exec rake gitlab:env:info RAILS_ENV=production\`)

Results of GitLab application Check

Expand for output related to the GitLab application check 

(For installations with omnibus-gitlab package run and paste the output of: `sudo gitlab-rake gitlab:check SANITIZE=true`)

(For installations from source run and paste the output of: `sudo -u git -H bundle exec rake gitlab:check RAILS_ENV=production SANITIZE=true`)

(we will only investigate if the tests are passing)

Possible fixes

Patch release information for backports

If the bug fix needs to be backported in a patch release to a version under the maintenance policy, please follow the steps on the patch release runbook for GitLab engineers.

Refer to the internal "Release Information" dashboard for information about the next patch release, including the targeted versions, expected release date, and current status.

High-severity bug remediation

To remediate high-severity issues requiring an internal release for single-tenant SaaS instances, refer to the internal release process for engineers.

Edited by 🤖 GitLab Bot 🤖