It's quite nuanced. In a nutshell the unlock just flips the 2 bits that halves how often the core can issue the specific instructions Nvidia locks to full rate on the professional and datacenter cards.

Obviously doing more compute requires more power but what it does is shift the card down on the frequency curve to keep the wattage in check. So you're right that at stock power limits it hits a cap where the full issuing rate isn't reached but it depends heavily on the exact workload how much juice is left.

The frequency curve also takes heat into account so in general you end up with a nice perf boost for a slightly hotter card.

I've been running this on my 2 3090s for a month now with no issues but they are also water cooled :-)

Ah, that's a twist I didn't see coming, all my GPUs are aircooled. I can see how with watercooling you'd be able to get more performance out of the GPU before hitting the limits.

There 3090 is from the era of overclocking, anything but the reference design should be fine even on air cooling (especially if you have a 3 connector model)

My EVGA came with stock firmware that started at a 420W and allowed up 500W, of course 500W did nothing for gaming