I finally figured it out, and it was mostly due to some configuration issues I made.
First I saw that it was definitely an issue with one of my pihole instances (piholeB). When I did nslookup pointed to piholeA everything worked fine but pointed at piholeB it was timing out, which likely is what led to Caddy having issues getting the certificates. I must have just got lucky originally where most of the domains initially got their DNS from working piholeA and those last 2 newer ones got their DNS from piholeB and so failed.
For more context, piholeA is pihole + unbound running in an LXC in PVE. Since the only other server I have on my network is a Synology machine, I run piholeB on that as a container. At some point, when I was upgrading to pihole 6 I think I did it on piholeA and then copied the configuration to piholeB, which included relevant unbound settings. This led to piholeB pointing at the recommended 127.0.01 IP for unbound, which works for piholeA where that is true, but for piholeB the pihole image didn't come with unbound and I didn't have another unbound instance installed (I don't remember anymore but I guess in the past piholeB maybe just didn't have unbound at all). So this led to the observed issue because queries to piholeB led to querying a non-existent unbound instance, and so led to a timeout.
Adding an unbound container (with proper configuration), and then changing the pihole configuration, solved the issue, and Caddy has been able to fetch new certificates for all of my domains.
So in the end it was mostly a silly misconfiguration on my part, but I wanted to post the solution here in case anyone comes across similar issues, as it was difficult for me to diagnose (with my network-related knowledge) from the errors I was getting and not understanding why sometimes it was successful.
Although for some reason I can't get it to properly point to the unbound IP, it keeps changing back to 127.0.0.1. I'm guessing it's maybe nebula_sync that I have running that is syncing the settings from my other pihole that uses that IP for unbound.