[Angrybird 12]

Your owners manual explains exactly how this operates. When you shift into M on the console, in the display it will show S at first. This is sport mode and the transmission will hold gears longer and is more aggressive. The first time you touch either paddle it will then be in manual mode and can only be upshifted with the paddle, but will automatically downshift when coming to a stop, on the V8 it will downshift to second and on the V6 it will shift down to first. You can force a downshift with the left paddle unless it would be over the rev limiter when it downshifts, in that case you will get a shift denied message. Also when you are upshifting if it is too low and rpm to shift that it would lug the engine you will not be allowed to shift and you will then get a shift denied message.
On a side note on 2012 (maybe 2011) and later cars you can also push the paddles and force a shift while in D, but this is only temporary and will resort back to auto mode when the computer dances the engine load stabilizing for several seconds.
This does not include the 2LS models though.
To verify what I said just read your owners manual.