The moment the game ends due to the death of the character. In some games there may be an option to return to a point in the game before death, so the obstacle may be attempted again. However, in most cases, it is up to the player to return to a 'save game' point and try again from there.
In Massively Multiplayer Online Role-Playing Games (MMORPGs), although players may die, there is never any 'game over' screen, the character merely continues with some sort of penalty; for example, a loss in experience points (XP).
In adventure games such as the Broken Sword series, the player cannot die and so there is no game over; only frustration as the player tries to solve puzzles or figure out what to do next.