You are correct it’s the bounce runner locking the tables. If I kill -15 the bounce runner, the session lock releases and my sync continues correctly.
├─python3,60157 /opt/mailman/venv/bin/runner -C /etc/mailman.cfg --runner=bounces:0:1
So something in the bounce runner is not closing transactions correctly from the looks of it.
-Simon
[signature_201226327]<https://www.cqu.edu.au/> Mr Simon Coggins Principal Systems Engineer | Digital Services Directorate CQUniversity Australia, Level 6.13, 160 Ann Street, Brisbane, QLD 4000 P +61 7 3295 1182 | X 51182 | M 0408 115 861 | E s.coggins@cqu.edu.au<mailto:.coggins@cqu.edu.au> [cquconnect]<https://www.cqu.edu.au/social-media> I respectfully acknowledge the Traditional Owners of the land on which we work and learn, and pay respect to the First Nations Peoples and their Elders, past, present and future.
This communication may contain privileged or confidential information. If you have received this in error, please return to sender and delete. CRICOS: 00219C | RTO Code 40939
From: Abhilash Raj <maxking@asynchronous.in> Date: Friday, 19 March 2021 at 4:58 pm To: Simon Coggins <s.coggins@cqu.edu.au> Cc: "mailman-users@mailman3.org" <mailman-users@mailman3.org> Subject: [EXTERNAL] Re: [MM3-users] Database deadlock when trying to delete a member.
This is an external email from: maxking@asynchronous.in
This is helpful.
The two pending queries looks like the job of bounce runner. Since it runs every 2m to check if any Member’s bounce score is above the threshold and if any warning needs to be sent or delivery needs to be disabled.
It looks to me that the database query has been running for 1min 58sec, which is longer than I’d expect. Are you seeing that query *always* there when you dump the list of queries?
I am not sure what the empty COMMIT queries are really.
Abhilash
On Mar 18, 2021, at 5:31 PM, s.coggins@cqu.edu.au wrote:
Just to add to this. Looking at the active queries in PostgreSQL there appear to be a bunch of commits that haven't finished. I don't know how it all fits together but it looks like the commits aren't finishing and transactions are staying open, so when I hit an open transaction all write operations for the DB hang until the transaction has finished.
postgres=# SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start desc; pid | age | usename | query
--------+-----------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
15767 | 00:01:58.30353 | mailman | SELECT member.id AS member_id, member._member_id AS member__member_id, member.role AS member_role, member.list_id AS member_list_id, member.moderation_action AS member_moderation_action, mem ber.address_id AS member_address_id, member.preferences_id AS member_preferences_id, member.user_id AS member_user_id, member.bounce_score AS member_bounce_score, member.last_bounce_received AS member_last_bounce_received, member.la st_warning_sent AS member_last_warning_sent, member.total_warnings_sent AS member_total_warnings_sent + | | | FROM member JOIN mailinglist ON member.list_id = mailinglist.list_id JOIN preferences ON preferences.id = member.preferences_id
- | | | WHERE mailinglist.process_bounces = true AND member.total_warnings_sent >= mailinglist.bounce_you_are_disabled_warnings AND preferences.delivery_status = 3 18033 | 00:05:28.255636 | mailman | DELETE FROM member WHERE member.id = 169331 15762 | 00:11:42.842237 | mailman | COMMIT 15740 | 00:11:59.12324 | mailman | COMMIT 15760 | 00:12:01.212976 | mailman | COMMIT 15757 | 00:12:01.542144 | mailman | COMMIT 15755 | 00:12:01.708328 | mailman | COMMIT 15754 | 00:12:01.804737 | mailman | COMMIT 15751 | 00:12:01.921644 | mailman | COMMIT 15745 | 00:12:02.72934 | mailman | COMMIT 15743 | 00:12:02.795885 | mailman | COMMIT 15742 | 00:12:02.948795 | mailman | COMMIT 15738 | 00:12:03.3471 | mailman | COMMIT 15664 | 00:12:08.480084 | mailman | COMMIT (21 rows)
I've checked my production system and I can see the same COMMIT queries running. Some for 2 days (since the last restart).
Mailman-users mailing list -- mailman-users@mailman3.org To unsubscribe send an email to mailman-users-leave@mailman3.org https://lists.mailman3.org/mailman3/lists/mailman-users.mailman3.org/<https://lists.mailman3.org/mailman3/lists/mailman-users.mailman3.org>
-- thanks, Abhilash Raj (maxking)