Yes, they're very bad when it comes to working out when to accept peace and when to go to war, but then this was equally true of Civ IV and prior games. Those games got around the problem to some degree by making war the endpoint of a "sliding scale" - a civ would only declare war if it hated you enough; however if it hated you enough it would still go to war whether that was in its interests or not. Civ V wars tend to start over specific objectives (mainly "I want that land") and a civ can loathe you without going to war if it knows it can't achieve that objective.
Unfortunately, it's not very good at estimating its ability to achieve its goals or realising when they can't be met.
I've not played Civ 4, only the original MS-DOS Civ, Civ V Vanilla and G&K, but it sounds like Civ V could do with a dose of this treatment.
In three quarters of the games I eventually get backstabbed by a former ally, despite having given them zero reasons to hate me. The arrival of their Unique Unit makes them think they have an opportunity to steal some of my land, and opportunity wins out over principle every time. I can deal with these attacks, and usually profit from them, but such faithlessness from EVERY SINGLE leader is quite depressing. Also, having done it once, they will keep backstabbing over and over - the only way to stop this harassment is to cripple them by capturing their strongest cities - then they will hate you for the rest of the game for being a warmonger, taking their capitol etc. - but will be too weak to do anything other than denounce.
I agree that having some "black hat" villans and bad boys makes the game more exciting, but every leader should not behave like this.
An example of my games -
I play peacefully looking for a science win. I meet one of my neighbours, they are friendly, there are no negative modifiers. I do not settle within 8 tiles of any of their cities. I offer them open borders, defensive pacts, give them unused lux and resources, i loan them money when needed to keep up a steady stream of research agreements. I friend their friends, denounce their enemies and declare war on Civs they are fighting, using my small but well-teched army to help their campaigns. When they ask for help in the way of resources or cash, i provide it. Yet despite having 6 or so positive modifiers and no negatives, the usual result is backstab. It ends badly for them, but that's beside the point. I'd like the game to recognise that degree of loyalty. The AI doesn't even stand to gain anything by attacking me - want my Aluminum? All they had to do was ask for it.
In the 20th century at least, such disloyal behaviour was rare. Yes, Hitler's invasion of the Soviet Union was a Backstab, because they were formally "allies", since the Molotov-Ribbentrop pact. However, you have to bear in mind
a) Stalin and Hitler were the two biggest douches the world has ever seen. In Civ terms, this is like Atilla and Dido being allied. You expect one to betray the other very soon.
b) Hitler covets the other's land, and they have a contested border
c) Incompatible ideologies
d) Have been to war in the past
e) only a few turns ago were denouncing and plotting against one another.
It should have surprised no-one.
What i experience in Civ 5 games however, is more akin to
a) At the end of WW II, America nuking London and invading the UK as soon as Germany surrenders. Because they have a larger army than us, the British Forces are overseas, therefore there is nothing to stop them.
b) Alternatively, the UK detonating a nuke over Cape Canaveral just as Apollo 11 is about to launch, with more nukes over Washington DC and NYC because we're upset about them getting ahead technologically.