This isn’t a Unity problem, this is a real time rendering limitation that affects all 3D game engines. Efficient and correct sorting of arbitrary transparent geometry is an unsolved problem for real time rendering.
Opaque or alpha tested shaders usually write to the depth buffer as they render each pixel which allows for proper sorting. If a pixel being rendered is further away than what has already been rendered to that pixel it is skipped and it leaves the pixel color alone. If it’s closer then it replaces the existing color. This is a nice optimization as well since a lot of wasted rendering can be skipped.
For transparent shaders you generally do not write to the depth buffer. Instead you try to render transparent objects presorted to draw furthest to nearest. This is what’s known as the Painter’s Algorithm. The triangles for each mesh are then just rendered in the order they are stored in the mesh. If triangles are drawn in the correct order and aren’t intersecting then you don’t need any additional per pixel sorting. Using the depth buffer only works in the opaque case as you can only completely replace a pixel’s color or blend on top of it. The result is either the depth buffer isn’t needed as it’s already rendering in the proper order, or you get stuff that’s behind showing through on top if no depth buffer (like in your case), or stuff missing because it was depth rejected when using the depth buffer. For anything not fully opaque, the “randomly” completely missing case is generally more objectionable than the out of order case.
There are several solutions with different benefits and draw backs.
Sort per triangle prior to rendering
Some game engines have this feature built in, but it’s rare as it can be quite expensive. You can do this in Unity manually if you want, and this will indeed solve the issue, as long as your triangles don’t intersect. This can be done either from script or more efficiently (and more complicatedly) via a compute shader. This is actually the closer to the approach many games with ocean rendering take, though it’d be more accurate to say they usually generate a whole new mesh every frame based on the camera’s position rather than sort the triangles of an existing mesh. You can also have a single mesh that’s been pre-sorted and moves to keep the mesh’s sorted center under the camera at all times and then reconstruct the UVs in world space in the shader. This is the simplest and most straight forward approach and some form of it is what almost all games end up doing for problematic situations. It doesn’t solve the issue of triangle intersections, but most games don’t bother trying to.
Pre-pass depth write
The short version is you render your mesh once with a special shader pass that only writes to the depth and not to the color. Then render again, but only the color. This “solves” the sorting problem for a single mesh by treating it more like an opaque object and allowing the per-pixel sorting to be effective. However it also means that for overlapping parts of the mesh only the parts closest to the camera are visible. It can also be problematic when rendering other transparent objects in the scene especially when doing this on large objects like planes of water. This can be an attractive solution for many other use cases though.
Order Independent Transparency (OIT)
True OIT is possible, like by using an A-Buffer, Linked List Pixels, or depth peeling, but are quite inefficient in real world use cases. A-Buffer has a significant memory usage impact increasing the frame buffer by the number of per pixel layers allowed. Linked List Pixels build on the A-Buffer by allowing each pixel to only store as much data as needed rather than always have it allocated, but maps poorly to GPU memory usage, and still has potentially the same or higher memory usage as the A-Buffer for complex scenes. Depth peeling has little additional memory usage, but requires rendering the scene multiple times for each layer, and those layers must be determined before rendering. There are also several Approximate OIT techniques like weighted average blending which can be quite convincing for many layers of mostly transparent objects, but falls down when opaque or nearly opaque objects are used. Obviously none of these are built into Unity, though there are freely available implementations of depth peeling and weighted average blending OIT out there if you look for them. However I don’t recommend this option.
Alpha to Coverage
When using MSAA you can use Alpha to Coverage techniques to get sorted transparency, but the technique is quite limited and is more appropriate for other uses. Used naively it behaves similarly to the pre-pass depth write technique on most hardware. More complex setups you can get n layers (depending on the MSAA quality) of transparency that are correctly sorted, but don’t necessarily blend together how you expect and can only be one of a handful of specific opacities. For example when using 4x MSAA you can only have fully opaque, 3/4th opaque, 1/2 opaque, 1/4th opaque, and invisible, and only with the 1/2 and 3/4 opacities can you get any kind of sorting between 4 layers. At 1/4th opaque all layers will always show, or act like the pre-pass depth. Under very specific and well controlled use cases this technique is very powerful, but I don’t recommend it unless you really know what you’re doing.