Some forms of massage are very superficial and can be very relaxing. A sports massage is generally applied deeper to manipulate muscle tissue more effectively. This can induce a deeper relaxation although at the time some techniques (not all) may border on the uncomfortable.
However, in certain situations such as pre and post event treatments the massage will typically be much lighter to either stimulate, relax or aid recovery.