The bike stays upright the same way a ruler balances vertically on your finger. You have to move your finger around to keep the balance point under the center of gravity. Same for a bike. You have to steer the front wheel in minute amounts to keep the balance point of the bike under the center of gravity of the bike and rider. If you want to test this theory, overtighten your headset next time you are out on your bike so the fork doesn't turn freely. It feels like an invisible hand is trying to tip you over. If you actually immobilize your headset, you will not be able to balance at all.
Even with counter rotating wheels to offset the angular motion, Gyro effect still applies, because the gyro effect works regardless of spin direction and counter rotation doesn't affect that.
This is wrong. Two giros rotating in opposite directions in the same plane cancel each other out. The gyroscopic effect is just a natural outcome of F=ma. If you take the two giros as a pair, all the accelerations cancel out, so no gyroscopic effect.
__________________
Cat 2 Track, Cat 3 Road.
"If you’re new enough [to racing] that you would ask such question, then i would hazard a guess that if you just made up a workout that sounded hard to do, and did it, you’d probably get faster." --
the tiniest sprinter