First, I will say that I am a total pain wuss. Just the thought of being in pain makes me upset! That said, I have three tattoos, some of which required more than one session to complete, and I really think that despite the hour or so you're sitting there with someone sticking a needle into you repeatedly, if the tattoo is something you really really want, it's definitely worth it.
I'm usually a slow healer in general, but the worst of my tattoo healing takes about three days. By worst I mean the sore, skin flaking, my-god-is-the-tattoo-coming-off-with-these-chunks-of-skin healing stage! After that, it's still fairly raw and really itchy for a couple of weeks, but really nothing intolerable (oh, and do not directly scratch the tattoo in the itchy phase - recruit a good friend to sort of slap the tattoo for you. It sounds bizarre, but really helps and minimizes the itch!).
As far as what can go wrong? As long as you go to a reputable place (clean, good artists, etc.) and are good about aftercare, not really much. The worst thing to do is neglect the aftercare and not moisturize properly, as you could end up losing some of the ink on the tattoo! It will probably hurt (for some people it's uncomfortable, for some it hurts in that good way, and for some it's nearly intolerable, but I think it's all in how you look at it!), but I always try to look around at the artwork on the walls or think about something else, and I don't notice it as much. I love all of my tattoos, and looking at them now I don't really mind the discomfort I went through to get them. One hour of pain = lifetime of awesome body art!