At least for humans. It's fairly easy to write a script with current technology even for an average "hacker" to solve them. But on the other hand it's extremely hard, nearly impossible for a person with special needs to complete them. Even for average person solving some of CAPTCHAs is a hassle.
Most of the popular CAPTCHAs services are "robot-friendly" and providers don't care who solves them, they just need data, they don't need to prove you're human.
Being "robot-friendly" still doesn't mean it's a walk in the park, and it's a hurdle that spammers will have to account for. If your site is a low value target or the spamming in general is low-value, it's often effective. Running any sort of small time open to the internet blog or forum will make the value of CAPTCHAs abundantly clear. The issue here is being inclusive to people, not making them "work" against robots.
A particular gaming forum uses game covers and asks users to select the correct title. This is quick enough to decipher for a human, while any OCR bots would have a difficult time with it. They could build a database of all covers, but it would be difficult to keep it up to date, and site owners could simply add more variation, so I doubt any bot authors bother to do so.