All of the solutions you describe require special attention from distro maintainers, packagers, or users. In the case of Flatpak, it doesn't really take any extra work in terms of adding extra package definitions or creating new prefixes that coincide— the people packaging the Flatpak (usually the app developers, we expect?) can just modify whatever distro package is used to provide that dependency in-place and ship it out in their Flatpak as if there were no collision problem at all. Similarly, on the user's side, the collision problem you outline is preempted; Flatpak packagers simply can't prepare their dependency chains in a way that will actually conflict with libraries present on end users' systems.

There are some more traditional (in the sense that libraries are always shared by default and no containerization is required for packages to work right) package managers that extend the kind of isolation you describe (‘installing [special or patched versions of dependencies] under a different path’) to all packages, namely Nix and Guix. These package managers also make it easy to do things like take a package definition from somewhere else and rebuild it with just one or two dependencies modified without redefining the whole package, or alternatively take such a package definition (perhaps originally targeted to a different release version of your distro) and rebuild it with all dependencies instead being provided from your current distro. At the same time, since the dependency chains of packages are isolated by default in these systems, it's relatively easy, in case of a special need, to take any package and bundle it and its dependencies into a container for distribution on ‘foreign’ systems. (And for packager convenience, the package collections in these distros may also explicitly include multiple versions of a library in their default package sets in the same way that other distros do, as you describe).

Nix and Guix have their own difficulties and quirks, of course! But one of the things I like about their approach is that their innovation is conservative in a certain sense: it offers more isolation for packages (along with the cross-distro compatibility and potential for bundling that come with that) but preserves the virtues that have been the strength of package management on Linux forever. I would like for whatever really is the future of package management on Linux to share in that.

So I don't think containers are The Way Forward™, although I do think that desktop containers like Flatpak are potentially useful as (1) a step toward more fine-grained permissions for desktop apps in terms of access to files, multimedia devices, and so on; and (2) a way of providing cross-distro packages for applications that (a) have yet to accrue much developer interest from packagers and distro maintainers or (b) are (unfortunately) proprietary.

(Personally, if I really want a piece of software, it's not in the distro I'm currently using, and it doesn't seem especially difficult to package, I will just go ahead and make a package for the distro I'm using. Case (2a) provides a convenient, more managed way for me to evaluate a new program I've heard about without having to package it just to see if I like it.)