So how does multiplayer play out? Does everyone need to be connected and sit there until someone doesn't die or what?
Can players come and go casually? How long does a typical multiplayer game last?
Multiplayer is literally the same as single player, but with more characters controlled by people, instead of AI. In the single player, you can choose any character to start off as. The same is true of multiplayer. And it lasts just as long as single player does. (typically, a very long time if you are trying to reach the end-date)
As far as competition, the level of animosity is entirely up to the players involved. If you want to ally yourself with another player, you can. If you want to conquer and destroy other players, you can. If you want to conquer another player, and make their dynasty a vassal in your empire, you can.
I was playing a multiplayer game with a friend of mine where I started off as the king of England (which was still a fairly small country at our start-date), and he started off as a noble in Norway. My start character was Catholic while his was pagan. (tribal) At first we had very little interactions, there was a significant body of water between us. My start was rocky, and I quickly got deposed, and eventually ended up clawing desperately just to maintain control over a single county. My friend had a meteoric rise in Norway, and was well on his way to establishing a North-Atlantic empire.
He eventually started invading Scotland and conquering the British Isles. When he started getting closer to England, I turned around, rebelled against the Queen of England, won my independence, and immediately offered to be my friend's vassal. He demanded that I switch religions, but otherwise was willing to take me on as his minion. Once the British Isles were conquered, he put me in charge of them and left their management and suppression up to me while he turned his eyes towards mainland Europe.
Crusader Kings II can be quite complex, but the possible interactions that come out of it are always fascinating.