Linux学习-MySQL事务(四)

丢爸

已于 2022-02-20 22:39:41 修改

阅读量105

点赞数

分类专栏： Linux 文章标签： mysql linux

于 2021-06-30 22:25:05 首次发布

本文链接：https://blog.csdn.net/xuwenpeng/article/details/118255309

版权

Linux 专栏收录该内容

146 篇文章 4 订阅

订阅专栏

连接管理器

接受请求
创建线程
认证用户
建立安全连接

并发控制

任何两个以上用户对同一张表(同一个数据)发出请求时，会产生并发操作。
多版本并发控制(MVCC):Multi-Version Concurrency Control
锁：
读锁：共享锁
写锁：独占锁(排他锁)

#给表设置锁
LOCK TABLES tb_name [READ|WRITE];
UNLOCK TABLES

锁粒度：从大到小，MySQL服务器只支持表级锁，行锁需要由存储引擎完成。

表锁：MyISAM存储引擎,Memory存储引擎
页锁：BDB存储引擎
行锁：InnoDB存储引擎
有关锁的具体内容请参考链接：表锁，页锁，行锁
参考—锁

事务

原子性(atomicity)：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性(consistency)：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性(Isolation)：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
1、读未提交（Read Uncommited），该隔离级别允许脏读取，其隔离级别最低；比如事务A和事务B同时进行，事务A在整个执行阶段，会将某数据的值从1开始一直加到10，然后进行事务提交，此时，事务B能够看到这个数据项在事务A操作过程中的所有中间值（如1变成2，2变成3等），而对这一系列的中间值的读取就是未授权读取
2、授权读取也称为已提交读（Read Commited），授权读取只允许获取已经提交的数据。比如事务A和事务B同时进行，事务A进行+1操作，此时，事务B无法看到这个数据项在事务A操作过程中的所有中间值，只能看到最终的10。另外，如果说有一个事务C，和事务A进行非常类似的操作，只是事务C是将数据项从10加到20，此时事务B也同样可以读取到20，即授权读取允许不可重复读取。
3、可重复读（Repeatable Read)，就是保证在事务处理过程中，多次读取同一个数据时，其值都和事务开始时刻是一致的，因此该事务级别禁止不可重复读取和脏读取，但是有可能出现幻影数据。所谓幻影数据，就是指同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果。在上面的例子中，可重复读取隔离级别能够保证事务B在第一次事务操作过程中，始终对数据项读取到1，但是在下一次事务操作中，即使事务B（注意，事务名字虽然相同，但是指的是另一个事务操作）采用同样的查询方式，就可能读取到10或20
4、串行化，是最严格的事务隔离级别，它要求所有事务被串行执行，即事务只能一个接一个的进行处理，不能并发执行。
持久性(durability)：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

日志中记录的是操作过程，不包含数据
服务器启动时，会读取日志，

日志组：

事务日志：

事务日志–顺序IO
数据文件–随机IO
参考文章–ReDo日志

重做日志(Redo Log)

#存放在数据目录下，名称为ib_logfile0和ib_logfile1
mysql> show variables like 'innodb_log%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| innodb_log_buffer_size      | 16777216 |
| innodb_log_checksums        | ON       |
| innodb_log_compressed_pages | ON       |
| innodb_log_file_size        | 50331648 |
| innodb_log_files_in_group   | 2        |
| innodb_log_group_home_dir   | ./       |
| innodb_log_write_ahead_size | 8192     |
+-----------------------------+----------+
[root@lotus data]# ll -h
total 121M
-rw-r----- 1 mysql mysql   56 Feb 15 08:27 auto.cnf
-rw------- 1 mysql mysql 1.7K Feb 15 08:27 ca-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Feb 15 08:27 ca.pem
-rw-r--r-- 1 mysql mysql 1.1K Feb 15 08:27 client-cert.pem
-rw------- 1 mysql mysql 1.7K Feb 15 08:27 client-key.pem
-rw-r----- 1 mysql mysql  470 Feb 17 20:25 ib_buffer_pool
-rw-r----- 1 mysql mysql  12M Feb 19 17:53 ibdata1
**-rw-r----- 1 mysql mysql  48M Feb 19 17:53 ib_logfile0
-rw-r----- 1 mysql mysql  48M Feb 15 08:27 ib_logfile1**

撤消日志(Undo Log)
Start Transaction:启动事务
SQL语句
COMMIT:提交 | ROLLBACK：事务回滚

事务的状态：

活动：Activate
部分提交的：最后一条语句执行后
失败的
中止的
提交的

事务一旦提交，将无法撤消
事务：并发执行
1. 提高吞吐量和资源利用率
2. 减少等待时间
  事务调试：
  可恢复调度
  无级联调度
  隔离级别
  READ UNCOMMITTED
  READ COMMITTED
  REPEATABLE READ（MySQL默认级别）
  SERIALIZABLE
  并发控制依赖的手段：
  锁
  时间戳
  多版本和快照隔离
  饿死：锁饥饿
  死锁：

#查询当前时间，此命令不会缓存
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 19:04:46       |
+----------------+
1 row in set (0.00 sec)
mysql> lock tables tutors read;
Query OK, 0 rows affected (0.00 sec)
#对表加读锁后，则插入操作会一直等待
mysql> insert into tutors(Tname,Gender,Age)values('Jerry','M',50);
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
#解除锁后，则数据插入成功
mysql> insert into tutors(Tname,Gender,Age)values('Jerry','M',50);
Query OK, 1 row affected (39.67 sec)
#查看默认的隔离级别
mysql> show global variables like '%isolation%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
#事务执行期间发现操作错误可以进行事务回滚
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
|  18 | Jerry        | M      |   50 |
+-----+--------------+--------+------+
15 rows in set (0.00 sec)

mysql> delete from tutors where TID=18;
Query OK, 1 row affected (0.02 sec)

mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
|  18 | Jerry        | M      |   50 |
+-----+--------------+--------+------+
15 rows in set (0.00 sec)
#事务一旦提交将无法回滚
mysql> delete from tutors where TID=18;
Query OK, 1 row affected (0.00 sec)

mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
#是否开启自动提交
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
#如果没有明确启动事务：autocommit：实现自动提交，每一个操作直接提交，会造成IO操作
建议：明确使用事务，并且关闭自动提交，关闭后，执行SQL语句前要使用START TRANSACTION启动事务。

保存点：SAVEPOINT

#先将自动提交关闭，然后执行DELETE操作后，使用ROLLBACK命令，则进行事务的回滚
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> use students;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)

mysql> delete from tutors where tid=10;
Query OK, 1 row affected (0.00 sec)

mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
+-----+--------------+--------+------+
13 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)


mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from tutors where TID=14;
Query OK, 1 row affected (0.00 sec)
mysql> savepoint ab;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from tutors where TID=13;
Query OK, 1 row affected (0.00 sec)

mysql> savepoint ac;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from tutors where TID=12;
Query OK, 1 row affected (0.00 sec)

mysql> savepoint ad;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
+-----+--------------+--------+------+
11 rows in set (0.00 sec)
#返回至ac保存点之前的数据
mysql> rollback to ac;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
+-----+--------------+--------+------+
12 rows in set (0.00 sec)

事务隔离性测试

#启动两个窗口，
#---------------------------------第一个窗口中输入以下命令

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
'---------read-uncommitted
mysql> set tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update tutors set Age=50 where TID=14;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   50 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
'---------read-uncommitted
'---------read-committed
mysql> set tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)
mysql> update tutors set Age=60 where TID=14;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   60 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
#执行事务提交
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
'---------read-committed
‘---------repeatable-read
mysql> set tx_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |  100 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)

mysql> update tutors set Age=60 where TID=14;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   60 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
‘---------repeatable-read
‘---------serializable
mysql> set tx_isolation='serializable';
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   60 |
+-----+--------------+--------+------+
14 rows in set (0.01 sec)

mysql> update tutors set Age=70 where TID=14;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   70 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
#提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

'---------serializable


#-----------------------------第二个窗口输入以下命令
'-----------read-uncommitted
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)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
#第一个窗口中事务执行了update语句后，再第二个窗口中查询，可以看到第一个窗口update的数据
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   50 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
#第一个窗口执行了rollback事务回滚后，第二个窗口查询结果如下，两次查看的数据不一致
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
'-----------read-uncommitted
'---------read-committed
mysql> set tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
#第一个窗口执行了update语句但没有提交事务，查看tutors数据没有更新
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
#当在第一个窗口执行了事务提交commit之后，查询结果如下：
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   60 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
'---------read-committed
‘---------repeatable-read
mysql> set tx_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
#第一个窗口执行完update语句没有执行提交，执行结果如下
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |  100 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
#第一个窗口执行完update语句并执行了commit提交，执行结果如下：
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |  100 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
#此事务提交后，再执行查询结果如下：
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   60 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
‘---------repeatable-read
‘----------serializable
mysql> set tx_isolation='serializable';
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
#第一个窗口未提交事务前，第二个窗口会一直卡住，处于串行化的事务管理，同时只能有一个事务执行
mysql> select * from tutors;
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
#当前窗口事务提交后，第一个窗口事务没有提交时查询的结果如下
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   60 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
#当前窗口事务提交后，第一个窗口事务已经提交时查询的结果如下：
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | M      |   49 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   70 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
‘----------serializable

事务级别越高，安全性越高，但并发能力越差

丢爸

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Linux学习-MySQL事务(四)

连接管理器接受请求创建线程认证用户建立安全连接任何两个以上用户对同一张表(同一个数据)发出请求时，会产生并发操作。??多版本并发控制(MVCC)锁：读锁：共享锁写锁：独占锁(排他锁)LOCK TABLES tb_name [READ|WRITE];UNLOCK TABLES锁粒度：从大到小，MySQL服务器只支持表级锁，行锁需要由存储引擎完成。表锁：页锁：行锁：事务：RDBMS：ACID原子性(atomicity)：一个事务（transaction）中的所有操作，要么全部
复制链接

扫一扫

专栏目录