While the specifics can vary from airline to airline, here’s a very general overview.
As a pilot you’ll have the ability to fly on any of your company’s flights free of charge. In addition all airlines will extend those privileges to your spouse or significant other, your parents and your children to a certain age. We also get Buddy Passes for others but the priority is lower and they’re not free. As you point out that’s all subject to availability. As to how often you can or cannot find a seat there are a plethora of variables. If say you want to visit your grandma in Boise in February you probably won’t have an issue. If however you’re trying to get to Fort Lauderdale over spring break things get a bit more challenging. Holidays can be rough and certain destinations are always a problem. Your priority is based on your seniority so as you gain seniority the higher you get on the list and the easier things become.
Now as pilots we also have the ability to JumpSeat. This is a great option as it’s only available to pilots and even if the entire cabin is full, you can often get the cockpit jumpseat when others can’t. The caveats are it’s just for you (so you may find yourself with some angry friends as you get home and they don’t), the jumpseat in certain aircraft is not necessarily that comfy (so if it’s a long flight you may get home but look like a pretzel) and lastly again it goes by seniority so if there’s another pilot who’s senior the seat is theirs.
The short answer to you question is I’ve never not been able to get where I’m going. It may take some creativity (reroute, multiple legs, a much longer day than you planned) but there’s almost always a way. It’s really also somewhat a matter of your (or your family’s) personality. Some actually enjoy the “adventure” of it, others do not. My daughter for example is a rockstar at pass travel and the only person I’ve given my log in to. She’ll tell me I want to go to XYZ and I’ll say go crazy. If it takes her 5 flights she doesn’t care. On the flip side a good friend of mine recently broke up with his girlfriend. When I asked what happened (they seemed happy) it was literally about pass travel. He loves to travel and if he’s not working he’s flying off somewhere. He too loves the adventure of it but apparently she did not. She insisted they start buying tickets and he absolutely refused. She said she’d be happy to pay, he tried to explain its not the money. She agreed it’s not about the money, it’s the uncertainty she hates. Yada, yada, yada, irreconcilable differences and they were done.