I've tried working out how to move to an event sourcing system, but I always struggle with locking behavior. Do you just have to invent your own locking mechanisms on top of event sourcing?
Locking across streams is an anti-pattern / smell. It can be done (as can anything) but it usually points to a modelling problem. Example: cancelling an amazon order is a _request_ that is in a race with the fulfillment system (boundary); it may or may not be successful.
So, you get to pick, "at most once" or "at least once." And then you need to build your system to act accordingly.
Low-ish volume- design your system such that data flows [at the relevant crucial points] through a single actor to ensure proper concurrency.
High volume- trickier but I think same idea in principle. First thought that comes to mind here is the new GenStage stuff in Elixir.
You may not have to do deal with locks at a local level. You absolutely have to deal with locks at a system level.
If you really must expose an SQL API, perhaps you could read the journal on another thread or process and then make changes to the db based on the incoming "diffs" that the threads determines from the journal?
For something like this my batch processor is implemented as you're probably used to—files get a CRUD model associated with them, schedule a background job to handle it, let locking get handled there. Once inside the batch processor you can use the same domain services and commands that you'd use from your application layer and commit events on a command basis, or on something like a row in the file (which may generate several commands and dozens of events depending on your model), or on a file level (all or nothing.)
The thing I see people do frequently (and sadly, have done myself on occasion!) that makes their lives harder is trying to shoehorn everything into ES without doing the design work to establish a domain, its boundaries, and what events make sense within it.