Thank you for all that detail, it's really helpful.
So, I'm going to make two recommendations to start. One is that you ask him to not vent to you about his disappointment. Even if he's not doing it to be manipulative, it's still clearly making you feel like crud and is making you think you should put your own boundaries and comfort aside so he won't feel that way. Ideally, when we're disappointed that a partner turned us down for sex, the reaction to aim for is something like "okay, some other time then." Not venting, not saying that we're disappointed or feel bad for being disappointed; just accepting the no and moving on. So, the next time you need to say no and he says he's disappointing, do you feel like you could say, "hey, I'm not the best person to process that with, do you want to take a break from chatting and talk again later?"
The other big recommendation is to, as much as you can, stop framing sex as a "commitment." Being sexual with another person isn't a commitment; it's a very personal decision and a fluid process, one which we can (and should) stop when we need to. Our interest in sex is influenced by so many things, including the stressors or other things we encounter during our day. That means that we could say "sure, I'd love to sext tonight" and mean it, but then find that when the time comes you're really not in the mood. Is that disappointing to the other person? Sure. Is it rude or a sign that you're causing a problem? Nope, absolutely not. In fact, his ask that you tell him you're not in the mood well before you're supposed to have sex isn't that realistic; yes, sometimes you can tell you're having a day where you just won't be in the mood. But lots of times, it may take until sex is close to happening for you body and brain to go "nope, not feeling it." Does that all make sense?
Since it sounds like boundaries are something you have a hard time with in general, I also want to give you this article, since some of the tools in it might help you out: Be Your Own Superhero: Learning How and When to Stand Up for Ourselves