MySQL Begin Transaction

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

MySQL Begin Transaction

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;