Backport of #31021.

Closes #30991.

This doesn't "fix" the regression, because I don't think there's a way to do that without introducing new bugs or depending on interpreter internals and/or trying to argue for yet another PyUnstable C API function we can use.

I'm also a little concerned that the old check is actually incorrect for arrays created by C extensions: the check on 3.13 and older allows resizing an array created directly via e.g. PyArray_FromAny with exactly one reference.

IMO it's better to keep the logic of the check the same and simply improve the documentation and error messages.

Now when the reference count is exactly 2, the error message on 3.14 and newer explains that this might be a false positive.

I added tests which trigger the behaviors we expect to happen on different Python versions. I also deleted some out-of-date docs about an unused order argument and added some notes that resizing in-place can lead to memory fragmentation and worse overall memory usage.

Also looking for any ideas for ways to make the error messages and docstrings less wordy.
-->