MySQL Transaction 1

The version of MySQL I am applying is: 5.6.22

MySQL5.5 later uses the InnoDB storage engine by default, where InnoDB and BDB provide transaction-safe tables, and other storage engines are non-transactional security tables. you can specify the engine that you use when you create or modify a database by using the engine keyword. Main storage Engine: MyISAM, InnoDB, memory and merge introduction, through the example of MyISAM and InnoDB on the application of the difference in the transaction

A MyISAM
　　It does not support transactions, does not support foreign keys, especially fast access, there is no requirement for transactional integrity, or a SELECT, insert-based application can basically use this engine to create tables.
Each myisam is stored as 2 files on disk, with the same file name and table names, but with the following extensions:

Two InnoDB
The InnoDB storage Engine provides transactional security with commit, rollback, and crash resiliency. However, compared to the MyISAM storage engine, InnoDB writes are less efficient and consume more disk space to preserve data and indexes.

Example one: (1) The table structure of the test application is created as follows, and the storage engine chooses Mysam

Create table:create Table ' T1 ' (
' ID ' int (6) DEFAULT NULL,
' Id2 ' int (6) DEFAULT NULL,
' Name ' varchar (+) not NULL DEFAULT ' Zhaolf '
) Engine=myisam DEFAULT Charset=utf8

(2) Insert test Application Data mysql> select * from T1;
+------+------+------+
| ID | Id2 | name |
+------+------+------+
|    2 | 1 | DBDH |
|    8 | 8 | 8 |
|    7 | 7 | 7 |
+------+------+------+
3 Rows in Set (0.00 sec)

(3) operation

mysql> savepoint T1;
Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT INTO T1 values (3,3,3);
Query OK, 1 row affected (0.01 sec)

Mysql> select * from T1;
+------+------+------+
| ID | Id2 | name |
+------+------+------+
|    2 | 1 | DBDH |
|    8 | 8 | 8 |
|    7 | 7 | 7 |
|    3 | 3 | 3 |
+------+------+------+
4 rows in Set (0.00 sec)

mysql> rollback to T1;
Query OK, 0 rows affected, 1 Warning (0.00 sec)

Mysql> select * from T1;
+------+------+------+
| ID | Id2 | name |
+------+------+------+
|    2 | 1 | DBDH |
|    8 | 8 | 8 |
|    7 | 7 | 7 |
|    3 | 3 | 3 |
+------+------+------+

4 rows in Set (0.00 sec)

According to the above know that Mysam did not support the transaction

Example two: Table T1, storage engine modified to InnoDB

(1) First step: Modify to not auto-commit mode

mysql> ALTER TABLE T1 ENGINE=INNODB;
Query OK, 4 rows affected (0.62 sec)
Records:4 duplicates:0 warnings:0

Mysql> Show CREATE TABLE T1\g
1. Row ***************************
Table:t1
Create table:create Table ' T1 ' (
' ID ' int (6) DEFAULT NULL,
' Id2 ' int (6) DEFAULT NULL,
' Name ' varchar (+) not NULL DEFAULT ' Zhaolf '
) Engine=innodb DEFAULT Charset=utf8
1 row in Set (0.00 sec)

Mysql>

(2) Transaction operations

mysql> savepoint T1;
Query OK, 0 rows Affected (0.00 sec)

mysql> Delete from t1 where id = 3;
Query OK, 1 row Affected (0.00 sec)

mysql> rollback to T1;
Query OK, 0 rows Affected (0.00 sec)

Mysql> select * from T1;
+------+------+------+
| ID | Id2 | name |
+------+------+------+
|    2 | 1 | DBDH |
|    8 | 8 | 8 |
|    7 | 7 | 7 |
+------+------+------+
4 rows in Set (0.00 sec)

Mysql>

The difference between the two storage engines is illustrated by an example.

MySQL Transaction 1