Transaction Control Statement

hi,

I have a question regarding MySQL.

As how to perform transaction control statements in MySQL command line client?
Can you guide me step by step through the process.

Thanks in advance,
Palak

Hi Palak,

MySQL, like any other RDBMS executes one ANSI SQL statement at a time.

In Transaction Control, a set of MySQL statement must be executed together such that all SQL statements execute successfully.

Even if one SQL statement in the block of SQL statement being executed fails, then all the SQL statements within the SQL statement block must be Rolled Back together and the database tables returned to their state prior the SQL transaction being executed.

To force MySQL to execute a block of SQL statements as a single transaction, simply enclose the block of SQL statement with a:

Begin Transaction
SQL statement 1
SQL statement 2
SQL statement 3
End Transaction

When the MySQL engine encounters the Begin Transaction and End Transaction, keywords, the MySQL DB engine recognizes that the block of SQL statements contained within have to be treated as one homogeneous block.

Regards,
Prerna

Hi Palak,

When working at the MySQL using a command window, it would be pretty tricky to create a block of ANSI SQL sentences, enclosed within a Begin Transaction and End Transaction keywords and fire this to the MySQL DB engine.

A simple solution to your needs would be to write the ANSI SQL commands using an ASCII editor (Notepad is great), then save this as <appropriatefilename>.sql somewhere in your computer. For example your desktop, would work just fine.

Then in a command window, when you are at the command prompt simply point to <appropriatefilename>.sql and execute it.

That should the job just fine.

Hope this helps.