I'm confused. Are we designing accounting software or high concurrency? Upsert is not magic, there are always trade offs. If this is accounting software then u

I'm confused. Are we designing accounting software or high concurrency? Upsert is not magic, there are always trade offs. If this is accounting software then use transactions. If you're doing a research paper on accounting software then have fun, there are lots of trade offs to play with. If you are designing for concurrency then drop your constraints and transactions and just make everything an insert. Then when you want to select just look for the first record from the bottom of the table. No feature, be it upsert or whatever, can ever beat proper design. The best it can ever aspire to do is equal it.