Lock picking is not something to do without preparation, and it's always something risky to do as the game is designed not to make things simple here.
Each container has two randomly determined values: Lock Strength and Security Rating. The first one determines how hard it will be to pick the lock itself. The second is how strong your chance of being detected if you fail to unlock the container. The container's location has a large influence on the difficulty: A safe in a grocery is much easier to open than one in a hospital or general store.
To do well 2 skills and 2 statistics need to be good. Your Lockpick skill has a large influence on your result. The Perception and Agility statistics are used as modifiers. If you fail your attempt your Stealth skill will be used to determine your chance at evading detection.
Some stores sell tools to help you in your task. Lockpicks are skill boosting items. When using one, your Lockpick skill will increase for 2 turns (you can open your Player Info Window to monitor the effect). Use those turns to pick the lock.
First, your Lockpick skill modified by both your Agility and Perception is compared to the strength of the lock. I won't give the exact formula, but know that even if maxing all your relevant stats and skills will grant you the ability to open the weak to medium locks without issues, the strong ones will still have a fair chance of resisting you for a while. Secondly, if you fail to pick the lock, a formula comparing your Stealth skill to the lock' security rating will be made. If you fail the roll your alert level will rise, otherwise you'll stay undetected.
Successful attempts will raise your Lockpick skill by 5 points, failed attempt will only provide a single point.
Before any attempt, you should give a look at the safe. By examining it, you'll get your chance of success and your chance of being detected in case of failure. An impossible lock will never open until you raise your LockPick skill high enough. Medium ones have a 50% chance, and trivial ones will always open.
If your skill and stats are too low, you should consider using a lockpick and maybe a stat boosting item before proceeding.
Unlocked containers will stay as such as long as a NPC doesn't come to close it.