When red wine is specified in a drink recipe, we mean a dry red wine. Sweet red wines will be specified as such.
For most drink recipes you can choose a red wine that doesn't break the bank. Because the wine is going to get mixed with other ingredients it won't be able to show off some of the subtle characteristics that you might be paying $40 a bottle for. But you do want to use a quality red wine - one that you would drink on it's own. Cutting corners with the wine while you are using high end (and expensive) other ingredients will just bring the drink down from where it could be.
It also depends on the proportion of the red wine to the other ingredients in the drink. For a sour drink recipe with a snap (red wine floated on the drink), the red wine is going to be a distinct minority with respect to the other ingredients. You don't need to go nuts here. When the red wine is the base for the drink such as with a cobbler, then you might want to up the quality a little.
I usually am using relatively young, fresh, good quality wines in my cocktails. The nuances of older wines with some age on them usually get lost. Spanish reds offer a particularly good value so I find myself using garnachas often.
Which red wines do you use in your cocktails?