I was also having trouble with this for a long time. The explanation which finally worked for me was the following:
For the purposes of parallel transport along a particular circle of latitude, the sphere can be replaced by the cone which is tangent to the sphere along that circle, since a “flatlander” living on the surface and travelling along the circle would experience the same “twisting of the tangent plane in the ambient space” regardless of whether the surface is a sphere or a cone.
And for the cone, there's an easy way of seeing that there is indeed a rotation of the transported vector with respect to the tangent vector of the curve: just cut the cone open and lay it flat on the table, so that parallel transport becomes simply ordinary parallel transport on the plane.
A picture says more than a thousand words, and I found a good one here:
A simple discussion of the Berry Phase (N. P. Ong, Physics, Princeton Univ.).
Referring to your first set of figures, take the unrolled cone in figure (b).
Treating it as a shape cut from a piece of paper (being careful not to cut away the red arrows), glue the two straight edges together. Allow the resulting figure to form the shape of a cone.
Place the sphere in the open mouth of the cone so the sphere is like a scoop of ice cream in an ice cream cone. Choose the radius of the sphere so that it sits in the mouth of the cone as shown in figure (a), so that the curved edge of the shape from figure (b) touches the sphere along a small circle. Then the red arrows will be tangent the the sphere along that small circle, and they will show how a vector is parallel transported around that small circle.
(The parallel transport ends at the point where you glued the shape together; if you try to follow the path through that point the vectors represented by the red arrows will appear to suddenly turn through an angle $\alpha$.)
In your last figure, the movement from $A$ to $N$ is along a great circle, and so is the movement from $N$ to $B.$
A great circle is a straight path in the two-dimensional space of the surface of the sphere, that is, it is the path you will follow if you do not turn to either the left or right.
Since you do not turn while you follow a great circle, if you keep holding the javelin in the same direction relative to your direction of travel, the javelin will not turn either. This is the same effect you get on a flat plane when you travel in a straight line across a field of parallel vectors.
An important difference between a flat plane and the surface of a sphere, however, is that there is no uniform parallel field of vectors on the surface of the sphere. You demonstrate this by going from $A$ to $N$ to $B$ and back to $A$; while the javelin did not turn while you traveled along any part of that path, it is not pointing the same way when you return to $A$ as when you left $A.$ That sort of thing could never happen in a flat plane.
When you travel along a small circle as shown in figure (a), you are turning, not going straight. (If you were going straight you would follow a great circle instead.)
If the javelin does not turn, then its angle relative to your path will change at the same rate as the direction of your path changes, that is, as fast as you turn. Again, that is the same local phenomenon you see if you follow a curved path in a flat plane.
If you were traveling along a very small small circle, that is, if the radius of the circle were negligible in comparison to the radius of the sphere, the parallel transport of a vector would look almost the same as it does in a flat plane and would be very easy to visualize. If the javelin was pointing straight ahead when you started, when you are halfway around the circle it would be pointing opposite your direction of travel.
Now consider this: for very small small circles, your rate of turn is almost exactly the same as the rate at which you complete the circuit around the circle, that is, approximately $360$ degrees turn for one time around the circle,
which is why the parallel transport looks so much like it does around a circle in a flat plane.
For a great circle, your rate of turn is zero.
For a small circle whose radius is too large to be a "very small" small circle and to small to be a great circle, your rate of turn will be less than $360$ degrees for each time around the circle, but greater than zero. And that rate determines how much the javelin will deflect from your path if you walk your turning path while not allowing the javelin to turn.
Best Answer
This isn't a direct answer to your question about the parallel transport equations, but the standard geometric solution. Assuming a unit sphere, parallel transport around the latitude $L_{\varphi}$ making angle $\varphi$ with the equator can be found by constructing the cone tangent to the sphere along $L_{\varphi}$, then cutting the cone along a generator, rolling it flat, and performing parallel transport of a tangent vector around an arc of a circle.
Elementary geometry shows that the unrolled cone is a sector of a disk of radius $\cot\varphi$ whose central angle is $2\pi\sin\varphi$. Parallel transport around $L_{\varphi}$ in the northern hemisphere rotates a vector clockwise (looking "down" at the tangent plane, toward the center of the sphere) by an angle $2\pi\sin\varphi$. The animation is a full-circle pan around the sphere.