Feeds:
Posts
Comments

Posts Tagged ‘MySQL transactions’

For Newbies, I suggest to go through Different Types of Engine Supported by MySQL

Please Visit:http://articles.techrepublic.com.com/5100-10878_11-1058872.html

MyISAM is MySQL’s extended ISAM format and default database engine but it doesn’t support transactions .

So, Consisder an example by creating a table of EngineType Innodb that supports transactions .

Go to Command Line connect to your MySQL Instance(I have used command Line but you can use Your Front End Like MySQL Query Browser or Any Other)

1.Connect to MySQL

C:\Windows\system32>mysql -u root -P3306

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.22-rc-community-log

MySQL Community Server (GPL) Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

2.Create database test

mysql> CREATE DATABASE test;

Query OK, 1 row affected (0.00 sec)

3.Select Database that you have Created

mysql> use test;

Database changed

4.Create a table with Engine Innodb

mysql> create table NNN(AA INT) -> ENGINE=INNODB;

Query OK, 0 rows affected (0.05 sec)

5.Check whether Table is Successfully Created using Engine Innodb

mysql> show create table NNN;

+——-+———————————————————————- ——————+ | Table | Create Table | +——-+———————————————————————- ——————+ | NNN | CREATE TABLE `nnn` ( `AA` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +——-+———————————————————————- ——————+ 1 row in set (0.00 sec)

So,Above we can see Table tn created with Engine Type Innodb

6.Start Transaction

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

7.Insert Values in Your table

mysql> INSERT INTO NNN VALUES(12);

Query OK, 1 row affected (0.00 sec)

8.Rollback Your Transaction

mysql>ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

9.Check the Output

mysql> SELECT * FROM NNN;

Empty set (0.00 sec)

So, We can see that table dont have a single record because transaction is Rolled Back

10.Start Another Transaction

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

11.Insert value

mysql> INSERT INTO NNN VALUES(12);

Query OK, 1 row affected (0.00 sec)

12.Commit Transaction

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

13.Check Value in table

mysql> SELECT * FROM NNN;

+——+ | AA | +——+ | 12 | +——+ 1 row in set (0.00 sec)

Value reflected in table NNN

Happy Transactions!!!

Read Full Post »

%d bloggers like this: