If you're going to use SQL why would you use such a limited backend with a parity mismatch and a much greater set of limitations in place of many heavily-optimized-over-decades backends?

To gain the scalability/performance of a k/v store while having the capability to do complex queries (e.g. joins, range queries)

I believe you would be much better off with a DSL that mapped to a set of fast operations on a k/v store. SQL databases are heavily heavily optimized, and far more performant than k/v stores for most non-trivial queries. k/v stores don't somehow give magical performance. What they do is limit you to a small set of primitives that helps with scalability and maps more naturally for applications that are document-oriented and don't slice and dice data as much.

K/V stores don't provide ACID properties themselves, but you could bolt them on pretty easily. For instance:

Atomicity: save a journal of the pre and post values of any rows, and on commit take all of your staged changes and write them and then delete the journal. If you're ever going to hit the DB again and you see a journal file that wasn't cleaned up, roll back to whatever pre values there are.

Isolation: use a semaphore to cause the start of a writing transaction to block all reads and writes, use a semaphore to cause the start of a reading transaction to block all writes, and escalate from reading transaction to writing transaction when necessary.

Transactions are actually incredibly tricky to get right, particularly in a portable manner. Not exactly the sort of thing one can implement in a weekend.

For example: http://sqlite.org/atomiccommit.html

Also, it will slow down the database a bit (particularly if added to the db as an afterthought!), which seems to really irritate people who would rather have potentially corrupt information as soon as possible rather than automatic guarantees of internal consistency.

And let's not even start talking about consistent reads.