How to make operation atomic while inserting in mysql using perl script

Question

I have a perl script that places order for customer.

2 tables.

table_orders - orderic, customerid, orderdatetime
table_order_details - orderid, productname, Quantity, MRP. Sellprice

Steps to insert into table:

1. insert in table_orders.
2. for each product in shopping cart insert into table_order_details

Now problem is after insert in first table,

1. It is possible because of some error in input data insertion in second table is not possible and perl returns an error.
2. Few entries done in table 2 too, but now got the error so further entry not possible in table 2 and perl returns an error.

In both the cases transaction is not completed successfully, so how to revert back the entries in DB that was done before error occurred?

Is there any perl or MYSQl wat to do this or we need to handle it manually??

score 3 · Accepted Answer · edited May 23 '17 at 11:58

Use transactions.

Transactions allow you to isolate changes to the database until you're done making all the changes. Nobody else can see those changes and nobody else can change those rows while you're working in your transaction. Then you commit the transaction and everyone else can see your changes all at once. If there's an error, you can rollback all the changes you made in your transaction.

In Perl, using DBI, the default is to not use transactions. Every statement is automatically committed. It is a very good idea to always turn transactions on. You typically do this when connecting to the database by setting AutoCommit off. You'll also want to turn on RaiseError so database errors will happen automatically, you don't have to write or die... everywhere. Then, since you don't want to see errors twice, turn PrintError off.

my $dbh = DBI->connect(
    'dbi:mysql:...', $user, $pass,
    { AutoCommit => 0, RaiseError => 1, PrintError => 0 }
);

Now everything you do will be in a transaction and nothing you do will be visible to anyone else until you run $dbh->commit. Wrap all the work in an eval block to catch any errors thrown now that RaiseError is on. If you get an error, $dbh->rollback will throw out any partial work.

eval {
    my $insert_table_orders_sth = $dbh->prepare(
        "INSERT INTO table_orders (...) VALUES (?, ?, ?)"
    );
    $insert_table_orders_sth->execute(@table_orders_values);

    my $insert_table_orders_details_sth = $dbh->prepare(
        "INSERT INTO table_orders_details (...) VALUES (?, ?, ?)"
    );
    for my $product ($cart->products) {
        $insert_table_orders_details_sth->execute($product->values);
    }

    # All done, make the changes visible to everyone else.
    $dbh->commit;
};
if($@) {
    warn "There was a database error: $@";

    # Wipe out anything you did above.
    eval { $dbh->rollback };

    # Do whatever else you might need to cleanup the error.
}

Tip: If you use `RaiseError => 1`, you probably want `PrintError => 0` too to avoid seeing every message twice. — ikegami, Oct 09 '15 at 06:13
Since the OP used the MySQL tag: MyISAM, MySQL's default storage engine prior to v5.5, doesn't support transactions; even with AutoCommit disabled, every statement is committed automatically. I think MySQL 5.1 comes with EL6, so this could be a problem for people on older systems. Unfortunately, there would be no indication that transactions aren't being used. — ThisSuitIsBlackNot, Oct 09 '15 at 14:31
(It would also be a problem for anyone who chooses to use MyISAM, regardless of MySQL version.) — ThisSuitIsBlackNot, Oct 09 '15 at 14:46

How to make operation atomic while inserting in mysql using perl script

1 Answers1