You really won't ever fail to pick a lock (like you might see with breaking bobby pins in Fallout), but the mechanic allows one of three things to occur: you just pick the lock, you pick the lock and set off an alarm, or you pick the lock and take the alarm for yourself as
a noisemaker bolt for the crossbow.