sql - How to deal with no-transactional service during transaction? -

sql - How to deal with no-transactional service during transaction? -

suppose have sql-transactional financial system, during transaction calls external non-sql service.

how deal e.g. powerfulness loss when don't have if external phone call successful or not?

let's imagine on sql-database , bitcoin wallet making transaction.

start sql transaction decrase user amount cash send user cash somewhere throught bitcoin wallet commit

the powerfulness loss between step 3 , 4, prevent commiting transaction (none decrase of user cash), sending our money wallet.

second scenerio decrase user cash , commit before calling external service. but, when powerfulness loss occur after external service fail (not actualy sended) money, don't create homecoming user funds after external service fail (suppose bitcoin wallet down).

currently, decrasing money , marking transaction "in progress" (and commiting that) before phone call external service, if external service responds ok, mark successfuly finished.

but, @ powerfulness loss can still remain in situation transaction after powerfulness loss marked "in progress", money decrased, have manualy on bitcoin wallet/blockchain transactions if money sended.

is there improve way manage transactions in situation? possible solve in way without possibilty check in future time if our external phone call successful or not? (like can check past transactions in blockchain)

welcome wonderful world of distributed transactions! after working on them many many years @ various places (ms, google, etc), pat helland came conclusion there is no silver bullet , best can go 'eventual consistency'.

you're making start 'in progress' flag. without knowing details of remote transaction (i.e. steps have go through, info available @ completion of each) can't tell how here's generalities.

if can transaction id remote site before transaction committed, when gat update local db transaction id. when remote transaction completed, update 'complete' flag on local db.

you have number of possible states. 2 need attention:

inprogress set, not completed, no transactionid: remnote transaction needs started , completed inprogress set, not completed, transaction id exists: may committed in remote site; contact check whether transaction has been completed; if not finish (or go first state , whole thing)

that's outline. don't know ins-and-outs of how bitcoin transaction completed, hope gives idea.

sql transactions finance bitcoin distributed-transactions