Since buying Rocksmith a few weeks ago, my son and I have both played on it a lot.
I bought this second Real Tone cable so that we can play duets together - it works perfectly.
When you select Multiplayer, each player needs their own Playstation controller to select between Guitar/Bass and a couple of other options - then you're up and running.
The screen shows the note stream (or whatever it's called) for each player, one above the other, and you play along as you would in the one player game. The adaptive difficulty is still running, so a better player will have more to do than a learner, but you're essentially playing along together.
A couple of things to note:
1. On PS3, the note stream animation seems a little choppier in mltiplayer mode.
2. When you're doing multiplayer with a child it's important to stress that you're not trying to *beat* each other's scores - you're trying to improve your own playing - otherwise they can get a little disheartened.