MySQL Transaction simple test

Auth:jin
date:20140507

First, transaction control
By default, MySQL is autocommit (autocommit), and if you need to commit and rollback a transaction through explicit commit and rollblack, you need to start the transaction with explicit transaction control commands.
SET autocommit, the START transaction,commit and rollback statements support local transactions.
1. Start a new business
The start TRANSACTION or BEGIN statement can start a new transaction.
2. Commit ROLLBACK TRANSACTION
Commit and rollback are used to commit rollback transactions
3. Operations after a transaction commits or rolls back
Chain and release words are used to define operations after a transaction commits or rolls back.
Chain will open a new transaction immediately and have the same isolation level as the transaction just now
RELEASE disconnects and links to the client.
4. Modify the current link submission method
Set Autocommit can modify the current way the link is submitted, if set autocommit=0, then the transaction used after Setup needs to be committed or rolled back by explicit commands.

Instance
CREATE TABLE ' T1 ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar (+) not NULL,
PRIMARY KEY (' id '),
UNIQUE KEY ' name ' (' name ')
) Engine=innodb DEFAULT Charset=utf8;
Note to Innodb,myisam does not support transactions
Session 1
mysql> INSERT into T1 (name) value (' Diege ');
Query OK, 1 row Affected (0.00 sec)

Mysql> select * from T1;
+----+-------+
| ID | name |
+----+-------+
| 1 | Diege |
+----+-------+
1 row in Set (0.00 sec)

Session 2
Mysql> select * from T1;
+----+-------+
| ID | name |
+----+-------+
| 1 | Diege |
+----+-------+
1 row in Set (0.00 sec)

Mysql> SET autocommit=0;
Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT into T1 (name) value (' Lily ');
Query OK, 1 row Affected (0.00 sec)

Session 1
Mysql> select * from T1;
+----+-------+
| ID | name |
+----+-------+
| 1 | Diege |
+----+-------+
1 row in Set (0.00 sec)
The number has no new data, because things are not submitted.
Session 2
Mysql> commit;
Query OK, 0 rows affected (0.02 sec)
Session 1
Mysql> select * from T1;
+----+-------+
| ID | name |
+----+-------+
| 1 | Diege |
| 2 | Lily |
+----+-------+
2 rows in Set (0.00 sec)
There's data after you submit things.

Second, transaction rollback
In a transaction, you can specify a part of a rollback transaction by defining savepoint, but you cannot specify a part of the commit transaction. Transaction rollback requires a transaction to be started
Session A
Mysql> select * from T1;
+----+-------+
| ID | name |
+----+-------+
| 1 | Diege |
| 2 | Lily |
+----+-------+
2 rows in Set (0.00 sec)
Mysql> SET autocommit=0;
Query OK, 0 rows Affected (0.00 sec)
Start a transaction
mysql> start transaction;
Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT into T1 (name) value (' Tom ');
Query OK, 1 row Affected (0.00 sec)

Mysql> select * from T1;
+----+-------+
| ID | name |
+----+-------+
| 1 | Diege |
| 2 | Lily |
| 3 | Tom |
+----+-------+
3 Rows in Set (0.00 sec)

Define a savapoint named Backup1
Mysql> savepoint Backup1;
Query OK, 0 rows Affected (0.00 sec)
Insert a second piece of data
mysql> INSERT into T1 (name) value (' json ');
Query OK, 1 row Affected (0.00 sec)
Mysql> select * from T1;
+----+-------+
| ID | name |
+----+-------+
| 1 | Diege |
| 4 | JSON |
| 2 | Lily |
| 3 | Tom |
+----+-------+
4 rows in Set (0.00 sec

Roll back to the savepoint you just defined
mysql> rollback to SavePoint backup1;
Query OK, 0 rows affected (0.01 sec)
Viewing post-rollback data conditions
Mysql> select * from T1;
+----+-------+
| ID | name |
+----+-------+
| 1 | Diege |
| 2 | Lily |
| 3 | Tom |
+----+-------+
3 Rows in Set (0.00 sec

Then the other session session B data situation
Mysql> select * from T1;
+----+-------+
| ID | name |
+----+-------+
| 1 | Diege |
| 2 | Lily |
+----+-------+
2 rows in Set (0.00 sec)
No data changes
Back to session a COMMIT transaction
Mysql> commit;
Query OK, 0 rows Affected (0.00 sec
Session B then view the data
Mysql> select * from T1;
+----+-------+
| ID | name |
+----+-------+
| 1 | Diege |
| 2 | Lily |
| 3 | Tom |
+----+-------+
3 Rows in Set (0.00 sec)
You can see the new data, and only the data before savapoint.