Efficient and correct sorting of arbitrary transparent geometry is an unsolved problem for real time rendering. Fading complex overlapping geometry is just really, really hard to do correctly.
The short version is real time transparencies are rendered with what is known as the Painter’s Algorithm, which is basically render the things further away first as each object that’s rendered gets drawn “on top” of the previous ones. Like what would happen if you were using stickers, or paint. For separate meshes, real-time renderers will try to sort the meshes by their distance from the camera. But within a single mesh it’s generally too expensive to sort the individual polygons so the mesh’s triangles are rendered in whatever order they appear in the mesh itself.
This is a problem if, like in your case, the “interior” polygons of the mesh show up later in the list than the outside.
Opaques handle this sorting problem by making use of the depth buffer to track the closest surface to be rendered per pixel and not rendering things further away than the last closest surface. That way you don’t need to have perfect sorting before you render. But that doesn’t work for transparent surfaces because since the on screen color isn’t just from one surface, it’s some blend of multiple surfaces. So you either need the triangles to be perfectly sorted before hand, or you’d need to keep a list of every surface color & depth rendered to each pixel and sort them afterwards. This is very expensive, and no one does this outside of tech demos. Even the big movie studios with custom tools don’t do it for their real time previews because it’s just too expensive.
For this particular mesh, you might get something acceptable by reordering the triangles in the mesh. You can do this in most modelling tools by hand by separating parts of the mesh and recombining them in the order you want them drawn in. For a tube like this you might get acceptable results by separating the exterior wall from the interior and then appending the exterior back on.
Other options would be to use a custom depth pre-pass shader, which renders the mesh to the depth buffer before rendering normally. This will let you fade out the mesh more gracefully, but you won’t be able to see the interior.
Beyond that there are various “Order Independent Transparency” approximations, like stochastic alpha testing, or Weighted Blended OIT, or depth peeling. But these are all more complex to setup.
Someday we may finally just be raytracing everything all of the time which would avoid this problem, but we’re not there yet, and probably won’t be even for many more years.