It has ZERO effect on anyone outside of gay people
I don't know about the states, but in the UK there are financial benefits the government offers to those who are married. If more people are elligible for marriage and those financial benefits are bestowed it costs the government. In the states the government are the people (constitutionally). Either way, it costs the taxpayers to fund the tax benefits that married couples get. Thus, gay marriages being respected by the same legals status as straight marriage would probably be followed by tax increases.
People get upset when taxes go up. People get even more upset when its because the government is pandering to a minority they don't like.
Also: fraudulent marriage potential increases, costing even more.
Marriage is a very personal thing.
If marriage was just a personal thing, nobody would care. However, since the government gives benefits to married couples at the expense of the American people, then who gets to be married is a contentious issue that affects everybody (that pays taxes or relies on those that pay taxes).