数据库系统之锁与事务

传统的操作系统支持的典型的文件处理系统，记录被永久存储在多个不同的文件中，人们编写不同的应用程序来将记录从有关文件中取出或加入。在数据库管理系统出现以前，各个组织通常都采用这样的系统来存储信息。在文件处理系统中存储组织信息的主要弊端包括:
数据的冗余和不一致；数据访问困难；数据孤立；并发访问异常；安全性问题等，这些问题促进了数据库系统的发展。数据库是一个以某种有组织的方式存储的数据集合。

数据库管理系统由一个互相关联的数据的集合和一组用以访问这些数据的程序组成。这个数据集合通常称作数据库。DBMS的主要目标是要提供一种可以方便、高效地存取数据库信息的途径。
数据库系统体系结构可分为微观结构(三级模式结构)；宏观结构：单用户结构，主从式结构，客户端服务器结构，分布式结构。

数据库结构的基础是数据模型。数据模型是一个描述数据、数据联系、数据语义以及一致性约束的概念工具的集合。数据模型提供了一种描述物理层、逻辑层以及视图层数据库设计的方式。数据模型按不同的应用层次分成三种类型：分别是概念数据模型、逻辑数据模型、物理数据模型。

数据库主要产品包括：Oracle、DB2、MySQL、Microsoft SQL Server、Microsoft Access等。

数据库系统划分为不同的模块，每个模块完成整个系统的一个功能。数据库系统的功能部件大致可分为存储管理器和查询处理部件。查询处理器组件包括:
●DDL解释器(DDL interpreter),解释DDL语句并将这些定义记录在数据字典中。
●DML编译器(DML compiler),将查询语言中的DML语句翻译为一个执行方案，包括一系列查询执行引擎能理解的低级指令。
一个查询通常可被翻译成多种等价的具有相同结果的执行方案的一-种。DML编译器还进行查询优
化(query optimization)，也就是从几种选择中选出代价最小的-种。
●查询执行引擎( query evaluation engine)，执行由DML编译器产生的低级指令。

存储管理器是数据库系统中负责在数据库中存储的低层数据与应用程序以及向系统提交的查询之间提供接口的部件。存储管理器负责与文件管理器进行交互。原始数据通过操作系统提供的文件系统存储在磁盘上。存储管理器将各种DML语句翻译为底层文件系统命令。因此，存储管理器负责数据库中数据的存储、检索和更新。存储管理部件包括:
●权限及完整性管理器(authorization and integrity manager)，它检测是否满足完整性约束，并检查试图访问数据的用户权限。
●事务管理器(transaction manager)，保证即使发生了故障，数据库也保持在一致的状态，并保证并发事务的执行不发生冲突。
●文件管理器(file manager)，管理磁盘存储空间的分配，管理用于表示磁盘上所存储信息的数据结构。
●缓冲区管理器(buffer manager)，负责将数据从磁盘上取到内存中来，并决定哪些数据应被缓冲存储在内存中。缓冲区管理器是数据库系统中的一个关键部分，因为它使数据库可以处理比内存更大的数据。

一、事务管理器

数据库事务是构成单一逻辑工作单元的操作集合。数据库事务可以包含一个或多个数据库操作,但这些操作构成一个逻辑上的整体。构成逻辑整体的这些数据库操作要么全部执行成功，要么全部不执行。一个典型的数据库事务如下所示

BEGIN TRANSACTION //事务开始
SQL1
SQL2
COMMIT/ROLLBACK //事务提交或回滚

1.事务特性

原子性(Atomicity):事务中的所有操作作为一个整体像原子一样不可分割， 要么全部成功，要么全部失败。
一致性(Consistency):事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态。一致性状志是指:1.系统的状态满足数据的完整性约束(主码,参照完整性,check约束等) 2.系统的状态反应数据库本应描述的现实世界的真实状态，比如转账前后两个账户的金额总和应该保持不变。
隔离性(lsolation):并发执行的事务不会相互影响,其对数据库的影响和它们串行执行时一样。比如多个用户同时往一个账户转账,最后账户的结果应该和他们按先后次序转账的结果一样。
持久性(Durabilty):事务一旦提交,其对数据库的更新就是持久的。任何事务或系统故障都不会导致数据丢失。
在事务的ACID特性中,C即一致性是事务的根本追求,而对数据一致性的破坏主要来自两个方面
1.事务的并发执行;2.事务故障或系统故障
数据库系统是通过并发控制技术和日志恢复技术来避免这种情况发生的。
并发控制技术保证了事务的隔离性,使数据库的一致性状态不会因为并发执行的操作被破坏。
日志恢复技术保证了事务的原子性,使一致性状态不会因事务或系统故障被破坏。同时使已提交的对数据库的修改不会因系统崩清而丢失,保证了事务的持久性。

2.出现的问题

脏写：指事务回滚了其他事务对数据项的已提交修改。
丢失更新：指事务覆盖了其他事务对数据的已提交修改，导致这些修改好像丢失了一样。
脏读：指一个事务读取了另一个事务未提交的数据
不可重复读：在第一个事务的两次读数据之间，第二个事务修改，第一个事务两次读到的数据可能不一样。一个事务内两次读到的数据不同，因此称为不可重复读。
不可重复读和脏读的区别是:脏读是读到未提交的数据；而不可重复读读到的是已经提交的数据，但是其违反了数据库事务一致性的要求。
一般来说不可重复读的问题是可以接受的，因为其读到的是已经提交的数据，不会带来很大的问题。很多数据库厂商将其数据库事务的默认隔离级别设置为READ COMMITTED，在这种隔离级别下允许不可重复读的现象。
InnoDB存储引擎中，通过使用Next-Key Lock算法来避免不可重复读的问题。在Next-KeyLock算法下，对于索引的扫描，不仅仅是锁住扫描到的索引，而且还锁住这些索引覆盖的范围(gap)。 因此对于这个范围内的插入都是不允许的。这样就避免了另外的事务在这个范围内插入数据导致的不可重复读的问题。InnoDB存储引擎的默认事务隔离级别是READ REPEATABLE。
幻读：指事务读取某个范围的数据时，因为其他事务的操作导致前后两次读取的结果不一致。 幻读和不可重复读的区别在于：不可重复读是针对确定的某一行数据而言，而幻读是针对不确定的多行数据。幻读通常出现在带有查询条件的范围查询中。

数据库运行过程中可能会出现故障,这些故障包括事务故障和系统故障两大类
事务故障:比如非法输入,系统出现死锁导致事务无法继续执行。
系统故障:比如由于软件漏洞或硬件错误导致系统崩溃或中止。
这些故障可能会对事务和数据库状态造成破坏，因而必须提供一种技术来对各种故障进行恢复,保证数据库一致性,事务的原子性以及持久性。数据库通常以日志的方式记录数据库的操作从而在故障时进行恢复。.
由于数据库存在立即修改和延迟修改,所以在事务执行过程中可能存在以下情况:
●在事务提交前出现故障,但是事务对数据库的部分修改已经写入磁盘数据库中，导致了事务的原子性被破坏。
●在系统崩溃前事务已经提交,但数据还在内存缓冲区中,没有写入磁盘。系统恢复时将丢失此次已提交的修改，这是对事务持久性的破坏。

3.解决措施

事务使系统能够更方便的进行故障恢复以及并发控制,从而保证数据库状态的一致性。事务是数据库系统进行并发控制的基本单位，也是数据库系统进行故障恢复的基本单位。ACID是事务的基本特性，数据库系统是通过并发控制技术和日志恢复技术来对事务的ACID进行保证的。

并发控制技术是实现事务隔离性以及不同隔离级别的关键。实现方式有很多，按照其对可能冲突的操作采取的不同策略，可以分为乐观并发控制和悲观并发控制两大类。

乐观并发控制:对于并发执行可能冲突的操作,假定其不会真的冲突,允许并发执行,直到真正发生冲突时才去解决冲突,比如让事务回滚。

悲观并发控制:对于并发执行可能冲突的操作,假定其必定发生冲突,通过让事务等待(锁)或者中止(时间戳排序)的方式使并行的操作串行执行。

二、隔离级别

事务具有隔离性，事务之间的执行不应该相互产生影响，其对数据库的影响应该和它们串行执行时一样。然而完全的隔离性会导致系统并发性能很低，降低对资源的利用率，因而实际上对隔离性的要求会有所放宽。这也会一定程度造成对数据库一致性要求降低。事务的隔离级别是为了解决脏读、不可重复读、幻读问题。SQL标准为事务定义了不同的隔离级别，从低到高依次是
●未提交读(read umommited):允许读取未提交数据。这是SQL允许的最低一致性级别。
●提交读(read omited):只允许读取已提交数据。
 ●可重复读(repeatable read): 只允许读取已提交数据，而且在一个事务两次读取一个数据项期间，其他事务不得更新该数据。例如当一个事务在查找满足某些条件的数据时，它可能找到一个已提交事务插入的一批数据，但可能找不到该事务插入的其他数据。
 ●串行化(serializable)：需要加锁实现，强制事务串行执行。

MYSQL默认隔离级别是可重复读。

以上所有隔离性级别都不允许脏写(dity write), 即如果一个数据项已经被另外一个尚未提交或中止的事务写入，则不允许对该数据项执行写操作。
事务的隔离级别越低，可能出现的并发异常越多，系统能提供的并发能力越强。不同的隔离级別与可能的并发异常的对应情况如下表所示，这种对应关系只是理论上的，对于特定的数据库实现不一定准确。比如mysql的Innodb存储引擎通过Next-Key Locking技术在可重复读级别就消除了幻读的可能。

隔离级别是如何实现的？主要依靠锁机制和MVCC实现。

三、锁类型

锁机制用于管理对共享资源的并发访问。锁按读写分为共享锁和排他锁两种类型
●共享锁(S):事务T对数据A加共享锁,其他事务只能对A加共享锁但不能加排他锁。
●排他锁(X):事务T对数据A加排他锁，其他事务对A既不能加共享锁也不能加排他锁
可能出现的问题:
●死锁:多个事务持有锁并互相循环等待其他事务的锁导致所有事务都无法继续执行。
●饥饿:数据项A一直被加共享锁，导致事务一直无法获取A的排他锁。
对于可能发生冲突的并发操作，锁使它们由并行变为串行执行，是一种悲观的并发控制。

锁根据粒度主要分为表锁、页锁和行锁。不同的存储引擎拥有的锁粒度都不同。MYISAM默认采用表级锁，InnoDB默认采用行级锁。

表级别的锁定是MySQL各存储引擎中最大粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，可以很好的避免死锁问题。锁定粒度大所带来最大的负面影响就是出现锁定资源争用的概率高，降低并发度。使用表级锁定的主要是MyISAM，MEMORY, CSV等非事务性存储引擎。

页级锁定是MySQL中比较独特的锁定级别。特点是锁定颗粒度介于行级锁定与表级锁之间, 所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也介于二者之间。页级锁定和行级锁定一样，会发生死锁。
在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也会提升。使用页级锁定的主要是BerkeleyDB存储引擎。

行级锁定最大的特点就是锁定对象的粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高需要高并发应用系统的整体性能。但由于锁定资源的粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。使用行级锁定的主要是InnoDB存储引擎。
InnoDB存储引擎有3种行锁的算法:
Record Lock: 单个行记录上的锁。
Gap Lock: 间隙锁，锁定一个范围，但不包含记录本身。
Next-Key Lock: Gap Lock+Record Lock，锁定一个范围， 并且锁定记录本身。.

总结
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

InnoDB支持多种粒度的锁，也就是行锁和表锁。为了支持多粒度锁定，InnoDB 存储引擎引入了意向锁(Intention Lock)。如果没有意向锁，当已经有人使用行锁对表中的某行进行修改时，如果另外一个请求要对全表进行修改，就需要对所有的行是否被锁定进行扫描，效率非常低。在引入意向锁之后，当有人使用行锁对表中的某行进行修改之前， 会先为表添加意向互斥锁(IX) , 再为行记录添加互斥锁(X) ,这时如果有人尝试对全表进行修改就不需要判断表中的每行数据是否被加锁了 ，只需要通过等待意向互斥锁被释放就可以了。意向锁不会阻塞全表扫描之外的任何请求，它的主要目的是表示是否有人请求锁定表中的某一行数据。意向锁也分为两种:
■意向共享锁(IS) :事务想获得表中某些记录的共享锁，需要在表上先加意向共享锁。
■意向互斥锁(IX) :事务想获得表中某些记录的互斥锁，需要在表上先加意向互斥锁。

Innodb行锁是怎么实现的

通过给索引上的索引项加锁来实现。只有通过索引条件检索数据才使用行级锁，否则将使用表级锁。

四、其他锁

乐观锁：假设数据的更新在大多数时候不会产生冲突，所以数据库只在更新操作提交的时候对数据检测冲突，如果存在冲突，则数据更新失败。
乐观锁实现方式：一般通过版本号和CAS算法实现。
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。通俗讲就是每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都上锁。
悲观锁的实现方式：通过数据库的锁机制实现，对查询语句添加for updata。

死锁：指两个或两个以上事务在执行过程中，因为争夺锁资源而造成的互相等待的现象。若无外力作用，事务都将无法推进下去。在MySQL中，MyISAM是一次获得所需的全部锁，要么全部满足，要么等待，所以不会出现死锁。在InnoDB存储引擎中，除了单个SQL组成的事务外，锁都是逐步获得的，所以存在死锁问题。
如何避免发生死锁：

●尽量使用较低的隔离级别
●尽量使用索引访问数据，使加锁更加准确，减少锁冲突的机会
●合理选择事务的大小，小事务发生锁冲突的概率更低
●如果不同的程序并发存取多个表，尽量以相同的顺序访问表。
发生死锁时的解决办法：解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。
除了超时机制，当前数据库还采用wait-for graph (等待图)的方式来进行死锁检测。较之超时的解决方案，是一种更主动的死锁检测方式。InnoDB存储引擎也采用这种方式。wait-for graph要求数据库保存以下两种信息:
●锁的信息链表;
●事务等待链表;
通过上述链表可以构造出一张图，在这个图中若存在回路就代表存在死锁，因此资源间相互发生等待。这是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁。通常来说InnoDB存储引擎选择回滚undo量最小的事务。
 

五、各种log

innodb事务日志包括redo log和undo log。redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。

1.redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。
2.undo log主要分为两种：

insert undo log
代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃

update undo log
事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

对MVCC有帮助的实质是update undo log ，undo log实际上就是存在rollback segment中旧记录链。不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条链表，undo log的链首就是最新的旧记录，链尾就是最早的旧记录。

MVCC详解 - xuwc - 博客园

binlog(BinaryLog) :
二进制日志文件就是常说的binlog。二进制日志记录了MySQL所有修改数据库的操作，然后以二进制的形式记录在日志文件中,其中还包括每条语句所执行的时间和所消耗的资源,以及相关的事务信息。
 

六、MVCC

当前读：读取的是数据库的最新版本，并且在读取时要保证其他事务不会修该当前记录，所以会对读取的记录加锁。
快照读：不加锁读取操作即为快照读，使用MVCC来读取快照中的数据，避免加锁带来的性能损耗。

InnoDB默认的隔离级别是REPEATABLE READ, 可解决脏读、可重复读、幻读等问题，使用的是MVCC。MVCC(多版本并发控制)是一种控制并发的方法，主要用来提高数据库的并发性能。作用就是在避免加锁的情况下最大限度解决读写并发冲突的问题，用更好的方式去处理读写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。可以实现提交读和可重复度两个隔离级别。它最大的优点是读不加锁，因此读写不冲突，并发性能好。而这个读指的就是快照读，而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现。InnoDB实现MVCC，多个版本的数据可以共存，基于以下技术及数据结构：
1.隐藏列: InnoDB中每行数据都有隐藏列，每行记录除了自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段

• DB_TRX_ID
  6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
• DB_ROLL_PTR
  7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
• DB_ROW_ID
  6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

2.基undo log的版本链:每行数据的隐藏列中包含了指向undo log的指针，每条undo log也会指向更早版本的undo log，形成一条版本链。
3. ReadView :通过隐藏列和版本链, MySQL可以将数据恢复到指定版本。具体恢复到哪个版本需要根据ReadView来确定。Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)，之后再进行读操作时，会将读取到的数据中的事务id与快照比较，判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

七、数据库设计三大范式

第一范式(1NF) :
确保每列保持原子性，数据表中的所有字段值都是不可分解的原子值。即数据库表的每一列都是不可分割的原子数据项，而不能是集合,数组,记录等非原子数据项。
实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式表中的每个域值只能是实体的一个属性或一个属性的一部分。第一范式就是无重复的域。
第二范式(2NF):
确保表中的每列都和主键相关。第二范式是在第一范式的基础上建立起来的，满足第二范式必须先满足第一范式。 第二范式要求数据库表中的每个实例或记录可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。
例如在员工表中的身份证号码即可实现每个一员工的区分，该身份证号码即为候选键，任何一个候选键都可以被选作主键。在找不到候选键时,可额外增加属性以实现区分。如果在员工关系中，没有对其身份证号进行存储，而姓名可能会在数据库运行的某个时间重复而无法区分出实体时，设计辟如ID等不重复的编号以实现区分。被添加的编号或ID选作主键。
第三范式(3NF) :
确保每列都和主键列直接相关而不是间接相关。在2NF基础上，任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)。第三范式是第二范式的一个子集，满足第三范式必须满足第二范式。第三范式要求一个关系中不包含在其它关系已包含的非主关键字信息。
例如存在一个部门信息表，其中每个部门有部门编号、部门名称、部门简介等信息。那么在员工信息表中列出部编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式也应该构建它，否则就会大量的数据冗余。