How to handle transaction process in Mysql database. When my database gets interrupted?

Question

I have executing 10 query in my stored procedure.

In case after executing the 5 Th query my database get interrupted and its stooped the execution.

My first 5 query updated in database.so i have to rollback all the 5 query's execution.

How to solve this kind of problems?

Answer 1

Use InnoDB transactions. START TRANSACTION ... COMMIT. See doc.

Answer 2

The default MySQL table type is MyISAM, which doesn't support transactions. Thankfully there is another table type InnoDB, which does support transactions. Converting the table type is easy enough:

http://dev.mysql.com/doc/refman/5.0/en/converting-tables-to-innodb.html

And when making queries do this:

START TRANSACTION

// do whatever queries you want to

// if there are no errors
COMMIT

// if there are errors
ROLLBACK

After a rollback, your db will return to the state it was at the point you called START TRANSACTION. This tutorial should help some more:

http://www.devshed.com/c/a/MySQL/Using-Transactions-In-MySQL-Part-1/

Note: You can't use FULLTEXT queries with InnoDB tables.