wrap DB operations in transactions

[sydneyli]

No description provided.

[TheNavigat]

I'm interested in this project, this looks simple enough to start with. Would that be alright?

[sydneyli]

Hey @TheNavigat, sorry for my slow response here, and for the lack of context that exists on this bug! There are some refactoring PRs still in the pipeline that might change the nature of this issue, but here's the current state of things to provide some context.

Since we don't pass transactions back to the database, we're concerned about this function where we perform an upsert spread across several DB queries. The nature of the problem might look different with this refactor, but the issue will still exist in some way or another.

So somewhere to start, without colliding with the current changes that are in the pipeline, might be to push through a transaction object into some of the db/sqldb.go functions. Then once #245 lands, we can use the new transactions API in order to fix the race.

Another thing that we've discussed in the past is moving from database/sql to something like sqlx or a more opinionated ORM like gorm, but our queries are pretty simple right now so we scrapped the idea. Something to look into is whether migrating to one of these might make the transaction migration easier as well?

@vbrown608 Do you have any thoughts on this?

[TheNavigat]

No worries, I am not the fastest either.

This is becoming a bit tricky for me. I don't get how this doesn't solve the problem, unless the problem has actually been fixed for this specific function, but not the other ones. In that situation, this makes sense.

Can you elaborate further on how such a transaction object would work? I'm don't fully understand how SQL operations are executed in this context. Apart from the Redeem function, what would be an example of a function that performs sequential write operations that we want to group into a transaction?