I don't know enough about child psychology, though it interests me greatly. I'll have to do some research, but I'd think 6 years old is old enough to start learning lessons like that. Given your wife's experience, she might have more insight on that type of stuff though?
It was actually one of my majors in college (double major Psych-HR).
Not to mention once you actually have them you start to get a feel for when they start to really get things. Sure they can associate if I do this, then I get this, but they don't UNDERSTAND why. Same thing with commitment, you can tell them til you are blue in the face that it's something they have to do when they say they are going to do something, but until they develop enough empathy to get the part about letting other people down (and they have to be emotionally vested in those people or teammates, if they are strangers, there's nothing to care about besides pride, and they REALLY don't get that yet) then it's just empty words.
You can't use your own traumatic experiences and regrets shape your decisions though, because that's trying to re-live your life through his. You have to let him tell you what is best for him, and if he is so miserable and distraught all you are going to do is weaken his trust in you.
Again, your kid, and you are the only one that will know if he's just trying to get out of putting in effort, or if he really doesn't like it, but either way this lesson has a double edged sword and trying to teach him a lesson by doing something he doesn't want to do (especially as trivial as playing a sport) can start to negatively impact your relationship. Kids at that age trust us so completely and blindly that they just can't understand why we are "punishing" them by forcing them to do something they don't like.