解振宇 客户技术经理 客户售前技术部 微软中国有限公司广州办事处

Presentation on theme: "解振宇 客户技术经理 客户售前技术部 微软中国有限公司广州办事处"— Presentation transcript:

1

2 解振宇 客户技术经理 客户售前技术部 微软中国有限公司广州办事处
2018年11月17日6时51分 SQL Server 2000 & 2005中的隔离级别 解振宇 客户技术经理 客户售前技术部 微软中国有限公司广州办事处 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

3 议程 可用性 ACID 属性 理解隔离级别 控制隔离级别 SQL语句中的 Read Committed “快照” 使用“快照”隔离级别
2018年11月17日6时51分 议程 可用性 ACID 属性 理解隔离级别 控制隔离级别 SQL语句中的 Read Committed “快照” 使用“快照”隔离级别 升级所带来的影响 潜在的问题 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

4 2018年11月17日6时51分 可用性 ACID 事务设计的要求 Atomicity Consistency Isolation Durability 隔离级别 Level 0 – Read Uncommitted Level 1 – Read Committed Level 2 – Repeatable Reads Level 3 – Serializable SQL 2000/2005中的默认隔离级别， ANSI/ISO Level 1, Read Committed 基于锁机制实现 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

5 ACID 的属性 Atomicity（原子） Consistency（一致） Isolation（隔离） Durability（持久）
2018年11月17日6时51分 ACID 的属性 Atomicity（原子） 事务是运算中的最小单位；要么全部修改，要么保持原状 Consistency（一致） 事务完成后，数据及相关结构必须处于一致的状态 Isolation（隔离） 在并发环境中，一个事务所能够获取的数据要么处于其他事务开始之前的状态，要么处于其他事物结束之后的状态；不允许获取其他事物处理过程中的数据。 Durability（持久） 即使系统出错，事务也应被保持 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

6 隔离级别 0 – Read Uncommitted 现象: 脏读
2018年11月17日6时51分 隔离级别 0 – Read Uncommitted 现象: 脏读 能够读取其他事物中尚未提交的数据——产生脏读 DML 语句总是采用排他锁 实现: 对于允许脏读的事务，不产生行级锁 (产生SCH_S锁) ;产生脏读时，将忽略所获取数据对象的锁机制 产生的现象: 基于不准确（一致）的数据产生查询结果，所涉及的数据集在读取过程中可能发生变化甚至消失（ROLL BACK） © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

7 隔离级别 1 – Read Committed 现象: 不一致的分析
2018年11月17日6时51分 隔离级别 1 – Read Committed 现象: 不一致的分析 所有版本中的默认行为 不能读取其他事务中正在修改的数据 ，只有提交后的数据是可见的 DML 语句总是使用排他锁 实现: 锁被释放之后，数据才能够被读取 ，在某些情况下，同一行数据可能被读取多次 产生的现象: 在整个事务周期中，同一行数据的读取操作不能够重复 ——所获取的同一行数据可能会不一致 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

8 隔离级别 2 – Repeatable Read 现象: 幻读
2018年11月17日6时51分 隔离级别 2 – Repeatable Read 现象: 幻读 事务中所涉及的数据不能被其他事务修改 事务中未被修改的数据能够被其他事务读取, 同时，不允许DML操作 实现: 在整个事务过程当中，对所涉及的数据加锁，数据在整个事务过程当中能够被重复读取 产生的现象: 在读取操作所产生的结果集中可能会出现事务开始时不存在的数据 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

9 隔离级别 3 – Serializable 现象: 无
2018年11月17日6时51分 Key range and index reminder 隔离级别 3 – Serializable 现象: 无 事务中所涉及的数据不能被其他事务修改 事务中未被修改的数据能够被其他事务所读取,同时，不允许 DML操作 实现: 在整个事务过程当中，所涉及的数据以及索引都将被加锁，以防止数据被添加到特定的数据集中 副作用: 为了防止幻读，数据集将会被锁定；如果没有对应的索引，将会产生一个表级锁 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

10 数据隔离的实现 在默认情况下，所有版本的SQL Server ， 包括SQL Server 2005 ，都采用基于锁的方式来实现数据隔离
2018年11月17日6时51分 数据隔离的实现 在默认情况下，所有版本的SQL Server ， 包括SQL Server 2005 ，都采用基于锁的方式来实现数据隔离 在默认环境下，为了减少各种异常情况的产生，在事务周期内确实有可能需要更高级别的锁——可能导致阻塞 在数据必须隔离的环境中，锁机制帮我们实现这种隔离 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

11 2018年11月17日6时51分 数据隔离的实现(续) SQL Server 2005 除了提供基于锁机制的数据隔离，同时还提供了基于数据版本（row-level versioning）的隔离方式 基于数据版本的隔离方式提供两种方式 Read Committed using Statement-level Snapshot 语句级的读取一致性 事务级的读取一致性 Snapshot Isolation（快照隔离级别） © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

12 设置隔离行为 默认行为 Snapshot Isolation Both RCSI and Snapshot Isolation
2018年11月17日6时51分 设置隔离行为 默认行为 Read Committed using Statement-Level Snapshot Snapshot Isolation Both RCSI and Snapshot Isolation ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK AFTER 5 ALTER DATABASE <database_name> SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE <database_name> SET RCSLS... ALTER DATABASE <database_name> SET Snapshot... © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

13 Read Committed 默认行为 所有副作用 – 除了脏读，即使在一条查询语句（ select ）中
2018年11月17日6时51分 Read Committed 默认行为 所有副作用 – 除了脏读，即使在一条查询语句（ select ）中 在某些频繁更新的数据库中，一个长时间运行的查询语句可能会产生不一致的结果 提高隔离级别 延长数据加锁时间 可能会产生阻塞 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

14 SQL Server 默认隔离行为 不同事务里的读写冲突
2018年11月17日6时51分 SQL Server 默认隔离行为 不同事务里的读写冲突 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

15 最小化阻塞 编写高效率的事务 —— 保持简短、通过成批（ batch ）的方式完成任务 在事务当中，尽可能避免用户交互
2018年11月17日6时51分 最小化阻塞 编写高效率的事务 —— 保持简短、通过成批（ batch ）的方式完成任务 在事务当中，尽可能避免用户交互 采用索引，只锁定必要的数据 考虑将运行时间超长的查询语句转移到另一个只读数据库上 长时间运行的事务容易引起锁阻塞 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

16 理解隔离级别 在多语句的事务环境中 Read Uncommitted Q1 > Q2 Q1 < Q2 Q1 = Q2
2018年11月17日6时51分 理解隔离级别 在多语句的事务环境中 Read Uncommitted Q1 > Q2 Q1 < Q2 Q1 = Q2 不能保证Q1所获取的数据是准确的（已提交的） Q1所涉及的数据未加锁，在Q2执行之前甚至执行过程中，有可能被修改 不能保证Q2所获取的数据是准确的（已提交的） BEGIN TRAN sql Q1 = SELECT count(*) FROM dbo.tname WHERE country = 'USA' … Q2 = SELECT count(*) FROM dbo.tname WHERE country = 'USA' COMMIT TRAN © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

17 理解隔离级别 在多语句的事务环境中 Read Committed Q1 > Q2 Q1 < Q2 Q1 = Q2
2018年11月17日6时51分 理解隔离级别 在多语句的事务环境中 Read Committed Q1 > Q2 Q1 < Q2 Q1 = Q2 能够保证Q1所获取的数据是已提交的数据 Q1所涉及的数据未被加锁，因此，即使在Q1执行过程中也存在被不一致读取的可能性 能够保证Q2所获取的数据是已提交的数据 BEGIN TRAN sql Q1 = SELECT count(*) FROM dbo.tname WHERE country = 'USA' … Q2 = SELECT count(*) FROM dbo.tname WHERE country = 'USA' COMMIT TRAN © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

18 理解隔离级别 在多语句的事务环境中 Repeatable Read Q1 < Q2 Q1 = Q2
2018年11月17日6时51分 理解隔离级别 在多语句的事务环境中 Repeatable Read Q1 < Q2 Q1 = Q2 Q1所涉及的数据被锁定；但这并不能防止新的数据添加进来 Q2所获取的数据不会比Q1少 锁定的数据不能够被其他事务修改 BEGIN TRAN sql Q1 = SELECT count(*) FROM dbo.tname WHERE country = 'USA' … Q2 = SELECT count(*) FROM dbo.tname WHERE country = 'USA' COMMIT TRAN © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

19 理解隔离级别 在多语句的事务环境中 Serializable Q1 = Q2 Q1所涉及的数据被锁定，在Q1、Q2之间无法更改
2018年11月17日6时51分 理解隔离级别 在多语句的事务环境中 BEGIN TRAN sql Q1 = SELECT count(*) FROM dbo.tname WHERE country = 'USA' … Q2 = SELECT count(*) FROM dbo.tname WHERE country = 'USA' COMMIT TRAN Serializable Q1 = Q2 Q1所涉及的数据被锁定，在Q1、Q2之间无法更改 Serializable保护整个Q1结果集所涉及的数据，不允许所涉及的数据集被更改 采用锁机制来保证一致性 被锁定的数据不能够被其他事务修改 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

20 锁机制防止冲突 以阻塞为代价 在事务过程当中，没有其他事务能够影响当前事务的结果集 在某些情况下，确实需要更高的隔离级别
2018年11月17日6时51分 锁机制防止冲突 以阻塞为代价 在事务过程当中，没有其他事务能够影响当前事务的结果集 在某些情况下，确实需要更高的隔离级别 基于队列的应用 经常变更的价格，等等… 在允许数据不实时的情况下… 在事务运行时间特别长的情况下… © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

21 Read Committed Snapshot Isolation 打开数据库 READ_COMMITTED_SNAPSHOT选项
2018年11月17日6时51分 Read Committed Snapshot Isolation 打开数据库 READ_COMMITTED_SNAPSHOT选项 在同一条SQL语句的边界内，所读取的数据是一致的 在一个经常更新的数据库中，一个事务中的多条查询语句（同样条件）可能会产生不一致的结果 在每一条语句的执行过程中，查询结果是一致的，但是在整个事务过程中，这些结果可能会不一致 每次有新的语句读取数据，都会产生一个最新版本 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

22 理解隔离级别 在多语句的事务环境中 Uses tempdb RCSI Q1 > Q2 Q1 < Q2 Q1 = Q2
2018年11月17日6时51分 理解隔离级别 在多语句的事务环境中 Uses tempdb RCSI Q1 > Q2 Q1 < Q2 Q1 = Q2 在语句开始执行的时候生成快照，数据视图在语句执行过程当中不会被更改 RCSI 保证该语句执行过程中数据的一致性；但不保证事务执行过程中快照的一致性 BEGIN TRAN sql Q1 = SELECT count(*) FROM dbo.tname WHERE country = 'USA' … Q2 = SELECT count(*) FROM dbo.tname WHERE country = 'USA' COMMIT TRAN © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

23 Snapshot Isolation 打开数据库 ALLOW_SNAPSHOT_ISOLATION选项
2018年11月17日6时51分 Snapshot Isolation 打开数据库 ALLOW_SNAPSHOT_ISOLATION选项 该参数使得用户可以使用“Snapshot Isolation” ，默认情况下该参数未打开 一旦设置该参数，事务周期内的所有操作都能够保持一致性 在事务周期内，所有操作共享事务开始时产生的快照 快照存在时间较长，在多语句的事务环境中，可能会产生冲突 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

24 理解隔离级别 在多语句的事务环境中 Snapshot Isolation Q1 = Q2 Q1所涉及的数据基于事务开始时产生的快照
2018年11月17日6时51分 理解隔离级别 在多语句的事务环境中 Snapshot Isolation Q1 = Q2 Q1所涉及的数据基于事务开始时产生的快照 在事务开始时，数据被隔离；即使在其他事务中对相关数据进行了更新，Q2所获取的数据也能与Q1保持一致 使用保存于tempdb中的数据版本 数据对于其他事务可用 BEGIN TRAN sql Q1 = SELECT count(*) FROM dbo.tname WHERE country = 'USA' … Q2 = SELECT count(*) FROM dbo.tname WHERE country = 'USA' COMMIT TRAN © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

25 2018年11月17日6时51分 SQL Server 2005 认识基于快照的隔离级别 READ_COMMITTED_SNAPSHOT & ALLOW_SNAPSHOT_ISOLATION © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

26 隔离级别: 总结 “脏读” 能够获取任何数据 (正在修改或者被锁定的数据) 只能够获取已提交的数据 任何处于中间状态的数据都不能够被获取
2018年11月17日6时51分 隔离级别: 总结 READ UNCOMMITTED (Level 0) “脏读” 能够获取任何数据 (正在修改或者被锁定的数据) READ COMMITTED (Level 1 – 默认级别) 只能够获取已提交的数据 任何处于中间状态的数据都不能够被获取 READ COMMITTED SNAPSHOT (RCSI) 语句级的读取一致性 全新的隔离级别，不加锁(ex. SCH_S)、不阻塞，基于数据版本的隔离级别 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

27 隔离级别: 总结(续) 在整个事务过程中，能够保证所有读取操作的一致性 将一直锁定数据（共享锁），直到数据处理完成
2018年11月17日6时51分 隔离级别: 总结(续) REPEATABLE READS (Level 2) 在整个事务过程中，能够保证所有读取操作的一致性 将一直锁定数据（共享锁），直到数据处理完成 不保护整个数据集（可能会产生幻读） SERIALIZEABLE (Level 3) 不会产生幻读 Snapshot Isolation – 2005 基于快照的事务级的一致性性 不加锁，不阻塞，基于数据版本的事务处理 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

28 控制隔离级别 表级更新 From 子句, 每张表(中间无空格) FROM dbo.titles WITH(READUNCOMMITTED)
2018年11月17日6时51分 控制隔离级别 表级更新 From 子句, 每张表(中间无空格) Level 0 – READUNCOMMITTED, NOLOCK Level 1 – READCOMMITTED (locking) Level 1 – READCOMMITTED (versioning) 只有在SQL 2005中，并且数据库的 READ_COMMITTED_SNAPSHOT打开之后 能够被READCOMMITTEDLOCK参数所覆盖 Level 2 – REPEATABLEREAD Level 3 – SERIALIZABLE, HOLDLOCK FROM dbo.titles WITH(READUNCOMMITTED) JOIN dbo.publishers WITH(SERIALIZABLE) © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

29 控制隔离级别 连接级更新 (续) 连接级的隔离级别设置将会影响当前数据库连接所执行的语句，但是能够被表级设置覆盖
2018年11月17日6时51分 控制隔离级别 连接级更新 (续) 连接级的隔离级别设置将会影响当前数据库连接所执行的语句，但是能够被表级设置覆盖 Level 0 – READ UNCOMMITTED Level 1 – READ COMMITTED Level 2 – REPEATABLE READ Level 3 – SERIALIZABLE SET TRANSACTION ISOLATION LEVEL… READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE SNAPSHOT 仅适用于SQL 2005，并且要求数据库选项 ALLOW_SNAPSHOT_ISOLATION已打开 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

30 在语句级使用基于快照的Read Committed隔离级别
2018年11月17日6时51分 在语句级使用基于快照的Read Committed隔离级别 数据库选项 ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK AFTER 5 不需要其他设置… 不需要更改查询语句或者应用程序 (注意: 如果您的应用程序逻辑基于锁，可能需要对应用程序进行修改 例如：队列, 阻塞式查询机制等) 减少了阻塞… 如果数据库并发度并不是您系统中的瓶颈，有可能会影响到最终的性能 考虑快照的成本 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

31 使用快照隔离级别 数据库选项 连接级设置 Changes to applications: 会引起更大的开销
2018年11月17日6时51分 使用快照隔离级别 数据库选项 ALTER DATABASE <database_name> SET ALLOW_SNAPSHOT_ISOLATION ON 连接级设置 SET TRANSACTION ISOLATION LEVEL SNAPSHOT Changes to applications: Request snapshot isolation Test for conflict detection 会引起更大的开销 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

32 SQL Server 2005 Snapshot Isolation 升级数据库结构带来的影响
2018年11月17日6时51分 SQL Server 2005 Snapshot Isolation 升级数据库结构带来的影响 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

33 潜在的问题 额外的消耗 – 每行增加14个字节 如果采用RCSI, 需要考察是否系统基于锁机制? 如果采用快照隔离级别,会产生冲突吗？
2018年11月17日6时51分 潜在的问题 额外的消耗 – 每行增加14个字节 如果采用RCSI, 需要考察是否系统基于锁机制? 要考虑到状态队列之类的情况… 提示: 使用 READCOMMITTEDLOCK 参数 如果采用快照隔离级别,会产生冲突吗？ 注意冲突检测和错误处理 可以参考相关白皮书和例程 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

34 管理和监控 版本存储于 TempDB中 如果不再被引用，相关版本将会被删除
2018年11月17日6时51分 管理和监控 版本存储于 TempDB中 如果不再被引用，相关版本将会被删除 在采用语句级快照方式的Read committed隔离级别时，版本将只会在同一语句有效 快照隔离级别可能会引起TempDB的冲突 如果有众多耗时事务同时运行，将会给TempDB带来较大的压力 关于快照版本的监控，请参考关于DMVs的白皮书 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

35 总结 可用性与锁 ACID属性 理解隔离级别 控制隔离级别 SQL语句中的Read Committed快照
2018年11月17日6时51分 总结 可用性与锁 ACID属性 理解隔离级别 控制隔离级别 SQL语句中的Read Committed快照 使用Snapshot Isolation 升级所带来的影响 潜在的问题 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

36 资源 MSDN上的SQL Server 2005 Developer Center
2018年11月17日6时51分 资源 MSDN上的SQL Server 2005 Developer Center More about Visual Studio 2005 XML Developer Center Microsoft Express Product Range 请密切关注Developer Center,每个星期都会有新的资源 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

37 Your Feedback is Important!

38 © 2005 Microsoft Corporation. All rights reserved.
2018年11月17日6时51分 © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary. © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.