How can I ensure a series data modify operation all performed?

Question

I have a series data modify operation to do,such as

 1. update table_a set value=1 where id=1 
 2. update table_b set value=2 where id=1 
 3. update table_c set value=3 where id=1

and I want to ensure this three operation must all complete,I know using transaction can guarantee all performed or none performed.But my point is must make these three all performed.when first sql performed,the app instance may crashed and the other two are missed.

Note this is a ditributed enviroment,may be another app instance can take over the unfinished SQL,but how can I do it?

Can I use a stored procedure,the app instance only fire the stored procedure,and database finsh all the sql?

If when performing transaction,the app instance suddenly crashes,will it leads to a dead lock?

score 0 · Answer 1 · answered May 07 '20 at 14:21

Deadlocks are not crashed requests which fail before the end of the execution. If your request crashes into a transaction, it won't lead to a deadlock.

It is always better to use stored procedures but this won't help you for this specific case.

What I would suggest is inded the use of a transaction with a try catch to rollback the transaction in case of failure. Something like that :

BEGIN TRY -- start of try
BEGIN TRANSACTION; -- start of transaction
    update table_a set value=1 where id=1 
    update table_b set value=2 where id=1
    update table_c set value=3 where id=1
COMMIT TRANSACTION; -- everything went ok we commit
BEGIN CATCH -- an error happened we rollback
    PRINT  N'Unexpected error';
    ROLLBACK TRANSACTION;
END CATCH

You can check more complete examples here

I mean when performing a series of sql,the app instance crashes and not encounter a exception. — ChenZhou, May 08 '20 at 02:59

O. Jones · Answer 2 · 2020-05-07T14:28:30.087

If an app is performing a transaction on a database server, and the app crashes (abruptly disconnects from the database) before committing the transaction, the database server rolls back the transaction. The disconnection does not leave the database in an unusable (potentially deadlocked) state.

So your database contents won't reflect any of your three UPDATE operations when your app crashes during your transaction. It will just lose the transaction in progress.

How to handle this potential failure mode?

Reduce the probability of a crash during a transaction. Try to avoid doing stuff in your app that could make it crash while your transaction is in process. For example, if you get data from some other server or device, get it all before you begin your transaction. This solution is usually good enough for production apps.
Rig up some sort of way for your app, upon restarting, to find out the most recent successful transaction. One good way? Add a column like this to one of your tables: (this is a MySQL thing.)

last_update_timestamp TIMESTAMP DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

This causes every UPDATE operation on each column to -- automatically -- put NOW() into the last_update_timestamp column. Then, when your crashed app restarts you can do

SELECT MAX(last_update_timestamp) FROM table

and you'll know when the most recent successful update occurred. This automatic update also gets rolled back if a transaction is rolled back. If you know when the last successful update occurred, your app may be able to redo the one that was rolled back by the crash.

If you choose to build a redo-transaction capability, be sure to build it so you can test it! if (testingAppCrash) crashNow = 1 / 0; might do the trick in your app.

How can I ensure a series data modify operation all performed?

2 Answers2