Home Sql Mysql-example MySQL Begin Transaction
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

MySQL Begin Transaction

Advertisement
MySQL Begin Transaction is a point in which data is referenced by a connection logically or physically.

MySQL Begin Transaction

     

MySQL Begin Transaction is a point in which data is referenced by a connection logically or physically. The error are encountered, when all the data modification made after the begin transaction can set to rolled back to return the data to known state of consistency. Commit Transaction is done to make the modifications a permanent part of the database or in simple words all the modification are erased with ROLLBACK Transaction statement. In most of the case SQL Server requires updation in the records. Sometime we want to restore the same value before the update query. To perform this we make use of rollback statement which helps you to restore the original value before the update query has taken place.

This example illustrates how to use the BEGIN keyword in a Transaction.

In this example we create a table 'trans' with column 'id', 'item' and 'quantity'. The query insert into adds one row in the table. Now we execute a BEGIN command and then UPDATE the existing row. After updating the row we can ROLLBACK the data of the table as below in the table. The Rollback statement is used to restore the original value present before the update query.

Query

 CREATE TABLE trans
 (
 id int not null auto_increment,
 item varchar(30) not null,
 quantity varchar(10) not null,
 primary key(id)
 )type=innodb;

Query

DESC trans;

Output

+----------+---------------+------+-----+---------+----------------+
| Field    | Type          | Null | Key | Default | Extra          |
+----------+---------------+------+-----+---------+----------------+
| id       | int(11)       | NO   | PRI |         | auto_increment |
| item     | varbinary(30) | NO   |     |         |                |
| quantity | varbinary(10) | NO   |     |         |                |
+----------+---------------+------+-----+---------+----------------+

Query

INSERT INTO trans (id,item,quantity) VALUES (NULL,'Computer','5');		

Output

+----+----------+----------+
| id | item     | quantity |
+----+----------+----------+
| 1  | Computer | 5        |
+----+----------+----------+

Query

BEGIN;

Query

UPDATE trans SET quantity ='4' WHERE id=1;

Output

+----+----------+----------+
| id | item     | quantity |
+----+----------+----------+
| 1  | Computer | 4        |
+----+----------+----------+

Query

ROLLBACK;
Output
+----+----------+----------+
| id | item     | quantity |
+----+----------+----------+
| 1  | Computer | 5        |
+----+----------+----------+
Query
COMMIT;
Advertisement

Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: February 4, 2009

Ask Questions?    Discuss: MySQL Begin Transaction   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
Pradeep K.
December 5, 2011
Conditional commit or rollback?

Is there a way to conditionally commit/rollback an entire transaction if any statement in the batch fails?
DMCA.com