MYSQL(八)

2023-04-06 Thursday

服务器

1. 事务处理

mysql学习笔记整理-事务处理

事务处理

事务是保证多个 SQL 操作的一致性，如果一条失败全部 SQL 也将失效。

学习事务时建议开启两个客户端（或 GUI 软件如 Dbeaver、SequelPro）来体验

业务分析

事务是保证多个 SQL 操作的一致性，如果一条失败全部 SQL 也将失效。
实际业务中大多数是对多个表操作，比如当发表文章时需要将文章的基本信息发到文章基础表和文章内容添加到文章内容表，这种情况不使用事务也没有关系，如果出现数据异常重新添加就可以了
但牵涉到货币的情况就必须使用事务了，必须保证货币处理是准确的
当然有些公司要求所有查询都使用事务，这就遵照公司要求完成就可以了

储存引擎

查看引擎

1	SHOW ENGINES;

最新版本的 Mysql 中已经不建议使用 MyISAM引擎了，所以我们也不用讨论它了。
InnoDB
支持事务的引擎建议使用 InnoDB。如果旧表是其他引擎，使用下面语句更改为InnoDB引擎。

1	ALTER TABLE stu ENGINE=InnoDB;

提交模式

自动提交

Mysql 的提交默认是自动提交，即发送一条执行一条。
在 DBeaver 执行以下 SQL 后，在另一个 Sequel Pro 会立刻看到结果。

1	INSERT INTO stu (class_id,sname,sex)VALUES(2,'张帝','女');

在 Sequel Pro 里即可看到结果，这为自动提交。

事务提交

事务单独开启
执行 START TRANSACTION 或 BEGIN 语句后，表示要开启一项事务处理。

COMMIT 提交事务
ROLLBACK 回滚事务

1
2
3

START TRANSACTION;
INSERT INTO stu (class_id,sname,sex)VALUES(2,'张帝','女');
COMMIT;

全局开启事务
如果所有 SQL 都使用事务操作，我们可以通过 SET AUTOCOMMIT=0 关闭自动提交来开启事务机制，这样所有语句都是事务类型。

-- 关闭自动提交
SET AUTOCOMMIT = 0;

INSERT INTO stu (class_id,sname,sex)VALUES(2,'李清','女');
COMMIT;

-- 开启自动提交
SET AUTOCOMMIT = 1;

事务隔离

并发问题

当高并发访问会遇到多个事务的隔离问题，可能会出现以下：

脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据
不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。
幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

隔离级别

事务隔离级别	脏读	不可重复读	幻读	说明
读未提交（read-uncommitted）	是	是	是	最低的事务隔离级别，一个事务还没提交时，它做的变更就能被别的事务看到
不可重复读（read-committed）	否	是	是	保证一个事物提交后才能被另外一个事务读取。另外一个事务不能读取该事物未提交的数据。
可重复读（repeatable-read）	否	否	是	多次读取同一范围的数据会返回第一次查询的快照，即使其他事务对该数据做了更新修改。事务在执行期间看到的数据前后必须是一致的。
串行化（serializable）	否	否	否	事务 100% 隔离，可避免脏读、不可重复读、幻读的发生。花费最高代价但最可靠的事务隔离级别。

查询级别

mysql8 版本查询隔离级别

1	select @@global.transaction_isolation,@@transaction_isolation;

mysql8 以下版本查询隔离级别

1	select @@tx_isolation;

设置级别

设置会话隔离级别，影响当前连接

1	set session transaction isolation level read uncommitted;

设置全局隔离级别，影响全局连接

1	set global transaction isolation level read uncommitted;

脏读

为了演示效果将隔离级别设置为最低级 read uncommitted。脏读是一个事务没有提交时可被其他事务读取到。
1.事务 A 执行更新操作

1
2
3

set session transaction isolation level read UNCOMMITTED;
BEGIN;
UPDATE stu SET sname = '后盾人' WHERE id=1;

2.因为使用了最低级别read uncommitted，事务 B 在事务 A 没有提交时就可以看到更新的数据，如果事务 A 执行ROLLBACK 事务 B 的读到的数据就为脏数据。

1
2
3

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT * FROM `stu`;

3.将隔离级别设置为除 read uncommitted 以外的，再重复上面的例子，都可以有效避免脏读的问题。

不可重复读

不可重复读指在事务中多次读取的数据出现不一致的情况，我们希望读取的数据在本事务中是一致的。

事务 A 在执行过程中更新数据，事务 B 同时读取的数据没有脏数据。
但当事务 A 提交了事务后，事务 B 再读取时得到了最新的数据，这种情况为不可重复读。
所以要保证事务过程中的数据重复操作是一致的，不受其他事务影响，即避免不可重复读的产生。

为了演示效果将隔离级别设置为低级别 read committed。
1.事务 A 执行以下代码，但没有提交

1
2
3

set session transaction isolation level READ COMMITTED;
BEGIN;
UPDATE stu SET sname = '后盾人9' WHERE id=1;

2.因为使用了 read committed级别，所以事务 B 不会读到脏数据

1
2
3

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT * FROM stu;

3.事务 A 提交事务

1
2
3

...
commit
...

4.此时事务 B 可以读取到事务 A 提交的数据，这就是不能重复读取到同一个数据，即事务 B 读取结果受事务 A 影响。
5.将隔离机制设置为 REPEATABLE READ 就可以解决这类不可重复读的问题。

幻读

幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体。
1.事务 A 执行查询，假如查询结果是 6 条

1
2
3

set session transaction isolation level REPEATABLE READ;
BEGIN;
SELECT * FROM stu;

2.事务 B 执行添加

1
2
3

set session transaction isolation level REPEATABLE READ;
insert into stu (class_id,sname,sex) values(2,'后盾人',1);
commit;

3.事务 A 执行更新，发现更新了 7 条（刚才查询时 6 条，但更新了七条，感觉像出现了幻觉）

1
2
3

...
UPDATE stu SET balance = 200;
...

4.切换隔离级别为 SERIALIZABLE 后，在事务 A 没有提交时，事务 B 是不能插入数据的（表现形式为等待）。

1
2
3

set session transaction isolation level SERIALIZABLE;

select @@global.transaction_isolation,@@transaction_isolation;

程序控制

程序语言基本上都支持事务的处理。

<?php
header("Content-type:text/html;charset=utf8");
try {
    $config = [
        'host' => '127.0.0.1',
        'user' => 'root',
        'password' => 'root',
        'database' => 'test',
        'charset' => 'utf8'
    ];
    $dsn = sprintf(
        "mysql:host=%s;dbname=%s;charset=%s",
        $config['host'],
        $config['database'],
        $config['charset']
    );
    $pdo = new PDO($dsn, $config['user'], $config['password']);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die($e->getMessage());
}
try {
    # 开启事务
    $pdo->beginTransaction();
    $d = $pdo->exec("INSERT INTO stu (class_id,sname,sex)
     VALUES(2,'后盾人','男')");

    $pdo->exec("UPDATE class SET
     stu_count = (SELECT count(*) FROM stu WHERE class_id=2) WHERE id=2");
    # 提交事务
    $pdo->commit();
} catch (PDOException $e) {
    # 事务回滚
    $pdo->rollBack();
    die("Exception:" . $e->getMessage());
}

MYSQL笔记

MYSQL(九) MYSQL(七)