Ok after bit tracking I’ve worked out that the bouncer is dangling transactions when it’s sending warning messages. When https://gitlab.com/mailman/mailman/-/blob/3.3.4/src/mailman/model/bounce.py#... is called and it get to update last_warn_sent it does the following in the DB:
2021-03-23 14:42:50.622 AEST [11693] LOG: statement: UPDATE member SET last_warning_sent='2021-03-23T04:42:50.622015'::timestamp, total_warnings_sent=1 WHERE member.id = 169331
But it never COMMITs this transaction. So all write operations are now hung waiting for the transction to be closed as the member record has been modified. As a test I added config.db.commit() after https://gitlab.com/mailman/mailman/-/blob/3.3.4/src/mailman/model/bounce.py#... and restarted. And my mailman syncmembers worked perfectly and I didn’t have any hangs it correctly removed everyone it should have. In the database logs I can see the COMMIT.
I’m pretty sure this is not the right spot for a config.db.commit() but hopefully this helps point to what might be going wrong.
-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: Simon Coggins <s.coggins@cqu.edu.au> Date: Tuesday, 23 March 2021 at 11:01 am To: Abhilash Raj <maxking@asynchronous.in> Cc: "mailman-users@mailman3.org" <mailman-users@mailman3.org> Subject: Re: [EXTERNAL] Re: [MM3-users] Database deadlock when trying to delete a member.
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