There is some pain involved, yes, but it is very manageable for most. People have described it as a “hot scratch” but if it were really that bad, nobody would ever come back to get more. There are numbing creams out available, but they are to be applied at least an hour before the tattoo and wrapped in cellophane. Even still, these topical agents will often wear off within the first hour or so of tattooing. It is always recommended to reserve these products for extended sessions or extremely painful spots.
Nick Baxter has an excellent write-up on what to expect with the pain and mental preparedness involved with tattoo sessions here.
“First, it’s important to change the mental focus of a tattoo experience from a negative concept of pain, which tends to carry a story of suffering, to a neutral concept of sensation, which tends to carry only observations.”