Isn't this the same as a message queue? Why would you want to rewrite this using a database? Also point/trick 2 seems unnecessary if you are using 3 (idempotent jobs). By queuing job ids you now have a consistency dependency between your message queue and database.
When using a non database-based queue you'll have to find another mechanism to make sure your operation is still atomic.
In other words you can end up in a situation where a record is inserted but the job to work on it is not enqueued, or worse - that an insert fails but the job to work on it is enqueued.
Point 2 is still necessary despite idempotency imo: lets say some value is updated to "a" and then to "b", enqueuing two jobs. If the request to update "b" runs before "a" then your receiver will end up with the wrong value. Same if the initial request to update "a" fails.
It seems to me that XA transactions one of those patterns that need to be rediscovered every generation. I see folks start with 0mq or Redis and hit edge cases where messages get lost.
I'd love to see a somewhat simple distributed transaction standard for http api's emerge.
There has to be a middle ground between the fiddly bits of soap's ws-reliable or full on JMS broker and ad-hoc Redis queues.
Perhaps I wasn't clear. What I meant was that if you enqueue the job directly and the job is idempotent, using approach 2 will introduce dependencies like the ones you pointed out.
