Specifically the purpose of CAA is to enable a subscriber (say, the owner of ycombinator.com) to tell trustworthy Certificate Authorities thanks, but no thanks. It is not a message for anybody else. If you're not a CA you don't need to read CAA records.
For example, say you're Facebook, you've got an arrangement with DigiCert where on top of the Ten Blessed Methods of the Baseline Requirements, DigiCert promises to go exclusively through a six man "Certificate management" team at Facebook for all .facebook.com and .fb.com names. Even if Marketing really wants coca-cola-advert.facebook.com they can't get a certificate without an OK from that six man team. Well, (and something similar really happened years ago) the deal you cut with DigiCert doesn't magically apply to every other CA. The Baseline Requirements do, but not your custom deal, so other CAs don't need to know about your rules and may issue coca-cola-advert.facebook.com certificates to the marketing guys who've set up the coca-cola-advert.facebook.com web site just obeying the Ten Blessed Methods.
CAA records are in the Baseline Requirements, and so Facebook can write a CAA which says "Only DigiCert may issue". And if you look with your preferred DNS querying tool, that is exactly what they did. CAA for facebook.com is 0 issue "digicert.com"
If you posit that there are crooks at some other CA issuing bogus certificates, CAA doesn't stop that. The crooks can ignore such a rule, the same way a crook can ignore the "Employees only" sign on a door. But, we can see what the public CAs are doing, so, if any of them are crooked we can notice that and kick them out. For the most part humans, including those running a CA, can be lazy and incompetent but they aren't malevolent.
The protection for this is in certificate transparency, as Chrome will throw up a warning if a certificate is valid other than it never showing up in the CT logs. See: https://no-sct.badssl.com/
CAA combined with this CT requirement means that businesses serious about issuance can set up a service to watch CT logs and get notified every time a certificate is issued, so any would-be CA attacker would have to be pretty quick with their attack if they wanted to impersonate fb.com, and that CA would be questioned by the CA/B community pretty quickly for breaking CAA policies.
They shouldn't. CAA controls issuance, but the browser isn't performing issuance.
It's completely allowed (a bit paranoid, but allowed) to set CAA to forbid everybody from issuing except when you are actually getting new certificates.
But now your hypothetical "CAA checking" browser thinks the certificates issued this way aren't valid, because when it visited, hours, weeks or even months later, the CAA record did not allow the certificate it saw.
I think those two are examples of why it's not getting better. People point at these lame kludges and think "well maybe it's secure". But CT doesn't stop attacks and literally nobody looks at it anyway. And nobody uses CAA, and even if they did, it depends on the security of their name servers, the DNS protocol, BGP, and other things, all of which are insecure.
There is simply no way to secure a domain name without having asserted it cryptographically from the people who actually control the domain: the registrar. Only the registrar knows who owns the domain, and what that owner will allow to happen with the domain. A CSR must go through the registrar, and the registrar must pass the request to the human who owns the domain for validation. (This can be automated by the owner for automatic cert renewal.) This puts the power in the hands of the people who really control the domain, rather than a bunch of wonky insecure kludges to kinda-sorta-validate who might control a DNS record or some temporary IP space or an email address or some other nonsense.
It's friggin' 2022. If we land on Mars before we figure out how to control domain names securely, we are truly an incompetent industry.
For example, say you're Facebook, you've got an arrangement with DigiCert where on top of the Ten Blessed Methods of the Baseline Requirements, DigiCert promises to go exclusively through a six man "Certificate management" team at Facebook for all .facebook.com and .fb.com names. Even if Marketing really wants coca-cola-advert.facebook.com they can't get a certificate without an OK from that six man team. Well, (and something similar really happened years ago) the deal you cut with DigiCert doesn't magically apply to every other CA. The Baseline Requirements do, but not your custom deal, so other CAs don't need to know about your rules and may issue coca-cola-advert.facebook.com certificates to the marketing guys who've set up the coca-cola-advert.facebook.com web site just obeying the Ten Blessed Methods.
CAA records are in the Baseline Requirements, and so Facebook can write a CAA which says "Only DigiCert may issue". And if you look with your preferred DNS querying tool, that is exactly what they did. CAA for facebook.com is 0 issue "digicert.com"
If you posit that there are crooks at some other CA issuing bogus certificates, CAA doesn't stop that. The crooks can ignore such a rule, the same way a crook can ignore the "Employees only" sign on a door. But, we can see what the public CAs are doing, so, if any of them are crooked we can notice that and kick them out. For the most part humans, including those running a CA, can be lazy and incompetent but they aren't malevolent.