连接管理器:
接收请求
创建线程
认证用户
进来安全连接
处理并发,多版本访问控制MVCC
每个用户在不同的时间快照上修改,修改完毕之后,数据库再合并数据修改情况。
锁:
按照锁的施行方式分类:读锁、写锁
读锁:共享锁,可以允许其他人读,但是不允许他人写
写锁:独占锁,不允许其他人的读、写操作
LOCK TABLES tb_name [read|write]
UNLOCK TABLES
按照锁的影响范围(锁粒度)分类
按照锁的影响范围(锁粒度)分为表锁、页锁(block)、行锁。MySQL服务器仅支持表级锁,行锁需要存储引擎完成。
锁表实例
session 1: 将表stu_test锁定为读锁
mysql> lock tables stu_test read;
Query OK, 0 rows affected (0.00 sec)
session 2:尝试读取和插入操作
mysql> select * from stu_test;
+----+------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+------+-----+--------+-----+------+
| 1 | lina | 3 | F | 33 | NULL |
+----+------+-----+--------+-----+------+
1 row in set (0.01 sec)
mysql> insert into stu_test (name,cid,gender,age,cid2) values ('jim',2,'M',23,3);
可以进行读操作,但是无法进行写操作。在插入数据时会阻塞,等待表解锁。
session 1:释放锁,session 2中的插入语句执行。
mysql> unlock tables;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from stu_test;
+----+------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+------+-----+--------+-----+------+
| 1 | lina | 3 | F | 33 | NULL |
| 2 | jim | 2 | M | 23 | 3 |
+----+------+-----+--------+-----+------+
2 rows in set (0.00 sec)
session 2: 显示插入数据成功。
mysql> insert into stu_test (name,cid,gender,age,cid2) values ('jim',2,'M',23,3);
Query OK, 1 row affected (3 min 49.03 sec)
事务
RDBMS:ACID(原子性、一致性、隔离性、持久性)
原子性:一个操作要么都完成,要么全部失败,比如转账,A转B,要么A转出成功,B接收成功,要么都失败。
一致性:事务的完成和完成之后的结果要一致。比如转账结束后,两者的总和是一样的。
隔离性:第一个事务执行不能影响到第二个事务的执行。
持久性:一个事务执行完毕之后,就算服务器宕机,服务器开机之后,事务还是存在的。内存中事务执行完毕之后会存储到物理设备上。
事务日志分为重做日志和撤销日志
重做日志(redo log):事务的操作在真正写到数据库中时,先写到日志当中。如果数据库崩溃,会根据重做日志进行重新写入。
撤销日志(undo log):每一次操作,在操作之前将原有的状态保存。当操作失败,可以还原日志
事务的执行过程:事务的操作都是在内存中完成然后写到事务日志中,过段时间,提交日志,修改物理设备中的数据。[执行语句写入事务日志] + [根据日志修改物理实例存储]
DELETE语句的操作,先在日志中标记删除,当事务中的语句都执行完毕,会将所有的操作写在日志中。最后日志提交之后会将数据同步到数据文件中。
日志组:事务的日志文件保存多个,如果只有一个日志,事务未执行完毕但是日志文件写满需要等待提交事务日志,严重影响执行效率。如果是两个日志文件,当第一个日志文件写完,可以写第二个日志文件,提交第一个日志。
事务的隔离性:
如果多个事务同时运行,第一个事务的执行对第二个事务的影响有隔离级别的区分
隔离级别:
READ-UNCOMMITTED: 读未提交,第一个人有删除操作,未提交事务,第二个人立马能看到。隔离级别最低,事务直接的影响最大。
READ-COMMITED: 读提交,第一个执行操作提交事务才能看到。会导致第二个人在事务执行时,能看到数据,第二次执行操作时发现无法找到数据。
REPATABLE-READ : 可重读,第一个事务的执行、提交不会影响第二个事务的执行,但是在第二个事务的提交时可能会导致和执行的时候不同。(MySQL)
SERIALIZABLE: 可串行 。第一个事务完全执行完毕物理存储文件中的数据修改完毕执行第二个事务。
mysql> show global variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
事务的隔离性测试
读未提交
session 1:设置隔离级别为读未提交
mysql> set tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
session 2:设置隔离级别为读未提交
mysql> set tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
Session 1和session 2 都启动事务:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
session 1: 修改数据
mysql> update student set age = 22 where id = 14;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session 2 : 查询修改后的数据
mysql> select * from student;
+----+--------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+--------+-----+--------+-----+------+
| 2 | tom | 2 | M | 19 | NULL |
| 3 | jack | 3 | M | 20 | 4 |
| 4 | lucy | 2 | F | 25 | 4 |
| 5 | neccy | 3 | F | 30 | NULL |
| 6 | mary | 4 | F | 21 | 5 |
| 7 | kaka | 5 | M | 21 | 3 |
| 8 | suke | 3 | M | 20 | 5 |
| 9 | suke | 4 | M | 21 | 3 |
| 10 | beita | 3 | M | 24 | 1 |
| 11 | wukong | 5 | M | 25 | 2 |
| 12 | wujing | 1 | M | 28 | NULL |
| 13 | wuneng | 4 | F | 30 | NULL |
| 14 | tita | 3 | NULL | 22 | 1 |
+----+--------+-----+--------+-----+------+
13 rows in set (0.00 sec)
session 1: 回滚数据
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
session 2:查看数据
mysql> select * from student;
+----+--------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+--------+-----+--------+-----+------+
| 2 | tom | 2 | M | 19 | NULL |
| 3 | jack | 3 | M | 20 | 4 |
| 4 | lucy | 2 | F | 25 | 4 |
| 5 | neccy | 3 | F | 30 | NULL |
| 6 | mary | 4 | F | 21 | 5 |
| 7 | kaka | 5 | M | 21 | 3 |
| 8 | suke | 3 | M | 20 | 5 |
| 9 | suke | 4 | M | 21 | 3 |
| 10 | beita | 3 | M | 24 | 1 |
| 11 | wukong | 5 | M | 25 | 2 |
| 12 | wujing | 1 | M | 28 | NULL |
| 13 | wuneng | 4 | F | 30 | NULL |
| 14 | tita | 3 | NULL | 32 | 1 |
+----+--------+-----+--------+-----+------+
13 rows in set (0.00 sec)
总结:session1 中的数据修改,还没有提交,session2中就可以看到。会导致session2中的数据前后查询的结果不同。
读提交
session 1 和session 2:设置隔离级别为读未提交
mysql> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)
session 1 和session 2:启动事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
session 1 修改数据库中的数据
mysql> update tb1 set age=33 where id=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tb1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tom | 30 |
| 2 | jim | 33 |
+------+------+------+
2 rows in set (0.02 sec)
session 2 :查看数据,没有改变
mysql> select * from tb1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tom | 30 |
| 2 | jim | 35 |
+------+------+------+
2 rows in set (0.02 sec)
session 1 :提交事务
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
session 2 :查看数据
mysql> select * from tb1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tom | 30 |
| 2 | jim | 33 |
+------+------+------+
2 rows in set (0.02 sec)
总结:session 1中的事务没有提交,session2中的数据没有变化,如果session1中的事务提交完毕,session2 没有执行完毕,会导致前后执行的结果不同。
事务的可重读
session1 和 session 2 : 同时设置事务的隔离等级
mysql> set tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql>
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.03 sec)
session1 和 session 2 :启动事务
mysql> start transaction;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from tb1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tom | 30 |
| 2 | jim | 31 |
+------+------+------+
2 rows in set (0.02 sec)
session1 : 修改数据并提交
mysql> update tb1 set age=55 where id = 2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tb1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tom | 30 |
| 2 | jim | 55 |
+------+------+------+
2 rows in set (0.03 sec)
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from tb1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tom | 30 |
| 2 | jim | 55 |
+------+------+------+
2 rows in set (0.02 sec)
session2 :查看数据是否修改
mysql> select * from tb1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tom | 30 |
| 2 | jim | 31 |
+------+------+------+
2 rows in set (0.02 sec)
session2 : 查看提交前后数据的修改。
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from tb1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tom | 30 |
| 2 | jim | 55 |
+------+------+------+
2 rows in set (0.03 sec)
总结:session1 中的数据修改、事务提交之后,session2中的数据是不同的,但是session2中的事务提交前后不同。
可串行
session1 和 session2 :设置事务的隔离级别
mysql> set tx_isolation = 'SERIALIZABLE';
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE |
+----------------+
1 row in set, 1 warning (0.02 sec)
session1 和 session2 :启动事务
mysql> start transaction;
Query OK, 0 rows affected (0.02 sec)
session1 :修改数据
mysql> select * from tb1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tom | 30 |
| 2 | jim | 55 |
+------+------+------+
2 rows in set (0.02 sec)
mysql> update tb1 set age=88 where id = 2;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session2:查询数据,发现数据卡顿甚至延迟较高导致中断。
mysql> select * from tb1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
session1:提交事务
mysql> commit
-> ;
Query OK, 0 rows affected (0.04 sec)
session2:查询数据
mysql> select * from tb1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tom | 30 |
| 2 | jim | 88 |
+------+------+------+
2 rows in set (0.02 sec)
总结:session1,没有执行完毕,session2 是无法执行的。只有session1中的事务提交之后session2中的数据才能显示。
易错点
如果在同一个会话中设置这四种隔离模式会导致,现象出错。
原因:使用的事务不是对应的事务的隔离级别
解决方案:
1.每一次测试重新连接
2.在做下一次测试时,使用rollback ,多回滚几次。
事务的特性:
automicity:原子性,事务所引起的数据库操作,要么完成,要么都不执行
consistency:一致性,
isolation:隔离性,
事务调度:事务之间的影响最小
MVCC: 多版本并发控制
durability:持久性,一旦事务完成都要保证事
1.事务提交之前就已经写出数据至持久性存储
2.结合书屋日志完成
事务日志:顺序IO
数据文件:随机IO
事务的状态
事务状态 | 解释 |
---|---|
活动的:Active | 事务正在执行中 |
部分提交 | 最后一条语句执行,最后一条语句执行后,数据写到磁盘上 |
失败 | 事务正常提交了,但是未能完成提交 |
中止的 | 事务没有提交 |
提交的 | 事务提交,并成功提交 |
事务并发执行:
1.提高吞吐量和资源的利用率
2.减少等待时间
并发控制依赖的技术手段:
锁
时间锁
多版本和快照隔离
饿锁:锁饥饿,事务无法调度锁
死锁:
事务调度:
可恢复调度
无级联调度
事务的启动
能够启动事务的操作,一般是一堆SQL语句,或是是ODBC中启动事务的指令。
start tansaction:启动事务的指令
SQL语句,(事务的操作指令)
commit:提交,(事务完毕)
rollback :事务回滚
事务的自动提交
如果没有明确的启动事务,
autocommit:能实现自动提交,每一个操作都直接提交
建议:明确使用事务,并且关闭自动提交。
通过变量查看是设置自动提交的
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
关闭自动提交
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
回滚测试
mysql> delete from student where id = 3;
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+--------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+--------+-----+--------+-----+------+
| 2 | tom | 2 | M | 19 | NULL |
| 4 | lucy | 2 | F | 25 | 4 |
| 5 | neccy | 3 | F | 30 | NULL |
| 6 | mary | 4 | F | 21 | 5 |
| 7 | kaka | 5 | M | 21 | 3 |
| 8 | suke | 3 | M | 20 | 5 |
| 9 | suke | 4 | M | 21 | 3 |
| 10 | beita | 3 | M | 24 | 1 |
| 11 | wukong | 5 | M | 25 | 2 |
| 12 | wujing | 1 | M | 28 | NULL |
| 13 | wuneng | 4 | F | 30 | NULL |
| 14 | tita | 3 | NULL | 32 | 1 |
+----+--------+-----+--------+-----+------+
12 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from student;
+----+--------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+--------+-----+--------+-----+------+
| 2 | tom | 2 | M | 19 | NULL |
| 3 | jack | 3 | M | 20 | 4 |
| 4 | lucy | 2 | F | 25 | 4 |
| 5 | neccy | 3 | F | 30 | NULL |
| 6 | mary | 4 | F | 21 | 5 |
| 7 | kaka | 5 | M | 21 | 3 |
| 8 | suke | 3 | M | 20 | 5 |
| 9 | suke | 4 | M | 21 | 3 |
| 10 | beita | 3 | M | 24 | 1 |
| 11 | wukong | 5 | M | 25 | 2 |
| 12 | wujing | 1 | M | 28 | NULL |
| 13 | wuneng | 4 | F | 30 | NULL |
| 14 | tita | 3 | NULL | 32 | 1 |
+----+--------+-----+--------+-----+------+
13 rows in set (0.00 sec)
保存点
mysql> help savepoint
Name: 'SAVEPOINT'
Description:
Syntax:
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
mysql> start transaction; # 启动事务
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+--------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+--------+-----+--------+-----+------+
| 2 | tom | 2 | M | 19 | NULL |
| 3 | jack | 3 | M | 20 | 4 |
| 4 | lucy | 2 | F | 25 | 4 |
| 5 | neccy | 3 | F | 30 | NULL |
| 6 | mary | 4 | F | 21 | 5 |
| 7 | kaka | 5 | M | 21 | 3 |
| 8 | suke | 3 | M | 20 | 5 |
| 9 | suke | 4 | M | 21 | 3 |
| 10 | beita | 3 | M | 24 | 1 |
| 11 | wukong | 5 | M | 25 | 2 |
| 12 | wujing | 1 | M | 28 | NULL |
| 13 | wuneng | 4 | F | 30 | NULL |
| 14 | tita | 3 | NULL | 32 | 1 |
+----+--------+-----+--------+-----+------+
13 rows in set (0.00 sec)
mysql> savepoint stu0; # 设置事务的保存点
Query OK, 0 rows affected (0.00 sec)
mysql> delete from student where id = 2;
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+--------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+--------+-----+--------+-----+------+
| 3 | jack | 3 | M | 20 | 4 |
| 4 | lucy | 2 | F | 25 | 4 |
| 5 | neccy | 3 | F | 30 | NULL |
| 6 | mary | 4 | F | 21 | 5 |
| 7 | kaka | 5 | M | 21 | 3 |
| 8 | suke | 3 | M | 20 | 5 |
| 9 | suke | 4 | M | 21 | 3 |
| 10 | beita | 3 | M | 24 | 1 |
| 11 | wukong | 5 | M | 25 | 2 |
| 12 | wujing | 1 | M | 28 | NULL |
| 13 | wuneng | 4 | F | 30 | NULL |
| 14 | tita | 3 | NULL | 32 | 1 |
+----+--------+-----+--------+-----+------+
12 rows in set (0.00 sec)
mysql> savepoint stu2;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from student where id = 3;
Query OK, 1 row affected (0.00 sec)
mysql> savepoint stu3;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+--------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+--------+-----+--------+-----+------+
| 4 | lucy | 2 | F | 25 | 4 |
| 5 | neccy | 3 | F | 30 | NULL |
| 6 | mary | 4 | F | 21 | 5 |
| 7 | kaka | 5 | M | 21 | 3 |
| 8 | suke | 3 | M | 20 | 5 |
| 9 | suke | 4 | M | 21 | 3 |
| 10 | beita | 3 | M | 24 | 1 |
| 11 | wukong | 5 | M | 25 | 2 |
| 12 | wujing | 1 | M | 28 | NULL |
| 13 | wuneng | 4 | F | 30 | NULL |
| 14 | tita | 3 | NULL | 32 | 1 |
+----+--------+-----+--------+-----+------+
11 rows in set (0.00 sec)
mysql> rollback to stu0; #恢复保存点
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+--------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+--------+-----+--------+-----+------+
| 2 | tom | 2 | M | 19 | NULL |
| 3 | jack | 3 | M | 20 | 4 |
| 4 | lucy | 2 | F | 25 | 4 |
| 5 | neccy | 3 | F | 30 | NULL |
| 6 | mary | 4 | F | 21 | 5 |
| 7 | kaka | 5 | M | 21 | 3 |
| 8 | suke | 3 | M | 20 | 5 |
| 9 | suke | 4 | M | 21 | 3 |
| 10 | beita | 3 | M | 24 | 1 |
| 11 | wukong | 5 | M | 25 | 2 |
| 12 | wujing | 1 | M | 28 | NULL |
| 13 | wuneng | 4 | F | 30 | NULL |
| 14 | tita | 3 | NULL | 32 | 1 |
+----+--------+-----+--------+-----+------+
13 rows in set (0.00 sec)
mysql> commit
-> ;
Query OK, 0 rows affected (0.01 sec)
事务回滚测试
事务提交之前可以回滚。
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> use students;
Database changed
mysql> select * from students;
ERROR 1146 (42S02): Table 'students.students' doesn't exist
mysql> select * from student;
+----+--------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+--------+-----+--------+-----+------+
| 1 | jim | 1 | M | 21 | 1 |
| 2 | tom | 2 | M | 19 | NULL |
| 3 | jack | 3 | M | 20 | 4 |
| 4 | lucy | 2 | F | 25 | 4 |
| 5 | neccy | 3 | F | 30 | NULL |
| 6 | mary | 4 | F | 21 | 5 |
| 7 | kaka | 5 | M | 21 | 3 |
| 8 | suke | 3 | M | 20 | 5 |
| 9 | suke | 4 | M | 21 | 3 |
| 10 | beita | 3 | M | 24 | 1 |
| 11 | wukong | 5 | M | 25 | 2 |
| 12 | wujing | 1 | M | 28 | NULL |
| 13 | wuneng | 4 | F | 30 | NULL |
| 14 | tita | 3 | NULL | 32 | 1 |
+----+--------+-----+--------+-----+------+
14 rows in set (0.00 sec)
mysql> delete from student where id = 1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+--------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+--------+-----+--------+-----+------+
| 2 | tom | 2 | M | 19 | NULL |
| 3 | jack | 3 | M | 20 | 4 |
| 4 | lucy | 2 | F | 25 | 4 |
| 5 | neccy | 3 | F | 30 | NULL |
| 6 | mary | 4 | F | 21 | 5 |
| 7 | kaka | 5 | M | 21 | 3 |
| 8 | suke | 3 | M | 20 | 5 |
| 9 | suke | 4 | M | 21 | 3 |
| 10 | beita | 3 | M | 24 | 1 |
| 11 | wukong | 5 | M | 25 | 2 |
| 12 | wujing | 1 | M | 28 | NULL |
| 13 | wuneng | 4 | F | 30 | NULL |
| 14 | tita | 3 | NULL | 32 | 1 |
+----+--------+-----+--------+-----+------+
13 rows in set (0.00 sec)
mysql> rollback
-> ;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from student;
+----+--------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+--------+-----+--------+-----+------+
| 1 | jim | 1 | M | 21 | 1 |
| 2 | tom | 2 | M | 19 | NULL |
| 3 | jack | 3 | M | 20 | 4 |
| 4 | lucy | 2 | F | 25 | 4 |
| 5 | neccy | 3 | F | 30 | NULL |
| 6 | mary | 4 | F | 21 | 5 |
| 7 | kaka | 5 | M | 21 | 3 |
| 8 | suke | 3 | M | 20 | 5 |
| 9 | suke | 4 | M | 21 | 3 |
| 10 | beita | 3 | M | 24 | 1 |
| 11 | wukong | 5 | M | 25 | 2 |
| 12 | wujing | 1 | M | 28 | NULL |
| 13 | wuneng | 4 | F | 30 | NULL |
| 14 | tita | 3 | NULL | 32 | 1 |
+----+--------+-----+--------+-----+------+
14 rows in set (0.00 sec)
mysql> select * from student;
+----+--------+-----+--------+-----+------+
| id | name | cid | gender | age | cid2 |
+----+--------+-----+--------+-----+------+
| 2 | tom | 2 | M | 19 | NULL |
| 3 | jack | 3 | M | 20 | 4 |
| 4 | lucy | 2 | F | 25 | 4 |
| 5 | neccy | 3 | F | 30 | NULL |
| 6 | mary | 4 | F | 21 | 5 |
| 7 | kaka | 5 | M | 21 | 3 |
| 8 | suke | 3 | M | 20 | 5 |
| 9 | suke | 4 | M | 21 | 3 |
| 10 | beita | 3 | M | 24 | 1 |
| 11 | wukong | 5 | M | 25 | 2 |
| 12 | wujing | 1 | M | 28 | NULL |
| 13 | wuneng | 4 | F | 30 | NULL |
| 14 | tita | 3 | NULL | 32 | 1 |
+----+--------+-----+--------+-----+------+
13 rows in set (0.00 sec)
事务提交
事务提交之后无法回滚。
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from student where id = 1;
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)