NoSQL DBs and 2-way synchronization

As far as I know there are no "nosql" databases that promise ACID transactions, so for banking purposes they are a non starter. Referential consistency support is not usually in their key feature sets either.

mySQL claims ACID transactions when using innodb tables, but I believe there are some caveats around that which may be show stoppers (any mix of other table types, including in-memory tables which are sometimes used for intermediate results in complex logic, will break ACID compliance for instance).

If you are looking at these options because they are free, then consider postgres which does offer ACID transactions, even including transactions that make schema changes. Please consider your support SLAs though: if this is a system that needs high availability (and the words "realtime" and "banking" suggest it is) then I recommend having a support contract in case something beyond your understanding or access to fix goes wrong. This will be rather expensive and once you are talking about that sort of money you might as well at least consider commercial database servers too.

One final point not relating to any particular database: make sure you have the right options selected for full ACID behaviour. A lot of systems default to settings that bend the isolation part, because guaranteeing complete isolation often requires serialisation which can significantly impact the performance of concurrent operations. Whatever DBMS you chose make sure that you understand its options for isolation/serialisation (like "SET TRANSACTION ISOLATION LEVEL" and related directives in MSSQL) and how they interact with each other.

My recommendation would be for you to check out Cassandra. In my experience, and based on the requirements you seem to have, it seems like it would be a good fit.

It appears to me that you have essentially have these requirements:

(1) Key-value storage (look up data based on ID)

(2) No need for JOIN or sub-SELECT

(3) Need for a SQL-like language

(4) Horizontal scalability

As for (1), Cassandra will hash the primary key (in your case you can use a single column id as your primary key) and which node in the cluster stores the data, and the Cassandra internals ensure look up by primary key is very fast. Cassandra doesn't support JOIN as in (2), but you appear to be OK with that.

Since you are essentially doing key-value lookups, I'm actually not sure why you need an SQL language. Perhaps you are wanting to not only look up data by primary key, but also perform aggregations and use GROUP BY? In this case, Cassandra won't work, but if you only need a simple SQL language to retrieve data as per (3), Cassandra has CQL, which may serve your needs.

Horizontal scalability is built right in to Cassandra, covering (4).

All that being said, Cassandra isn't a silver bullet. It's a great technology for certain applications, and it sounds like it fits you needs. Still, if you want aggregations, or if you want to query for multiple primary keys in a single query (e.g. SELECT * FROM table WHERE id IN (...)), then you may run in to some issues.

Now, if you can let go of constraint (4), I'd 100% be promoting PostgreSQL. You stated the database will be getting hit with thousands of requests per hour, but that's nothing for a database like PostgreSQL, if your queries are only individual primary key lookups, and you aren't trying to transfer large amounts of data in those queries.

Best of luck! If you provide more info based on my feedback, perhaps the community here can help you narrow down your selection even more.