Oh okay. That sounds a lot like OCC in terms of the tradeoffs involved. If you're looking for implementation hints, you might want to check out Silo (
http://db.csail.mit.edu/pubs/silo.pdf). It's
really hard to beat for transactions that are mostly nonconflicting (if transactions
are mostly conflicting it is hard to exploit multiple writers unless you are very clever about how you go about it; I can dredge up a few links if you're interested). My overall understanding is that the usual rule is that unless you have highly partitionable workloads, single writer almost always wins, but I think your current strategy is also going to suffer from a lot of contention even in cases with no conflicts: the write batch is going to be constantly bouncing between cores and require lots of locking, which could easily substantially degrade performance over what it currently is (though obviously, benchmark :P).
From a correctness perspective: SQLITE_CONSTRAINT will only detect write conflicts, not read conflicts, so you are losing serializability (instead you have snapshot isolation, which is what SQLite gives you by default with multiple writers). SI allows a variety of subtle anomalies that can screw you over; see https://wiki.postgresql.org/wiki/SSI and https://wiki.postgresql.org/wiki/Serializable. If you are currently using only a single-writer thread, you will never experience these issues, AFAIK, because you cannot form a "dangerous structure" (you can never have two R-W dependencies that conflict with each other because you can only have one R-W transaction at a time), but I could be wrong about that; however, if I'm not, this could lead to you dealing with some really bad bugs. Note that these kinds of anomalies have a tendency to pop up when you're using stored procedures to perform validation!
From a correctness perspective: SQLITE_CONSTRAINT will only detect write conflicts, not read conflicts, so you are losing serializability (instead you have snapshot isolation, which is what SQLite gives you by default with multiple writers). SI allows a variety of subtle anomalies that can screw you over; see https://wiki.postgresql.org/wiki/SSI and https://wiki.postgresql.org/wiki/Serializable. If you are currently using only a single-writer thread, you will never experience these issues, AFAIK, because you cannot form a "dangerous structure" (you can never have two R-W dependencies that conflict with each other because you can only have one R-W transaction at a time), but I could be wrong about that; however, if I'm not, this could lead to you dealing with some really bad bugs. Note that these kinds of anomalies have a tendency to pop up when you're using stored procedures to perform validation!