高性能MySQL（第3版）笔记 1.3 事务

2020-06-22 09:50 314人阅读评论(0)

1.3 事务

事务：一组原子性的SQL查询，如果数据库引擎能够成功地对数据库应用改组查询呢的全部语句，那么就执行该查询，如果其中任何一条执行失败，那么其他语句都不会执行。
事务内的语句，要么全部执行成功，要么全部执行失败

事务的四大特性：
原子性(Atomicity)：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
首先创建两张表并插入数据

DROP TABLE
IF
	EXISTS checking;
CREATE TABLE checking(
customer_id int(10) UNSIGNED NOT NULL,
balance DOUBLE UNSIGNED NOT NULL DEFAULT 0 CHECK(balance >= 0),
-- 使用CHECK时注意mysql版本，有些版本虽然能解析CHECK语法，但是并不生效
PRIMARY KEY (customer_id)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO checking (customer_id, balance)
VALUES
		(10233276, 20),
		(10233277, 19);

DROP TABLE
IF
	EXISTS savings;
CREATE TABLE savings(
customer_id int(10) UNSIGNED NOT NULL,
balance DOUBLE UNSIGNED NOT NULL DEFAULT 0 CHECK(balance >= 0),
PRIMARY KEY (customer_id)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO savings (customer_id, balance)
VALUES
		(10233276, 80),
		(10233277, 81);

如果Jane(10233276)想要从自己的支票余额中转移20美元到自己的储蓄账户
这个过程包含两个步骤
checking: 20 - 20 = 0
savings: 80 + 20 = 100

START TRANSACTION;
SELECT balance FROM checking WHERE customer_id = 10233276;
UPDATE savings SET balance = balance + 20 WHERE customer_id = 10233276;
UPDATE checking SET balance = balance - 20 WHERE customer_id = 10233276;
COMMIT;

结果：
checkings

savings

这两个步骤必须同时执行成功，否则同时执行失败，例如支票余额不足20美元，那么储蓄账户中不会增加20美元(创建表的时候用check约束)

使用另一个用户(10233277)进行测试

START TRANSACTION;
SELECT balance FROM checking WHERE customer_id = 10233277;
UPDATE savings SET balance = balance + 20 WHERE customer_id = 10233277;
UPDATE checking SET balance = balance - 20 WHERE customer_id = 10233277;
COMMIT;

信息

可以看到checkings表由于余额不足没有向下执行，再查看表中的数据发现没有发生变化
结果：
checkings

savings

一致性(Consistency)：事务前后数据的完整性必须保持一致。

同上:

状态	checking	savings
操作前	20	80
操作后	0	100

即操作前后账户数字复合逻辑运算，即操作前后checking和savings中余额的总数为100
隔离性(Isolation)：事务的隔离性是并发访问数据库时，数据库为每一个访问开启的事务，不能被其他事务的操作数据锁干扰，多个并发事务之前要互相隔离。
持久性(Durability)：一个事务一点被提交，它对数据库中数据的改变是永久性的，此时即使系统崩溃，修改的数据也不会丢失

一个实现了ACID的数据库，相比没有实现ACID的数据库，通常需要更强的CPU处理能力、更大内存和磁盘空间

1.3.1 隔离级别

在SQL标准中为隔离性定义了四种级别

未提交读(Read Uncommitted)：

在Read Uncommitted级别，事务中的修改，即使没有提交，对其他事务也都是可见的。例如在上例中，用户10233277如果在执行第3行sql

UPDATE savings SET balance = balance + 20 WHERE customer_id = 10233277;

时，事务没有提交，这时候恰好另外一个事务B去读取savings表中用户10233277的余额时，发现余额已经变成了100，但是这个事务会因为checkings表中用户10233277的余额不足导致无法向下执行后又进行了回滚，这样事务B就读取了此次的脏数据，称为脏读

脏读(Dirty Read)：事务可以读取未提交的数据
这个级别会导致很多问题，并且性能不会比其他级别好太多，实际应用很少

提交读(Read Committed)

一个事务开始时，只能读取已经提交的事务所做的修改。
Read Committed是大多数数据库系统的默认隔离级别（但MySQL不是）
Read Committed也叫Nonrepeatable Read(不可重复读),因为两次执行同样的查询，可能会得到不一样的结果
例如上例中，事务A比较复杂，前后读取同一条数据需要经历很长时间，事务A第一次读取到用户10233276的checkings表中的支票余额为0，这时候事务B对用户10233276的checkings表中的支票余额增加了100，那么事务A再次去读checkings表中用户10233276的支票余额，发现余额变成了100，这时候前后数据就不一致了，称为不可重复读

不可重复读和脏读的区别是：脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

可重复读(Repeatable Read)

MySQL的默认事务隔离级别
可重复读解决了脏读和不可重复读的问题。保证了在同一个事务中多次读取同样记录的结果是一致的。但是还是无法解决幻读的问题。

幻读(Phantom Read)：例如又另外一张表records记录这每个每一条从checkings表和savings表的转账记录，事务A很复杂需要很长的时间，第一次读取records中用户10233276的转账记录，发现是一条，这时候另外一个事务B增加了转账1元的操作，并顺利提交，这时候事务A再次去读表records记录，发现用户10233276又多了一条记录（幻行(Phantom Row)）

在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

幻读和不可重复读都是读取了另一条已经提交的事务（这点和脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

可串行化(Serializable)

隔离的最高级别，它通过强制事务串行执行，避免了前面说的幻读的问题
Serializable会在读取的每一行数据上都加上锁，所以可能导致大量的超时和锁争用的问题。实际很少应用。只有在非常需要确保数据一致性而且可以接受没有并发的情况下，才考虑该级别。

查看当前会话隔离级别：

select @@transaction_isolation;

（上述sql是mysql8.0以后的版本用的，如果版本小于8.0则把sql中的transaction_isolation替换成tx_isolation）

或

SHOW VARIABLES LIKE 'transaction_isolation';

查看系统当前隔离级别

SELECT @@GLOBAL.TRANSACTION_ISOLATION;

设置当前会话隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
Read Uncommitted	✔️	✔️	✔️	❌
Read Committed	❌	✔️	✔️	❌
Repeatable Read	❌	❌	✔️	❌
Serializable	❌	❌	❌	✔️

1.3.2 死锁

死锁：两个或者多个事务在同一资源上互相占用，并请求锁定对方占用的资源，从而导致恶性循环的现象

为了解决这种问题，数据库系统实现了各种死锁检测和死锁超时机制。比较复杂的存储引擎InnnoDB，遇到死锁的循环依赖可以立即返回一个错误。还有一种解决方式当查询时间达到锁等待超时的设定后放弃锁请求，这种方式不太好。InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚

死锁发生以后，只有部分或者完全回滚其中一个事务，才能打破死锁

1.3.3 事务日志

事务日志可以提高事务的效率
使用事务日志时，存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行行为记录持久在硬盘上的事务日志中，而不需要每次都将修改的数据本身持久到硬盘。事务日志持久化以后，内存中被修改的数据在后台可以慢慢地刷回磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志(Write-Ahead Logging)，修改数据需要写两次磁盘
如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具体的恢复方案是则视存储引擎而定。

1.3.4 MySQL中的事务

MySQL共提供了两种事务型存储引擎：InnnoDB和NDB Cluster。还有一些第三方存储引擎也支持事务，比较知名的有XtraDB和PBXT。

自动提交(AUTOCOMMIT)

MySQL默认采用自动提交。如果不是显式地开始一个事务，每个查询都被当作一个事务执行提交操作。在当前连接中，可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式：

SHOW VARIABLES LIKE 'AUTOCOMMIT';

可以临时设置当前连接的AUTOCOMMIT：

SET AUTOCOMMIT = 0;

如果关闭当前连接后重新连接，那么临时设置就失效了。

当AUTOCOMMIT = 0或OFF时，所有的查询都是在一个事务中，直到显式地执行COMMIT或ROLLBACK，该事务结束，同时又开始一个新事务。AUTOCOMMIT对非事务型的表没有任何影响，例如MyISAM或者内存表，这类表没有COMMIT或ROLLBACK的概念，也可以说相当于一直处于AUTOCOMMIT启用的模式。

另外还有一些命令，在执行之前会强制执行COMMIT提交当前的活动事务。例如数据定义语言(DDL)中，如果是会导致大量数据改变的操作，比如ALTER TABLE、 LOCK TABLES等

在事务中混合使用存储引擎

MySQL服务器层不管理事务，事务是由下层的存储引擎实现的，所以在同一个事务中，使用多种存储引擎是不可靠的。
如果在事务中混合使用了事务型和非事务型的表（例如InnoDB和MyISAM），正常提交的情况下不会有问题。但是如果该事务需要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，很难修复。
如上例中，把savings表的存储引擎换为MyISAM

savings表中的数据：

checking表中的数据：

执行事务：

START TRANSACTION;
SELECT balance FROM checking WHERE customer_id = 10233277;
UPDATE savings SET balance = balance + 20 WHERE customer_id = 10233277;
UPDATE checking SET balance = balance - 20 WHERE customer_id = 10233277;
COMMIT;

执行信息：

checking表中的数据：

可以发现并没有发生变化
savings表中的数据：

可以发现用户10233277余额多了20，这样数据就不一致了。
所以，为每张表选择合适的引擎非常重要。

显式和隐式锁定

InnoDB采用的是两阶段锁定协议(two-phase locking protocol)。在事务执行过程中，随时都可以执行锁定，锁只哟欧在执行COMMIT或者ROOLBACK的时候才会释放，并且所有的锁是在同一时刻被释放。前面描述的锁都是隐形锁定，InnoDB会根据隔离级别在需要的时候自动加锁

InnoDB也支持通过特定的语句进行显式锁定：

SELECT ... LOCK IN SHARE
SELECT ... FOR UPDATE

实例：
会话1开启事务，并且用FOR UPDATE进行查询：

START TRANSACTION;
SELECT
	* 
FROM
	savings 
WHERE
	customer_id = 10233277 FOR UPDATE;

此时再去开启会话2，先用普通不加FOR UPDATE进行查询：

SELECT
	* 
FROM
	savings 
WHERE
	customer_id = 10233277;

结果没有任何问题：

再在会话2中使用FOR UPDATE进行查询：

SELECT
	* 
FROM
	savings 
WHERE
	customer_id = 10233277 FOR UPDATE;

等待一段事件后会显示超时：

也就是说在事务中使用，需要两个查询都用FOR UPDATE进行查询才能阻塞另一个去读。需要注意的是，where中的条件里必须是主键，否则会锁住整张表。

LOCK TABLES 、UNLOCK TABLES

LOCK TABLES命令是为当前线程锁定表。这里有2种类型的锁定

锁定类型	命令
读锁定	LOCK TABLES tablename READ
写锁定	LOCK TABLES tablename WRITE

读锁定

如果一个线程获得在一个表上的READ锁，那么该线程和所有其他线程都只能从表中读数据，不能进行任何写操作。
实例：
会话A：

LOCK TABLES savings READ;
SELECT
	* 
FROM
	savings;
UPDATE savings 

SET balance = 200 
WHERE
	customer_id = 10233277;

结果信息：

可以看到查询完全没有问题，只有写操作UPDATE被读锁阻塞住了

线程B

SELECT
	* 
FROM
	savings;
UPDATE savings 
SET balance = 200 WHERE customer_id = 10233277;

结果信息：

可以看到线程B查询也完全没有问题，同样写操作UPDATE被读锁阻塞住了
再测试一下INSERT

LOCK TABLES savings READ;
SELECT
	* 
FROM
	savings;
UPDATE savings -- SET balance = 200 
-- WHERE
-- 	customer_id = 10233277;
INSERT INTO savings ( customer_id, balance )
VALUES (10233278, 400);

可以看到是同样的结果。

线程A释放锁并执行更新操作

UNLOCK TABLES;
UPDATE savings 
SET balance = 300 
WHERE
	customer_id = 10233277;

结果信息：

同时一直等待读锁的线程B也执行完成，结果信息：

同时再测试用LOCAL修饰读锁：
线程A

LOCK TABLES savings READ LOCAL;
SELECT
	* 
FROM
	savings;
UPDATE savings 
SET balance = 200 
WHERE
	customer_id = 10233277;

结果信息：

可以看到写操作同样被阻塞住了
再用线程B去执行读写(SELECT UPDATE)操作：

SELECT
	* 
FROM
	savings;
UPDATE savings 
SET balance = 400 WHERE customer_id = 10233277;

结果信息：

在测试一下INSERT

LOCK TABLES savings READ LOCAL;
SELECT
	* 
FROM
	savings;
INSERT INTO savings ( customer_id, balance )
VALUES (10233278, 400);

结果

在线程B中进行INSERT

INSERT INTO savings ( customer_id, balance )
VALUES
	( 10233278, 400 );

同样被阻塞住了

因为数据库引擎是InnoDB，可以发现效果和上面不加LOCAL修饰是相同的不能UPDATE和INISERT，把表引擎更换为MyISAM，注意要删除表重新创建，

DROP TABLE
IF
	EXISTS savings;
	
CREATE TABLE savings(
customer_id int(10) UNSIGNED NOT NULL,
balance DOUBLE UNSIGNED NOT NULL DEFAULT 0 CHECK(balance >= 0),
PRIMARY KEY (customer_id)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;

INSERT INTO savings (customer_id, balance)
VALUES
		(10233276, 80),
		(10233277, 81);

更换完后，再次进行上面的测试
线程A

LOCK TABLES savings READ LOCAL;
SELECT
	* 
FROM
	savings;
INSERT INTO savings ( customer_id, balance )
VALUES (10233278, 400);

结果：

线程B

INSERT INTO savings ( customer_id, balance )
VALUES
	( 10233278, 400 );

结果：

奇怪的是当我不重新创建表再次去执行上述操作时：
线程A

UNLOCK TABLES;
LOCK TABLES savings READ LOCAL;
SELECT
	* 
FROM
	savings;
INSERT INTO savings ( customer_id, balance )
VALUES (10233278, 400);

结果：

线程B

INSERT INTO savings ( customer_id, balance )
VALUES
	( 10233278, 400 );

结果
竟然又被锁住了，也就是说LOCAL失效了，这个问题还没搞明白。

转载：https://blog.csdn.net/weixin_43249914/article/details/106794348

查看评论

小言_互联网的博客

小言_互联网的博客

个人资料

文章分类

文章存档

阅读排行

评论排行

推荐文章