749540 - frequent database deadlocks when deleting recent searches

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[:glob ✱]

on bmo we're seeing frequent (daily) occurrences of:

DBD::mysql::db do failed: Deadlock found when trying to get lock; try restarting transaction [for Statement "DELETE FROM profile_search WHERE user_id = ? AND id <= ?"] at Bugzilla/Search/Recent.pm line 68.

we have about 75k rows in that table.

perhaps we need an index for user_id+id

Comment 1

[Frédéric Buclin]

I wonder if this could also help to mitigate https://bugs.freedesktop.org/show_bug.cgi?id=41983

Comment 2

[Max Kanat-Alexander]

One way to solve this would probably be:

SELECT id FROM profile_search WHERE user_id = ?

(filter ids manually in Perl)

DELETE FROM profile_search WHERE id IN (a, b, c, d)

Comment 3

[:glob ✱]

Attachment: patch v1

Comment 4

[David Lawrence [:dkl]]

Comment on attachment 656368 [details] [diff] [review]
patch v1

Review of attachment 656368 [details] [diff] [review]:
-----------------------------------------------------------------

Works for me and looks good. r=dkl

Comment 5

[Frédéric Buclin]

Comment on attachment 656368 [details] [diff] [review]
patch v1

>+        splice(@ids, scalar(@ids) - SAVE_NUM_SEARCHES);

Nit: simply write: splice(@ids, - SAVE_NUM_SEARCHES); A negative offset means "remove the last SAVE_NUM_SEARCHES items", which is exactly what you want here.

I didn't test your patch, but dkl did. So r=LpSolit

Comment 6

[David Lawrence [:dkl]]

(In reply to Frédéric Buclin from comment #5)
> Nit: simply write: splice(@ids, - SAVE_NUM_SEARCHES); A negative offset
> means "remove the last SAVE_NUM_SEARCHES items", which is exactly what you
> want here.

Just tried and both obviously give me the same results so I am fine either way.

dkl

Comment 7

[:glob ✱]

Committing to: bzr+ssh://[e-mail]/bugzilla/trunk/
modified Bugzilla/Search/Recent.pm
Committed revision 8387.

Committing to: bzr+ssh://[e-mail]/bugzilla/4.4/
modified Bugzilla/Search/Recent.pm
Committed revision 8385.