Personally, I find all spanking erotic. The more serious it feels when I am being spanked, the more erotic it feels afterwards. Although, since being in a Taken In Hand relationship, I have found that it is possible to be spanked in such a way that during I only feel pain, nevertheless, the after-effect is arousal. Indeed, I can't really imagine it having a beneficial effect on me if I didn't ultimately find it arousing.
I don't really believe that you can cure bad habits by spanking. You have to want to change the habit enough to do it. I honestly don't think that you can use spanking as some kind of aversion therapy. Fear of punishment does not seem to me a good reason for doing or not doing anything, especially not in my case, since fear is an emotion that tends to arouse defiance rather than submission in me. I used to be afraid of my husband when he lost his temper and shouted at me, but it never made me feel submissive, it just made me hate him.
I try to obey my husband (with mixed success) because I find it sexy. Being spanked for being disobedient, rude, bad-tempered or whatever doesn't actually stop me doing those things again, but it makes me feel more affectionate and loving towards my husband and therefore more submissive and therefore more likely to try to obey him in the future. But I can't be cured of bad habits by spanking; it just doesn't work that way for me.
I certainly don't think you should spank someone beyond what both of you are comfortable with. My husband very much dislikes it when he occasionally causes bruises; he doesn't like the idea of hurting me excessively. Charlotte, who has been trying to cure a bad habit through physical chastisement, said that her husband is reluctant to cause her too much pain. I think that is a good thing!