1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
|
mysql> show engines \G;
***************************
1
. row ***************************
Engine: ndbcluster
Support: NO
Comment: Clustered, fault-tolerant tables
Transactions: NULL
XA: NULL
Savepoints: NULL
***************************
2
. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
***************************
3
. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/
null
storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
***************************
4
. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
***************************
5
. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored
in
memory, useful
for
temporary tables
Transactions: NO
XA: NO
Savepoints: NO
***************************
6
. row ***************************
Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
***************************
7
. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine
as
of MySQL
3.23
with
great performance
Transactions: NO
XA: NO
Savepoints: NO
***************************
8
. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
8
rows
in
set
(
0.00
sec)
ERROR:
No query specified
|
1 |
create table ...... engine=innodb;
|
1 |
show create table table_name;
|
1 |
alter table table_name engine=innodb;
|
1 2 3 4 |
start transaction update from account set money =money- 100 where name = 'a' ; update from account set money =money+ 100 where name = 'b' ; commit |
1 2 3 |
update from account
set
money
=money-
100
where
name
=
'a'
; update from account set money =money+ 100 where name = 'b' ; commit |
1
2
|
mysql> create table student(id
int
(
10
),name char(
10
),msg
var
char(
50
)) engine=innodb ;
Query OK,
0
rows affected (
0.05
sec)
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> select * from student;
+------+----------+------------+
| id | name | msg |
+------+----------+------------+
|
1
| chen | goof |
|
2
| zhaoqian | DEDDEFFccc |
|
3
| sunli | nihaoma |
|
4
| zhenwang | ngood |
|
5
| meiguo | nginx |
+------+----------+------------+
5
rows
in
set
(
0.00
sec)
|
1
2
3
4
5
6
7
|
mysql> show
var
iables like
"autocommit"
;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1
row
in
set
(
0.01
sec)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> insert into student values(
6
,
'zhongguo'
,
'xiaoping'
) ;
Query OK,
1
row affected (
0.01
sec)
mysql> savepoint s1 ;
//插入数据创建保存点是s1
Query OK,
0
rows affected (
0.00
sec)
mysql> update student
set
id=
7
where name=
'meiguo'
;
Query OK,
1
row affected (
0.00
sec)
Rows matched:
1
Changed:
1
Warnings:
0
mysql> savepoint s2
//修改数据创建保存点s2
-> ;
Query OK,
0
rows affected (
0.00
sec)
mysql>
delete
from student ;
Query OK,
6
rows affected (
0.00
sec)
mysql> savepoint s3;
//删除所有的表内容创建保存点s3
Query OK,
0
rows affected (
0.00
sec)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
回滚到s1 就有第六条数据,但是如果上述的创建保存点是在一个session进程的话,一旦rollback到s1那么后面的保存点也不存在了
mysql> rollback to savepoint s1
-> ;
Query OK,
0
rows affected (
0.00
sec)
mysql> select * from student;
+------+----------+------------+
| id | name | msg |
+------+----------+------------+
|
1
| chen | goof |
|
2
| zhaoqian | DEDDEFFccc |
|
3
| sunli | nihaoma |
|
4
| zhenwang | ngood |
|
5
| meiguo | nginx |
|
6
| zhongguo | xiaoping |
+------+----------+------------+
6
rows
in
set
(
0.00
sec)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
mysql> select * from student;
+------+----------+----------+
| id | name | msg |
+------+----------+----------+
|
6
| zhongguo | xiaoping |
|
1
| chen | goof |
|
5
| meiguo | nginx |
+------+----------+----------+
3
rows
in
set
(
0.00
sec)
mysql> savepoint good ;
Query OK,
0
rows affected (
0.00
sec)
mysql>
delete
from student;
Query OK,
3
rows affected (
0.01
sec)
mysql> select * from student;
Empty
set
(
0.00
sec)
mysql> rollback to savepoint good ;
Query OK,
0
rows affected (
0.00
sec)
mysql> select * from student;
+------+----------+----------+
| id | name | msg |
+------+----------+----------+
|
6
| zhongguo | xiaoping |
|
1
| chen | goof |
|
5
| meiguo | nginx |
+------+----------+----------+
3
rows
in
set
(
0.00
sec)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql> select * from student;
+------+----------+----------+
| id | name | msg |
+------+----------+----------+
|
6
| zhongguo | xiaoping |
|
1
| chen | goof |
|
5
| meiguo | nginx |
+------+----------+----------+
3
rows
in
set
(
0.00
sec)
mysql> update student
set
id=
2
where id=
6
;
Query OK,
1
row affected (
0.00
sec)
Rows matched:
1
Changed:
1
Warnings:
0
mysql> select * from student;
+------+----------+----------+
| id | name | msg |
+------+----------+----------+
|
2
| zhongguo | xiaoping |
|
1
| chen | goof |
|
5
| meiguo | nginx |
+------+----------+----------+
3
rows
in
set
(
0.00
sec)
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql>
use
test
Database changed
mysql> select * from student;
+------+----------+----------+
| id | name | msg |
+------+----------+----------+
|
6
| zhongguo | xiaoping |
|
1
| chen | goof |
|
5
| meiguo | nginx |
+------+----------+----------+
3
rows
in
set
(
0.00
sec)
|
1
2
3
4
5
6
7
|
mysql> select @@tx_isolation ;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1
row
in
set
(
0.00
sec)
|
1
2
3
4
5
6
7
|
mysql> show
var
iables like
"tx_isolation"
;
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1
row
in
set
(
0.00
sec)
|