Acupuncture can help relieve tight muscles in a few ways:
In general, acupuncture can help release soft tissue constriction and loosen tight bands or "knots" in a muscle. This can be done with acupuncture, gua sha, or cupping.
More specifically, tight muscles can form trigger points, or hyperirritable bands of muscle that have pain referral patterns.
For example, a tight trapezius muscle can cause neck pain, jaw pain, and headaches (see: http://www.triggerpoints.net/muscle/trapezius). These need to be released via acupuncture. The muscle will involuntarily contract or release which will help return it to a more normal length and also reduce pain. There will often be soreness in the area for a day or two after treatment that feels like workout soreness.
On a deeper level, an overactive sympathetic nervous system can cause tight muscles. When we are in a sympathetic or "fight or flight" state, our heart rate speeds up, and our muscles tighten to prepare for action against the threat. Acupuncture has been shown to help balance the nervous system by calming the sympathetic nervous system and activating the parasympathetic "rest and digest" nervous system. This helps relieve muscle tension and relax the entire body.
As others have mentioned here it is important to identify the underlying cause of the muscle tension which can be things like an acute injury, overuse, chronic stress, poor posture, lack of movement/exercise, or lack of hydration/nutrients.