Can we create database transaction and commit/rollback later. I mean we do not committing/rollback at the same machine/host/server. Let say we return the transaction and let other to decide to commit or rollback based on transaction ID. How we do it in Go and sql library?
Advertisement
Answer
No.
A transaction allows for doing a series of commands atomically e.g., without another command getting data that’s half updated, and without another command changing the underlying data within the series of commands.
It is something you want to be over and done with quickly because they lock underlying tables.
Imagine if your transaction was to insert a row into Table A. You start transaction, insert the row, then don’t commit or rollback. Nobody else can use Table A until you have done so (except in particular circumstances). They will sit there waiting (blocked). You could also get deadlocks if concurrent transactions try to put data into tables in different orders – in which transactions are automatically rolled back without user input.
There’s a great video by Brent Ozar explaining and showing deadlocks – worth watching on its own, but also demonstrates what happens if you don’t commit transactions.
If you want a queuing or approving mechanism for changes, you’ll need to build that inherently e.g.,
- Putting changes into a ‘queue’ to be done later, or
- Doing the data changes but flagging them as ‘draft’ in a column in relevant table(s). The rest of your code then has to include whether they want to include draft data or not.
tl;dr version: Transactions in databases are a data-level feature to ensure your data is consistent. Using approval/etc is at the business logic level.