Operations done through the storage API within the same API call are all done atomically and allow you to use a - read, modify then write - pattern with the help of conditional writes to ensure that a write fails if another happens concurrently in between for the same object (making the read stale).
It is generally best to avoid keeping transactions open unless absolutely necessary, so the API also protects from issues stemming from having many long-running transactions open in a highly concurrent environment at scale.
If you need to atomically update multiple different entities (such as a storage object and a wallet) you can use the runtime multiUpdate function.
Ultimately you can use custom queries if you need to, the *sql.DB handle is exposed in the Go runtime, as well as the ability to run custom queries in the Lua and JS runtimes for this reason, but we discourage it because the storage engine APIs are optimised around performance, and if you write your own queries we cannot give any guarantees. Custom tables however, create a plethora of other issues so we strongly advise against it.