Correct, there are no dice rolls; either your level of Lockpick is high enough, or it is not. Regular lockpicks are consumed when used, and later in the game you can acquire magic, unbreakable lockpicks (which have another bonus, see spoiler, in addition to being much lighter than a stack of regular lockpicks). The magic lockpicks are quest related, but it is much, much better to keep them than give them up to complete the quest.
Finnigan's magic lockpicks give a +20 Agility bonus when in the character's inventory.