[华为云在线课程][7天玩转MySQL基础实战营][day05事务和锁][学习笔记]

ACID

事务的定义

A database transaction symbolizes a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions. A transaction generally

represents any change in a database. Transactions in a database environment have two main purposes:

To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure, when execution stops (completely or partially) and many operations upon a database remain

uncompleted, with unclear status.

To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the programs’ outcomes are possibly erroneous.

数据库事务通常包含了对数据库的一系列的读/写操作。

数据库通过事务（transaction）来支持ACID的特性，保证整个数据库的有效性和可靠性。

什么是ACID

In computer science, ACID (atomicity, consistency,isolation,durability) is a set of properties of database transactions intended to guarantee validity even in the event of errors, power failures, etc. In the context of database, a

sequence of database operations that satisfies the ACID properties (and these can be perceived as a single logical operation on the data) is called a transaction. For example, a transfer of funds from one bank account to another, even

involving multiple changes such as debiting one account and crediting another, is a single transaction.

ACID是一组特性，它们定义了即使是在错误，断电等极端异常情况下，数据库事务操作应该满足的特性。不同数据库对ACID的支持程度可能存在不同。

ACID具体规范

A - Atomically（原子性）

事务包括的语句集必须要么都执行成功，要么都执行失败。任何一个语句的失败应该使得整个事务都失败

C - Consistency（一致性）

事务只能把数据库从一个有效状态改变到另一个有效状态，期间所有的数据改变都要符合所有预定义的规则

I - Isolation（隔离性）

事务即使并行执行，它们对数据库的修改应该和事务是串行执行的效果一样

D - Durability（持久性）

事务一旦提交，它的修改和提交状态不会因为系统的任何问题而消失

事务

事务的生命周期

都以MySQL的事务为例

事务一般有三种开启方式

BEGIN/START TRANSACTION

AUTOCOMMIT = 0

AUTOCOMMIT = 1，一条语句即一个事务

事务的结束一般有四种方式

COMMIT

所有修改都会生效

ROLLBACK

所有修改都会失效，好像事务从未执行过

当前连接断开，回滚当前事务

执行某些特定语句，如DDL

原有的事务会被隐式提交，再执行DDL

BEGIN/START TRANSACTION; ... ... COMMIT/ROLLBACK;

SET autocommit = 0; DML1; ... COMMIT/ROLLBACK;

SET autocommit = 1; SQL STATEMENT; -- 单语句自动提交

事务的隔离级别

隔离级别解决了isolation的问题，MySQL/InnoDB支持SQL92标准定义的四种隔离级别：

READ UNCOMMITTED – 不建议使用

事务内所有的不加锁读都有可能看到不可预期的过期的数据

READ COMMITTED – 建议使用

事务内所有的一致性读，读到的是每个读语句自己建立的最新快照

REPEATABLE READ – 默认隔离级别

事务内所有的一致性读，读到的数据都是事务内第一个读操作建立的快照

SERIALIZABLE – 一般不需要使用

类似 REPEATABLE READ ，但是对于事务内的读，都会隐式的转换成加锁读

事务的可见性

事务一定能看到自己的修改

事务可能看得到已提交的数据

事务可能看得到未提交的修改

事务的可见性 - Repeatable Read（1）

事务看到的始终是本事务第一次读时时候能看到的内容

事务的可见性 - Repeatable Read（2）

事务看到的始终是本事务第一次读时时候能看到的内容

事务的可见性 - Read Committed（1）

事务看到的始终时每个读开始时刻已提交的数据

事务的可见性 - Read Committed（2）

事务看到的始终时每个读开始时刻已提交的数据

事务的可见性 - Read Committed（3）

事务看到的始终时每个读开始时刻已提交的数据

事务的可见性 - Read Uncommitted

事务看得到当前最新的未提交数据（谨慎使用）

事务的可见性 - Serializable

事务需要对读到的数据进行加锁（谨慎使用）

锁

锁定义

摘自Wikipedia：Record locking

Record locking is the technique of preventing simultaneous access to data in a database, to prevent inconsistent results.

To allow several users to edit a database table at the same time and also prevent inconsistencies created by unrestricted access, a single record can be locked when retrieved for editing or updating. Anyone attempting to retrieve the same

record for editing is denied write access because of the lock (although, depending on the implementation, they may be able to view the record without editing it). Once the record is saved or edits are canceled, the lock is released.

Records can never be saved so as to overwrite other changes, preserving data integrity.

In database management theory, locking is used to implement isolation among multiple database users. This is the “I” in the acronym ACID

数据库系统使用锁机制来支持事务的并发控制和隔离性

数据库系统使用的锁包含多个层面的，不同类型的锁机制

用户需要关心的主要包括两种，表锁和记录锁

锁类型

锁类型 - 显示的表锁

显示表锁有两种类型

READ - 持有者只能读加锁的表，不同会话可以共同持有读锁

WRITE - 只有持有者可以读写加锁的表，其他会话都不能访问加锁的表

语法：

LOCK TABLES t1 READ [, t2 READ [, t3 WRITE]]…;

UNLOCK TABLES;

所有当前会话要访问表需要在同一个LOCK TABLES语句里面加锁

加锁语句会隐式的提交当前未完成事务

加锁语句会隐式的释放当前已持有的表锁

加锁粒度太大，不利于开发，谨慎使用

锁类型 - 隐示的表锁（1）

隐式表锁一般对用户不可见，用户不可操作，但能感知到，主要用于数据库内部并发同步保证正确性

锁类型 - 隐示的表锁（2）

隐式表锁一般对用户不可见，用户不可操作，但能感知到，主要用于数据库内部并发同步保证正确性

锁类型 - 行锁

行锁存在于InnoDB存储引擎层

行锁主要类型

记录锁

间隙锁

插入意向锁

…

行锁模式

共享锁

互斥锁

加锁语句

DML 语句

SELECT 语句，带加锁提示

…

一致性读和加锁读

InnoDB实现两种不同的读数据机制

一致性不加锁读

不加锁

基于多版本机制

读写可执行

读取的是指定时间点的快照内容，而不一定是最新内容

加锁读

读取最新数据

基于锁管理机制，按要求加锁，锁冲突需等待

可能产生死锁

SELECT … LOCK IN SHARE MODE;

SELECT … FOR UPDATE;

锁冲突

锁冲突 - INSERT和SELECT（1）

INSERT和读可能会冲突，例如先读后插入场景

锁冲突 - INSERT和SELECT（2）

INSERT和读可能会冲突，例如先读后插入场景

锁冲突 - INSERT和INSERT

INSERT和INSERT可能会冲突

锁冲突 - INSERT和DELETE（1）

INSERT和DELETE可能会冲突，例如先INSERT再DELETE场景

锁冲突 - INSERT和DELETE（2）

INSERT和DELETE可能会冲突，例如先INSERT再DELETE场景

死锁检测

MySQL文档对死锁的说明：A deadlock is a situation where different transactions are unable to proceed because each holds a lock that the other needs. Because both transactions are waiting for resource to become available, neither ever release the

locks it holds.

死锁发生条件

多个事务并发

每个事务持有部分资源（行锁），需要申请更多的资源（行锁）

一旦申请存在相互依赖，资源等待构成环，即形成死锁

死锁检测

MySQL/InnoDB内部默认会进行死锁检测，避免事务长时间等待

一旦检测到死锁，选择一个事务进行回滚，其他事务可以继续

禁用死锁检测

某些场景下，可以提高性能

通过innodb_lock_wait_timeout来控制死锁超时

UPDATE语句导致的死锁检测和处理

最佳实践

高效使用事务

高效使用事务 - 隔离级别的选择

确认隔离级别对并发DML的影响：

最常用的隔离级别是REPEATABLE READ和READ COMMITTED

READ UNCOMMITTED和SERIALIZABLE请谨慎使用

高效使用事务 - 优化小事务

频繁的单语句DML事务不利于性能

考虑将可以合并的DML在一个事务提交

多条语句合为一条语句

高效使用事务 - 大事务和小事务

避免大事务

主要指事务包含的语句很多，或者语句执行耗时很长

将大事务转换成若干小事务提交，提高可靠性

优化大事务逻辑，如删除全表数据改为TRUNCATE TABLE

注意DDL执行的耗时，以及它对资源、复制等其他问题的影响

避免长事务

主要指事务目前不繁忙，但是一直没有提交

长事务占用连接资源

长事务可能占用系统资源，如磁盘空间等

长事务可能导致过期数据一直无法回收

高效使用事务 - 其他优化

确定是不是只能用只读事务

START TRANSACTION READ ONLY;

可以提高性能

索引对加锁的影响

如果表上没有索引，一旦涉及到范围加锁，可能就整张表被锁住

如果表上有唯一索引，唯一索引的加锁粒度更小

如果使用二级索引扫描进行更新，二级索引和聚簇索引记录都要加锁

避免死锁

死锁导致事务回滚，降低系统效率，浪费系统资源，影响业务体验

最主要的原则是避免死锁条件的满足

事务尽量小，比如只更新一条记录，但不代表不会死锁

事务尽量短，缩短或这避免冲突时间窗

事务更新多张表时，用同一个顺序更新不同的表

事务更新一张表内的多行时，用同一个顺序更新不同的行

另一个角度是减少事务的加锁

避免事务（长时间）锁一个范围

如果一致性读可以满足要求，尽量少用加锁读

需要加锁读的时候，尽量使用READ COMMITTED隔离级别有利于减少死锁的产生

使用索引扫描，减少事务加锁的数量

如何监测和处理死锁

应用程序做好重新启动事务的准备，应对死锁场景

SHOW ENGINE INNODB STATUS;/ 错误日志

根据死锁信息，调整应用程序逻辑

MySQL

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。