[Q] Could someone explain how a multiple regression "decides" which variable to reduce the significance of when predictors share variance?

I have looked this up online but have struggled to find an answer I can follow comfortably.

Id like to understand better what exactly is happening when you run a multiple regression with an outcome variable (Z) and two predictor variables (X and Y). Say we know that X and Y both correlate with Z when examined in separate Pearson correlations (i.e. to a statistically significant degree, p<0.05). But we also know that X and Y correlate with each other as well. Often in these circumstances we may simultaneously enter X and Y in a regression against Z to see which one drops significance and take some inference from this - Y may remain at p<0.05 but X may now become non-significant.

Mathematically what is happening here? Is the regression model essentially seeing which of X and Y has a stronger association with Z, and then dropping the significance of the lesser-associating variable by a degree that is in proportion to the shared variance between X and Y (this would make some sense in my mind)? Or is something else occuring?

Thanks very much for any replies.