SQL Server 2008之创建高并发应用程序

1. 每个事务都是记录在事务日志中，数据修改首先写到事务日志中，然后在写到数据库中，如果事务的任何部分失败，修改全部回滚，数据库保持在原来的状态，事务使用锁阻止其他用户读取或者修改还没有提交的数据
2. SQL Server默认是自动提交，即每个T-SQL语句执行成功就提交，执行失败就回滚，除非显式开启事务；默认是只回滚产生错误的语句，如果XACT_ABORT设置为ON的话，则出现错误时回滚整个批处理

3. 使用WITH MARK选项可以为事务指定一个描述，描述被标记在事务日志中，这个事务日志标记用于restore数据库，表明想要restore的点；SAVE TRANSACTION语句创建一个save point，这样回滚数据库时，就可以回滚到这个点上，而不是事务最开始处

4. 所有T-SQL语句被执行时，它都要被记录到事务日志中，SQL Server每隔一段时间执行一次checkpoints，checkpoints被标记到数据库中，当产生一个新的checkpoint时，从上一个checkpoint开始的所有内存中被修改的数据页都被写到数据库中；如果在事务中发生错误，SQL Server使用日志中的信息回滚事务，这样的回滚不会影响其他事务；有些错误自动回滚事务，比如死锁、网络中断；每次SQL Server启动的时候都自动运行恢复进程，自动恢复进程使用事务日志前滚已经提交的任何事务，回滚任何未提交的事务，日志使用最近一次checkpoint作为开始标记点，这个点之前提交的事务都被写到数据库中，这个点之前开始但仍然active的需要回滚

5. 使用事务时需要考虑的因素：
   1. 事务尽可能短，长事务增加了用户不能访问被锁数据的可能性
      • 不能在事务中要求用户输入，一条黄金法则就是不能在用户交互中保持事务
      • 尽可能不要在浏览数据时打开事务，事务应该在所有需要数据都准备好以后在开启
      • 事务应该尽可能影响最少的行，一个事务不应该比一个逻辑工作单元小
      • 事务中应该尽可能访问最少行，减少被锁的行
      • 保证适当的索引，因为这减少需要访问和被锁的数据页
   2. 试图使用同样的顺序访问资源，帮助避免死锁，这也不总是可以的
   3. 考虑嵌套事务
      • 使用嵌套事务时要非常小心
      • 使用@@trancount查看事务嵌套层次
      • 语法上支持事务嵌套，但不是真正支持，回滚嵌套事务将回滚真个事务，而不仅仅只回滚当前事务

6. 并发控制就是管理多个用户同时访问相同的数据，以便于他们之间不会相互影响，通过指定隔离级别来指定并发控制的类型，共有两种并发控制的类型：

• 保守式并发控制：为了修改而读取数据时，对数据上锁，只有释放锁以后，其他用户才可以访问数据，用于高数据竞争的情形
• 开放式并发控制：起初不对数据加锁，当执行更新时对数据加锁，如果数据和最初读取时发生了修改，就返回错误，用于低数据竞争的情形

7. 锁就是同步多个用户同时对同样的数据访问的机制，主要有两种类型的锁：

• Read Locks：允许其他用户读取数据，但是不允许写
• Write Locks：禁止其他用户读写数据

8. 事务赋予的锁不能与数据上已经存在的锁冲突，否则事务请求会被挂起知道先前的锁被释放，锁持续的时间取决于锁的隔离级别设置

9. 锁阻止更新冲突：

• 保证事务是顺序执行的
• 锁是原子的
• 保证数据使用的一致性

10. 如果没有锁，会发生如下问题：

• 更新丢失，两个事务同时更新同一行，后一个事务更新覆盖前一个事务更新

• 脏读取，读取了其他事务未提交的数据，如果其他事务回滚的话，就读取了脏数据

• 不可重复读，两次检索同一行，每次读取数据都不相同，当前事务第一次读取数据后，另外一个事务修改了数据，并提交，当前事务再次读取时，读取到的数据就和第一次不同（这也就是不可重复读的意义，正常应该读取的是重复值才对）

• 幻读，事务进行两次查询，第二次查询包含了第一次查询没有的数据或者缺少了第一次查询的数据，因为另外一个事务在这个事务读取的过程中插入或者删除了数据

• 由于行更新，丢失读取或者重复读取

11. 可以锁定的资源：

• RID，行标识符，用来锁定堆上的一行

• KEY，锁定索引里的一行，用来保护一个key的范围被一致性访问

• PAGE，数据库中8KB的数据页

• EXTENT，8个连续的数据页组成的一个组

• HoBT，A heap or B-tree，用来保护一个B-tree或者表上没有聚集索引的堆

• TABLE，整个数据表，包括所有数据和索引

• FILE，一个数据库文件

• APPLICATION，一个应用程序指定的资源

• METADATA

• ALLOCATION_UNIT，一组数据页集合，数量可少可多

• DATABASE，整个数据库

12. SQL Server使用不同的锁模式决定资源如何被重入事务访问，分为两类主要类型：
    1. 基本锁，通常来说，读操作获取共享锁，写操作获取独占锁
    • Shared locks，共享锁，用于只读数据的操作，在一个资源上应用共享锁后，第二个事务还可以获取一个共享锁，数据不可以被修改，SQL Server在读取下一条记录时释放本条记录共享锁，在满足查询条件的所有行返回客户端时释放共享锁
    • Exclusive locks，独占锁，用于增、删、改操作，在一个资源上只有一个事务可以获取一个独占锁，一个事务不能在已经拥有独占锁的资源上获得独占锁，直到该独占锁被释放才可以获得
    2. 特殊用途的锁
    • Intent locks，用于最小化锁冲突，用于建立锁层级，使得其他事务只能获得更加严格的锁，比如一个事务获取了行级别的独占锁，其他事务就不能获取表级别的独占锁，包括intent share、intent exclusive、shared with intent exclusive
    • Update locks，在将要修改数据页时使用更新锁，在更新数据页前提升为独占锁，在数据页第一次被读取更新操作最初始部分获取更新锁，和共享锁兼容（是不是可以理解为开始时是个共享锁，提交数据时提升为独占锁）
    • Schema locks，用来保证表或者索引不被删除或者不会引用正在修改的架构，包括两种类型：Schema stability，保证资源不被删除，Schema modification，保证其他会话不会引用正在修改的资源
    • Bulk update locks，用来在批量拷贝数据时，保证其他进程不会访问同样的数据，在以下情况下使用批量更新锁：TABLOCK hint（比如INSERT INTO Production.Location WITH (BULOCK)...）或者批量加载选项

13. 一些锁和其他锁兼容，一些锁则不兼容，共享锁和独占锁不兼容，更新锁除了共享锁以外都不兼容，独占锁和其他锁都不兼容

14. SET LOCK_TIMEOUT指定等待锁释放的毫秒数，默认是-1，表示永远等待，超时后返回错误并回滚所有语句；READPAST告诉SQL Server忽略因为被锁住无法读取的行，很少使用

15. Lock escalation，锁升级，就是将许多细粒度锁转化为更少粗粒度锁的过程，降低系统开销，增加了并发争用的可能性；经常需要处理大量的数据行，这样就需要大量的锁，获取和释放大量的锁对处理性能和内存使用有重要的影响，因此SQL Server会将行级别的锁提升为表级别的锁，对于分区表来说，会提升为分区级别的锁

16. SQL Server处理死锁的过程：

• 回滚发生死锁victim的事务，在一个死锁中，SQL Server给予处理时间长的事务高优先级，回滚执行时间短的事务（即死锁victim）

• 通过消息编号1205通知死锁victim应用程序

• 取消死锁victim的当前请求

• 允许其他事务继续

17. 锁相关的Table Hints，尽量不要使用这些Table Hints，如果使用的话，要非常小心：

• HOLDLOCK，持有共享锁，直到事务结束
• NOLOCK，不使用锁
• PAGLOCK，使用页级别锁，而不是使用行级别锁
• ROWLOCK，使用行级别锁，而不是页级别或者表级别锁
• TABLOCK，使用表级别锁
• TABLOCKX，在整个表使用一个独占锁
• UPDLOCK，使用一个更新锁，直到事务结束

• XLOCK，使用独占锁，直到事务结束

18. 查看死锁的方式：

• DMV，sys.dm_tran_locks检索数据库引擎当前持有的锁信息，每行返回当前授予的锁和当前请求的锁，sys.dm_tran_active_transactions，sys.dm_tran_session_transactions，sys.dm_tran_current_transaction

• SQL Server Profiler

• Reliability and Performance Monitor

• SSMS

19. 隔离级别保护事务免受其他重入的事务影响，当你设置隔离级别时，你为当前会话所有语句设置了默认锁定行为；隔离级别越高，数据完整性问题越低，但是锁相应的被持有的时间越长；这里的快照隔离指的是在事务开始那一刻对数据库进行快照（这些快照被保存在tempdb中），不会在快照的基础数据行或者数据页上获取锁，这样其他事务就可以执行更新操作，这些执行更新操作的事务也不会影响快照事务，但是当快照事务提交数据时，如果数据较读取之前已经发生了更改，将进行回滚并引发错误，在使用快照隔离前，必须设置ALLOW_SNAPSHOT_ISOLATION ON来启用快照隔离

隔离级别

脏读取

非重复读取

幻读

Read uncommitted

Yes

Yes

Yes

Read committed（默认）

No

Yes

Yes

Repeatable read

No

No

Yes

Snapshot

No

No

No

Serializable

No

No

No

20. Read Committed Snapshot是一个数据库选项，不需要修改应用程序，当开启这个选项时，即使没有事务使用快照隔离级别时，也会导致DML语句开始生成row versions，指定了Read committed的事务自动使用Read Committed Snapshot，即自动使用row versions，而不是锁，所有语句看到的都是语句开始时的数据快照，通过READ_COMMITTED_SNAPSHOT开关设置该选项，这个选项和快照隔离级别是不一样的，使用这个选项时快照只存在于每个语句执行期间，而快照隔离界别则是在整个事务执行期间

21. 隔离相关的Table Hints，READCOMMITTED、READUNCOMMITTED、REPEATABLEREAD、SERIALIZABLE对象相应的隔离级别，READCOMMITTEDLOCK，指定读取操作都采用READCOMMITTED隔离级别，不管Read Committed Snapshot的设置，这些Table Hints同样不建议使用