I like using DOs for transactions more simply because you control the thread of processing. Run a bunch of SQL in synchronous code with the right error handling in place and you have transactions.
Another approach I use when coordinating changes async across D1/DO other resources in a saga pattern.