MySQL Transaction: One Large Transaction against Multiple Small Transactions

Question

The design for the Big Transaction..

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

and the design for the Small Transactions..

START TRANSACTION;
    /*
        INSERT for login detail
    */
COMMIT;

START TRANSACTION;
    /*
        INSERT for personal information
    */
COMMIT;

START TRANSACTION;
    /*
        INSERT for user's transaction account
    */
COMMIT;  

Current results

• I have tried both in our application, and by using the 'Big' transaction we experienced deadlock in a certain table.

• By using the small transactions, there's a chance that one or two of the three may not run and cause discrepancy.

My experience in handling such cases is not sufficient to provide the best solution in this kind of scenario. What kind of solution can be made here?

Answer 1

The point of using transaction is to ensure consistency of the data being stored. When you make a transaction, all the inserts, updates and deletes are not stored on the database right away, the DB locks the tables (or rows depending on the config) with the tentative data until it reaches the commit command. At that point the data is written and the locks are released.

If you make "small" transactions then it's the same as not making transactions at all.

If your "big" transaction is getting stuck, find which table is causing the deadlock, and why is it doing it. There are many reasons including concurrent inserts/updates/deletes on the table, locks not being released on time, previous transactions staying "alive" (i.e. not reaching the commit command), DB taking too much time to store the data on the table, too much time between the inserts, foreign key violation, etc.

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

Answer 2

After some years of experience in my work, I have come up with this solution.. because the data for the affected modules are all intact thru success or failure. Thanks everyone for helping

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