数据库事务、锁死、范式

数据库事务和范式

什么是事务
事务(Transaction)是并发控制的基本单位。所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

数据库事务四大特性：原子性、一致性、分离性、持久性

原子性（Atomicity）：一个事务它所做的对数据修改操作要么完整执行，要么完全不执行。

一致性（consistency）：在一个事务执行之前和执行之后数据库都必须处于一致性状态。

隔离性（Isolation)：对于任何一对事务T1和T2，在事务T1看来，T2要么在T1开始之前执行，要么在T1完成之后才开始执行，这样，每个事务都感觉不到系统中有并发事务执行。【通过锁实现】

持久性（Durability)：一个事务一旦成功完成，它对数据库的改变必须是永久的，即使是在系统遇到故障的情况下也不会丢失，数据的重要性决定了事物的持久性的重要。

原子性：（all or nothing的问题）指一个事务中，包含若干个数据操作，这些操作是一个整体，要么一起完成，要么一起不完成，不能只完成其中的一部分。比如你去银行转帐，从一个账户转帐到另一个账户，这是一个完整的事务，包括两个操作，从你第一个账户读数，增加到第二个账户，并减去第一个账户中的钱，如果这些操作有一个失败了，整个事务都必须还原成最开始的状态。
一致性：是指数据库从一个完整的状态跳到另一个完整的状态，是用于保护数据库的完整性的。比如你修改数据库的某个外键值，如果没有和相应的主键对应，就违反了数据库的一致性。另外，还有读一致性，如：你刚写入一个数到数据库中，但还没有提交，这时候有人要读这个数，就涉及完整性问题，要保证读取的数据在整个数据库中是处于和其他数据一致的一个状态。

 如何标识一个事务
在SQL Server中，通常事务是指以BEGIN TRAN开始，到ROLLBACK或一个相匹配的COMMIT之间的所有语句序列。ROLLBACK表示要撤消（ U n d o）该事务已做的一切操作，回退到事务开始的状态。COMMIT表示提交事务中的一切操作，使得对数据库的改变生效。
在SQL Server中，对事务的管理包含三个方面：
• 事务控制语句：它使程序员能指明把一系列操作（ Transact - SQL命令）作为一个工作单
位来处理。
• 锁机制（ Locking）：封锁正被一个事务修改的数据，防止其他用户访问到“不一致”的数据。
• 事务日志( Transaction Log)：使事务具有可恢复性。

SQL Server的锁机制
所谓封锁,就是一个事务可向系统提出请求，对被操作的数据加锁( Lock )。其他事务必须等到此事务解锁（ Unlock）之后才能访问该数据。从而，在多个用户并发访问数据库时,确保不互相干扰。可锁定的单位是：行、页、表、盘区和数据库。
1. 锁的类型
SQL Server支持三种基本的封锁类型：共享（ S）锁，排它（X）锁和更新（U）锁。封锁的基本粒度为行。
1) 共享（S）锁：用于读操作。
• 多个事务可封锁一个共享单位的数据。
• 任何事务都不能修改加Ｓ锁的数据。
• 通常是加Ｓ锁的数据被读取完毕，Ｓ锁立即被释放。
2) 独占（Ｘ）锁：用于写操作。
• 仅允许一个事务封锁此共享数据。
• 其他任何事务必须等到Ｘ锁被释放才能对该数据进行访问。
• Ｘ锁一直到事务结束才能被释放。
3) 更新（Ｕ）锁。
• 用来预定要对此页施加Ｘ锁，它允许其他事务读，但不允许再施加Ｕ锁或Ｘ锁。


• 当被读取数据页将要被更新时，则升级为Ｘ锁。
• Ｕ锁一直到事务结束时才能被释放。
2. 三种锁的相容性
如下表简单描述了三种锁的相容性：
通常，读操作（SELECT）获得共享锁，写操作（ INSERT、DELETE）获得独占锁；而更新操作可分解为一个有更新意图的读和一个写操作，故先获得更新锁，然后再升级为独占锁。
执行的命令  获得锁  其他进程可以查询？  其他进程可以修改？
Select title_id from titles  S  Yes  No
delete titles where price>25  X  No  No
insert titles values( ...)  X  No  No
update titles set type=“general”  U  Yes  No
where type=“business”  然后Ｘ  NO  No

使用索引降低锁并发性
我们为什么要讨论锁机制？如果用户操作数据时尽可能锁定最少的数据，这样处理过程,就不会等待被锁住的数据解锁，从而可以潜在地提高SQL Server的性能。如果有200个用户打算修改不同顾客的数据，仅对存储单个顾客信息的单一行进行加锁要比锁住整个表好得多。那么，用户如何只锁定行而不是表呢？当然是使用索引了。正如前面所提到的，对存有要修改数据的字段使用索引可以提高性能，因为索引能直接找到数据所在的页面，而不是搜索所有的数据页面去找到所需的行。如果用户直接找到表中对应的行并进行更新操作，只需锁定该行即可，而不是锁定多个页面或者整个表。性能的提高不仅仅是因为在修改时读取的页面较少，而且锁定较少的页面潜在地避免了一个用户在修改数据完成之前其他用户一直等待解锁的情况。

*******************************************范式****************************************************************

范式（Normalization）要按照要求使用以提高性能。Normalization做的不够会导致数据冗余，而过度Normalization 会导致太多的join和数据表，这两种情况都会影响性能。

数据库范式：是为了消除重复数据减少冗余数据，从而让数据库内的数据更好的组织，消除了数据冗余、更新异常、插入异常和删除异常（删除某个字段信息后其他属性也被删除），让磁盘空间得到更有效利用的一种标准化标准，满足高等级的范式的先决条件是满足低等级范式。

◆ 第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列（粒度问题）。
考虑这样一个表：【联系人】（姓名，性别，电话）
如果在实际场景中，一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列（电话）拆分，即：【联系人】（姓名，性别，家庭电话，公司电话）。1NF 很好辨别，但是 2NF 和 3NF 就容易搞混淆。

◆ 第二范式（2NF）：首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
考虑一个订单明细表：【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName）。
因为我们知道在一个订单中可以订购多种产品，所以单单一个 OrderID 是不足以成为主键的，主键应该是（OrderID，ProductID）。显而易见 Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。
可以把【OrderDetail】表拆分为【OrderDetail】（OrderID，ProductID，Discount，Quantity）和【Product】（ProductID，UnitPrice，ProductName）来消除原订单表中UnitPrice，ProductName多次重复的情况。

◆ 第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
考虑一个订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID）。
其中 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主键列都完全依赖于主键（OrderID），所以符合 2NF。不过问题是 CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。
通过拆分【Order】为【Order】（OrderID，OrderDate，CustomerID）和【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity）从而达到 3NF。
第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。