数据库 SQL 基础
关系型数据库-SQL
事务(transaction)
什么是数据库事务
数据库事务是构成单一逻辑工作单元的操作集合：
- 数据库事务可以包含一个或多个数据库操作，但这些操作构成一个逻辑上的整体；
- 构成逻辑整体的这些数据库操作，要么全部执行成功，要么全部不执行；
- 构成事务的所有操作，要么全部对数据库产生影响，要么全部都不产生影响，即不敢事务是否执行成功，数据库总能保持一致性状态；
- 即使在数据库出现故障以及并发事务存在的情况下依然成立。
事务如何解决问题
事务使系统能够方便的进行故障恢复以及并发控制，从而保证数据库状态的一致性。
事务的ACID特性以及实现原理概述
- 原子性（Atomicity）
事务中的所有操作作为一个整体像原子一样不可分割，要么全部成功，要么全部失败。 - 一致性（Consistency）
事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态。一致性状态是指：- 系统的状态满足数据的完整约束（主码，参照完整性，check 约束等）。
- 系统的状态反应数据本应描述的现实世界的真是状态，比如转账前后两个账户的金额总和应该保持不变。
- 隔离性（Isolation）
并发执行的事务不会相互影响，其对数据库的影响和它们串行执行时一样。比如多个用户同时往一个账户转账，最后账户的结果应该和他们按先后次序转账的 结果一样。 - 持久性（Durability）
事务一旦提交，其对数据库的更新就是持久的。任何事务或系统故障都不会导致数据库丢失。
在事务的 ACID 特性中，C 即一致性是事务的根本追求，而对数据一致性的破坏主要来自两个方面：
- 事务的并发执行
并发控制技术保证了事务的隔离性，使数据库的一致性状态不会因为并发执行的操作破坏。 - 事务故障或系统故障
日志恢复技术保证了事务的原子性，使一致性状态不会因事务或系统故障被破坏。同时使已提交的对数据库的修改不会因为系统崩溃而丢失， 保证了事务的持久性。
并发异常与并发控制技术
常见的并发异常
- 脏写
指事务回滚了其他事务对数据项的已提交修改。 - 丢失更新
事务覆盖了其他事务对数据的已提交修改，导致这些修改好像丢失了一样。 - 脏读
一个事务读取了另一个事务未提交的数据。 - 不可重复读
一个事务对同一数据的读取结果前后不一致。脏读和不可重复读的区别在于：前者读取的是事务未提交的脏数据，后者读取的是事务已经提交的数据， 只不过因为数据被其他事务修改过导致前后两次读取的结果不一样。 - 幻读
事务读取某个范围的数据时，因为其他事务的操作导致前后两次读取的结果不一致。幻读和不可重复读的区别在于：不可重复读是针对确定的某一行数据而言， 而幻读是针对不确定的多行数据。因而幻读常出现在带有查询条件的范围查询中。
事务的隔离级别
- 事务具有隔离性，理论上来说事务之间的执行不应该相互产生影响，其对数据库的影响应该和他们串行执行时一样。
- 然而完全的隔离性会导致系统并发性能很低，降低对资源的利用率，因而实际上对隔离性的要去会有所放宽，这也会一定程度对数据库一致性要求将。
- SQL标准为事务定义了不同的隔离级别从低到高依次是：
- 读未提交（READ UNCOMMITED）
- 读已提交（READ COMMITED）
- 可重复读（REPEATABLE READ）
- 串行化（SERIALIZABLE）
事务的隔离级别越低，可能出现的并发异常越多，但是通常而言系统能提供的并发能力越强。
不同的隔离级别与可能的并发异常的对应情况如果下表所示，有一点需求要强调，这种对应关系只是理论上的，对于特定的数据不一定准确， 比如Mysql的Innodb存储引擎通过Next-Key Locking技术再可重复读级别就消除了幻读的可能。
| 可能导致的并发异常 | |||||
|---|---|---|---|---|---|
| 事务的隔离级别 | 脏写 | 脏读 | 不可重复读 | 幻读 | 丢失更新 |
| 读未提交 | 可能 | 可能 | 可能 | 可能 | |
| 读已提交 | 可能 | 可能 | 可能 | ||
| 可重复读 | 可能 | ||||
| 串行化 | |||||
所有事务隔离级别都不允许出现脏写，而串行化可以避免所有可能出现的并发异常，但是会极大的降低系统的并发处理能力。
事务隔离性的实现
并发控制技术是实现事务隔离性以及不同隔离级别的关键，实现方式有很多，按照其对可能冲突的操作采取的不同策略可以分乐观并发控制和悲观并发控制两大类：
- 乐观并发控制
对于并发执行可能冲突的操作，假定其不会真的冲突，允许并发执行，知道真正发生冲突时才去解决冲突，比如让事务回滚 - 悲观并发控制
对于并发执行可能冲突的操作，假定其必定发生冲突，通过让事务等待（锁）或者中止（时间戳排序）的方式使并行的操作串行执行
基于封锁的并发控制
核心思想：对于并发可能冲突的操作，比如 读-写 写-读 写-写 通过锁使它们互斥执行。
锁通常分为共享锁和排他锁两种类型：
- 共享锁（S）：事务T对数据A加共享锁，其他事务只能对A加共享锁但不能加排他锁
- 排他锁（X）：事务T对数据A加排他锁，其他事务对A既不能加共享锁也不能加排他锁
基于锁的并发控制流程：
- 事务根据自己对数据项进行的操作类型申请相应的锁（读申请共享锁，写申请排他锁）；
- 申请锁的请求被发送给锁管理器。锁管理器根据当前数据项是否已经有锁以及申请的和持有锁是否冲突决定是否为该请求授予锁；
- 若锁被授予，则申请锁的事务可以继续执行；若被拒绝，则申请的事务将进行等待，知道锁被其他事务释放。
可能出现的问题：
- 死锁：多个事务持有锁并互相循环等待其他事务的锁导致所有事务都无法继续进行
- 饥饿：数据项A一直被加共享锁，导致事务一直无法获取A的排他锁
对于可能发生冲突的并发操作，锁使它们由并行变为串行执行，是一种悲观的并发控制。
基于时间戳的并发控制
核心思想： 对于并发可能冲突的操作，基于时间戳排序规则选定某事物继续执行，其他事务回滚。
系统会在每个事务开始时赋予其一个时间戳，这个时间戳可以是系统时钟也可以是一个不断累加的计数器值，当事务回滚时为期赋予一个新的时间戳， 先开始的事务时间戳小于后开始事务的时间戳。
每一个数据项Q有两个时间戳相关的字段：
- W-timestamp（Q）： 成功执行write（Q）的所有事务的最大时间戳
- R-timestamp（Q）： 成功执行read（Q）的所有事务的最大时间戳
时间戳排序规则如下：
- 假设事务T发出read(Q),T的时间戳为TS a. 若TS（T）< W-timestamp(Q),则T需要读入的Q已被覆盖。此read操作将被拒绝，T回绝 b. 若TS（T）>= W-timestamp(Q),则执行read操作，同时把R-timestamp(Q)设置为TS（T）与R-timestamp(Q)中的最大值
- 假设事务T发出write（Q） a. 若TS(T)<R-timestamp(Q),write操作被拒绝,T回滚。 b. 若TS(T)<W-timestamp(Q),则write操作被拒绝,T回滚。 c. 其他情况:系统执行write操作,将W-timestamp(Q)设置为TS(T)。
基于时间戳排序和基于锁实现的本质一样：对于可能冲突的并发操作，以串行的方式取代并发执行，因而它也是一种悲观并发控制。 它们的区别主要有两点：
- 基于锁是让冲突的事务进行等待，而基于时间戳排序是让冲突的事务回滚
- 基于锁冲突事务的执行次序是根据它们申请锁的顺序，先申请的先执行；而基于时间戳排序是根据特定的时间戳排序规则。
基于有效性检查的并发控制
核心思想：事务对数据库的额更新首先在自己的工作空间进行，等到要写会数据库时才惊醒有效性检查，对不符合要求的事务进行回滚。
基于有效性检查的事务执行过程会被分为三个阶段：
- 读阶段：数据项被读入并保存在事务的局部变量中。所有write操作都是对局部变量进行，并不对数据库进行真正的更新
- 有效性检查阶段：对事务进行有效性检查，判断是否可以执行write操作而不违反可串行性。如果失败，则回滚该事务
- 写阶段：事务已通过有效性检查，则将临时变量中的结果更新到数据库中
有效性检查通常也是通过对事务的时间戳进行比较完成的，不过和基于时间戳排序的规则不一样。 该方法允许可能冲突的操作并发执行，因为每个事务操作的都是自己工作空间的局部变量，知道有效性检查阶段发现了冲突才回滚。因而这是一种乐观的并发策略。
基于快照隔离的并发控制
核心思想： 数据库为每个数据项维护多个版本（快照），每个事务只对属于自己的私有快照进行更新，在事务真正提交前进行有效性检查， 使得事务正常提交更新或者失败回滚。
快隔离是多版本并发控制（mvcc）的一种实现方式。
由于快照隔离导致事务看不到其他事务对数据项的更新，为了避免出现丢失更新问题，可以采用以下两种方案避免：
- 先提交者获胜： 对于执行该检查的事务 T，判断是否有其他事务已经将更新写入数据库，是则 T 回滚否则 T 正常提交
- 先更新者获胜： 通过锁机制保证第一个获得的事务提交其更新，之后视图更新的事务终止
事务间可能冲突的操作通过数据项的不同版本的快照相互隔离，到真正要写入数据库时才进行冲突检测。因而这也是一种乐观并发控制。
故障与故障恢复技术
为什么需要故障恢复技术
数据库运行过程中可能会出现故障，这些故障包括事务故障和系统故障两大类
- 事务故障： 比如非法输入，系统出现死锁，导致事务无法继续执行
- 系统故障： 比如由于软件漏洞或软件错误导致系统崩溃或中止
这些故障可能会对事故和数据库状态造成破坏，因而必须提供一种技术来对各种故障进行会发，保证数据库一致性，事务的原子性以及持久性。
数据库通常以日志的方式记录数据库的操作从而在故障时进行恢复，因而可以称之为日志恢复技术。
事务的执行过程以及可能产生的问题
事务的执行过程可以简化如下：
- 系统会为每个事务开辟一个私有工作区
- 事务读操作将从磁盘中拷贝数据项到工作区中，在执行写操作前所有的更新都作用于工作区中的拷贝
- 事务的写操作将把数据输出到内存的缓冲区中，等到何时的时间再由缓冲区管理器将数据写入到磁盘
由于数据库存在立即修改和延迟修改，所以在事务执行过程中可能存在以下情况：
- 在事务提交前出现故障，但是事务对数据库的部分修改已经写入磁盘数据库中。这导致事务的原子性被破坏
- 在系统崩溃前事务已经提交，但数据还在内存缓冲区中，没有写入磁盘。系统恢复时将丢失此次已提交的修改。这是对事务持久性的破坏
日志的种类和格式
- <T,X,V1,V2>:描述一次数据库写操作,T 是执行写操作的事务的唯一标识,X 是要写的数据项,V1 是数据项的旧值,V2 是数据项的新值。
- <T,X,V1>:对数据库写操作的撤销操作,将事务 T 的 X 数据项恢复为旧值 V1。在事务恢复阶段插入。
- <T start>: 事务 T 开始
- <T commit>: 事务 T 提交
- <T abort>: 事务 T 中止
关于日志,有以下两条规则
- 系统在对数据库进行修改前会在日志文件末尾追加相应的日志记录。
- 当一个事务的commit日志记录写入到磁盘成功后,称这个事务已提交,但事务所做的修改可能并未写入磁盘
日志恢复的核心思想
- 撤销事务undo：将事务更新的所有数据项恢复为日志中的救治，事务撤销完毕时将插入一条
<T abort>记录
系统从崩溃中恢复时先进行redo再进行undo - 重做事务redo：将事务更新的所有数据项恢复为日志中的新值
事务正常回滚/因事务故障中止将进行redo
总结
事务是数据库系统机型并发控制的基本单位，是数据库系统进行故障恢复的基本单位，从而也使保持数据状态一致性的基本单位。
ACID是事务的基本特性，数据库消停时通过并发控制技术和日志恢复技术来保证事务的ACID，从而可以得到如下关于数据库事务的概念体系结构
文章摘抄自
操作类型
SQL语言共分为四大类：数据查询语言DQL，数据操作语言DML，数据定义语言DDL，数据控制语言DCL。
-
数据查询语言DQL
DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块- SELECT <字段名表>
- FROM <表或视图名>
- WHERE <查询条件>
-
数据操纵语言DML
DML主要有三种形式：- 插入： INSERT
- 更新： UPDATE
- 删除： DELETE
-
数据定义语言DDL
DDL用来创建数据库中的各种对象–表、视图、同义词、聚簇等如：1
2-- 表 视图 索引 同义词 簇
CREATE TABLES/VIEW/INDEX/SYN/CLUSTERDDL 操作是隐形提交的！！！不能 rollback。
-
数据控制语言DCL
DCL用来授予或收回访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：- GRANT：授权
- ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点
回滚–rollback
回滚命令时数据库状态回到上次最后提交的状态 - COMMIT [WORK] ： 提交
在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。提交数据有三种类型：显示提交、隐式提交及自动提交。- 显示提交
用COMMIT命令直接完成的提交为显示提交。格式为：1
COMMIT
- 隐式提交
用SQL命令间接完成的提交为隐式提交。命令是：1
ALTER,AUDIT,COMMENT,CONNECT,CREATE,DISCONNECT,DROP,EXIT,GRANT,NOAUDIR,QUIT,REVOKE,RENAME
- 自动提交
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交，格式为：1
SET AUTOMCOMMIT ON; Mysql--sql语句
- 显示提交