One rather fundamental requirement for a good loss condition is that the game accurately tells you *that* you lost, and preferably gives you some information of *why* you lost. Games with a long campaign of many battles like XCOM or Heroes of Might & Magic even manage to fail the former sometimes: Either a decision you made that leads to inevitable doom will not give you any feedback at all, or worse you win a Pyrrhic victory and the game tells you that you won, while in reality you just sealed your loss.
One eternal problem with losing is how much it depends on randomness. I do think that randomness has its place in these games, and XCOM does it well by telling you exactly what your chances are for a shot. But an 80% chance isn't the same as certainty, and losing a soldier because you missed a shot with 80% chance can feel somewhat frustrating. But I do think that is more a problem of players not being good at risk assessment, and it gets better with practice.
If losing means having to replay part of the game, the obvious question is how much of the game. Console games frequently have "save points" visible or implicit, and a loss sends you back to the start of the level. As long as the level isn't too long, and you don't need to replay it 20 times before getting some jump pixel-perfect, that works quite well as a consequence of loss. Games like XCOM have loss consequences that are somewhat less transparent: If you lose a soldier, you need to recruit a rookie and train him back to that level. As the game doesn't necessarily give you the time to do that, you might actually lose the game while replenishing your losses, and then the penalty becomes a much harsher one of having to start the game over from the very beginning.
In many situations in XCOM, if you are reasonably clever you will know why a soldier of yours just died. He moved forward to quickly or otherwise exposed himself unwisely, most of the time. But there are situations where your soldier died because you come for the first time in contact with a new element or scripted part of the game. Traps you can easily circumvent the second time you meet them, but that are quite deadly the first time you see them, are rather bad loss conditions. They make people look up solutions on the internet instead of having fun trying things out. In a tactical game like XCOM you can learn a lot of general tactics by trying out, for example how close together you should keep your soldiers, or how fast they should advance. If that trial and error results in a minor wound, you learned your lesson. If it is insta-death and the error wasn't even obvious, that only gets frustrating.
The weirdest thing about XCOM and many other PC games is that you can frequently choose the consequence of losing. You aren't forced to accept the loss consequence the game just handed to you, but can opt for a different one instead by simply reloading a saved game from minutes ago. That leads to some perfectionists saving before every action and only accepting perfect outcomes, using the load function every time something didn't work perfectly. In some game the random number generator even gives different outcomes every time you reload, so you can reload until every attack is a perfect hit. Unlimited save options thus can completely negate the designed loss consequences, and make a game rather boring. XCOM fortunately has the ironman option to disable saving and reloading. I once recommended playing XCOM games on easy plus ironman, and I still think that is a good option to learn about consequences in the game. But the option most veterans will be use is an intelligent management of save game files to suit loss consequences to your personal preferences.