People don't move because there's a tax hike proportionate to how long you've lived there, so there's less turnover in housing, meaning less supply, meaning higher prices.
It puts a hard, long term, effectively permanent cap on tax growth, so cities must plan for everything to continually get leaner. Cities make land use and zoning decisions based on uses that are net contributors to revenue, vs net costs. What is a net cost? Housing in general, especially housing that might contain school aged children - that's why 3+ bedroom apartments are so much more rare. What are net contributors? Offices and retail. So the places with demand get to pick, and SF and the Peninsula restrict housing as much as they can and have a huge concentration of offices.
Places on the outskirts (e.g. Dublin, Livermore, Gilbert, Brentwood) are allowing housing to be built because they're trying to turn nothing into something, but any established, and especially built-out city, has a strong financial incentive not to admit a single additional resident to live there, while encouraging everyone they can to shop and work there.