Geek Answers Handbook

MySQL Transaction: One Big Transaction vs. Several Small Transactions

Design for a large transaction.

START TRANSACTION; /* INERT for login detail */ /* INSERT for personal information */ /* INSERT for user transaction account */ COMMIT;

and design for small transactions.

 START TRANSACTION; /* INSERT for login detail */ COMMIT; START TRANSACTION; /* INSERT for personal information */ COMMIT; START TRANSACTION; /* INSERT for user transaction account */ COMMIT;

Current results

  • I tried both in our application and using the transaction "Big", we encountered a dead end in a certain table.

  • Using small transactions, it is likely that one or two of the three may not work and cause a mismatch.

My experience with such cases is insufficient to provide the best solution in this type of scenario. What decision can be made here?

+6
source share
2 answers

After several years of experience in my work, I came up with this solution .. because the data for the affected modules is not corrupted or unsuccessful. Thank you all for your help.

 START TRANSACTION; /* INSERT for login detail */ /* INSERT for personal information */ /* INSERT for user transaction account */ COMMIT;
0
source

The point of using a transaction is to ensure the consistency of stored data. When you make a transaction, all inserts, updates, and deletes are not immediately stored in the database, the database blocks the tables (or rows, depending on the configuration) with preliminary data until it reaches the commit command. At this point, data is written and locks are released.

If you are doing "small" transactions, then this is the same as not doing transactions at all.

If your “big” transaction is stuck, find which table causes the deadlock, and why it does. There are many reasons, including simultaneous inserts / updates / deletions in the table, locks are not released on time, previous transactions remain "alive" (ie, Do not reach the commit command), DB takes too much time to store data in the table, too much time between inserts, foreign key violation, etc.

You can read this article explaining how transactions work and how you can identify and avoid deadlocks http://flylib.com/books/en/1.142.1.79/1/

+2
source

More articles:


All Articles