CODEWITHSUNDEEP
c#mysqltransactionscommitrollback
Hikaros
Hikaros

Reputation: 121

What happens if there is a power outage before calling commit or rollback?

So I've been testing with this code: 

MySqlTransaction trans = null;
                    var con = new MySqlConnection(DBInfo.CON_STRING);
                    try
                    {

                            con.Open();
                            trans = con.BeginTransaction();

                            var query = "INSERT INTO test (num) VALUE (@val1)";
                            var cmd = new MySqlCommand(query, con);
                            cmd.Transaction = trans;
                            cmd.Prepare();

                            cmd.Parameters.AddWithValue("@val1", 0);

                            for (int i = 0; i <= 15; i++)
                            {

                                    cmd.Parameters["@val1"].Value = i;
                                    cmd.ExecuteNonQuery();
                                    if (i == 10)
                                    {
                                            //throw new DivideByZeroException();
                                            MessageBox.Show("pause");
                                    }
                            }

                            trans.Commit();
                    }
                    catch (Exception ex)
                    {

                            MessageBox.Show(ex.StackTrace);

                            try
                            {
                                if (trans != null) trans.Rollback();
                            }
                            catch (Exception ex1)
                            {
                                    MessageBox.Show(ex1.Message);
                            }
                    }
                    finally
                    {
                        con.Close();
                    }

The commit and rollback method. And i have a question. In the for block i have a MessageBox to pause the execution of the code so i can stop the program to simulate a sudden execution of the program. Thing is that the query didn't commit but what happened to the server then? Does it rollback automatically? and what happens if for example the PC where the program is running happens to be the server and there is a power outage? (let say the power outage happened in the middle of the execution of the for block.

Upvotes: 2

Views: 4067

Answers (3)

Fraser Orr
Fraser Orr

Reputation: 425

When you start a transaction what happens is that the database commands don't actually change the database itself, rather then maintain a list of changes that the user requested. There is a lot of mechanics to make this work apparently transparently, but the changes are stored in a log. When you do a commit it writes all those transactions in one block the toe database, so all the changes are made at the same time. If you do a rollback, or if you do nothing (as would be the case with a power outage) then the transaction log is deleted, as if you had never executed the commands in the first place.

What is that holding off in a log, and atomic committment (meaning they all get saved in one shot with no-one, not another thread or another process intervening) can lead to some odd things.

For example, if you have two connections to the database and you do the following:

  1. Open two separate connections, connection1 and connection2
  2. Begin a transaction on connection 1
  3. Insert data row R in to table T using connection 1
  4. Using connection 2, look in table T, and you will not find row R
  5. Commit transaction on connection1
  6. Now using connection 2, look in table T, and you will now find row R.

This is absolutely correct behavior. At point 4 the data is still in connection1's transaction log, and has not been written to the database table, so it is not visible on connection 2. Only when connection1's transaction log is committed to the database, is it visible on connection 2.

Upvotes: 0

Devarsh Desai
Devarsh Desai

Reputation: 6112

This is a really good question. What happens with MySQL is that it's ACID compliant. The literal use case for ACID compliance is "what happens if we pull the plug during a transaction". Essentially what it boils down to is that every operation is atomic, meaning that it either completes or doesn't. If it doesn't then the state of your database before that transaction is guaranteed to be saved and you can resume there. If it completes, then you're ensured that if your next operation fails, your current state will be saved.

fun fact: One of the main caveats to the NoSQL database, MongoDB, is that it's not ACID compliant. So if you have a transaction that's updating multiple set's and you pull the power outlet, then the state of your database is non-deterministic (something might have saved, some others might have not).

Please let me know if you have any questions!

Upvotes: 4

Thilo
Thilo

Reputation: 262684

When a database server restarts after a power outages, it reverts back to a clean state that contains only committed work.

When a database client gets cut off in the middle of a transaction, the server will clean up the transaction automatically by rolling it back.

Upvotes: 4

Related Questions