In many fictional media sometimes a character goes through a lot of pain and ends up being unconscious because of it. It this true or at least possible? In that case, why that would happen?
TL:DR: No, not usually.
It this true or at least possible?
It is possible, but it is not common. People in an incredible amount of pain most often endure it without passing out. Others pass out at the sight of a drop of blood (a classmate of mine in medical school, every single time), the sight of a needle (a man in the ED who was about to get stitches), the sight of a child's minor injury (my friend whose daughter sustained a minor laceration at my house) or for other reasons.
If you see a theme here, that's part of the answer.
Neurocardiogenic syncope (NS) is the most common cause for loss of consciousness. Basically what that means is that fainting is caused by the nervous system slowing the heart rate down (in the presence of venous dilation) to the point that not enough blood is being pumped to the brain to keep someone conscious. These people fall, usually to the ground, where they regain consciousness as the heart rate increases and more blood is made available to get more oxygen to the brain.
Neurocardiogenic syncope is a collective term used to describe the clinical syndromes of syncope that result from inappropriate, and often excessive, autonomic reflex activity, and manifest as abnormalities in the control of vascular tone and heart rate. These include carotid sinus syndrome, vasovagal syncope, and the syndromes of cough, deglutition, and micturition syncope.
The vagus nerve can be stimulated by fear (including fear of pain.)
However, pain causes the opposite effect:
Pain stimulates the sympathetic nervous system, which in turn increases heart rate (HR) and causes peripheral vasoconstriction.
It also increases the contractility of heart muscle (it is a positive inotrope.)
To (over)simplify, mild fear in susceptible people stimulates the vagus nerve to the point of causing decreased blood flow to the brain resulting in passing out. Pain, however, causes a sympathetic output which counteracts (and inhibits) the fear driven vagal effects on the heart. The more adrenaline and noradrenaline pain causes, the less likely the person is to pass out from pain.
In all of my years as an ER doctor, I have never seen anyone pass out (or give a history of passing out) from pain, and I have seen all kinds of pain of every severity.
Recently I sustained a severe (comminuted, displaced) fracture of my lower leg. Without any pain management, an orthopedic resident pulled, twisted, pushed and otherwise manipulated my leg for about 20 minutes to try to align the fracture better in anticipation of surgery the next day. (He failed.) I think it was the worst pain I've ever endured, but I was very much awake.
Finally, I knew and worked with a very experienced emergency physician who was in an MVA in which she was pinned in her vehicle, and was found on extraction to have lost both of her lower legs. Because they were still crushed, there wasn't sufficient blood loss to cause her to pass out, and she directed her own rescue to the paramedics (ED doctors give what's referred to as "medical command" verbally to paramedics in the field. She effectively - and without passing out - gave the paramedics her own medical command.)