2008年04月12日 11:35:35
sqlserver事务处理全面解析四
|
1.1.1. 编码指导方针 以下是编写有效事务的指导原则：
在事务启动之前，获得所有需要的用户输入。如果在事务处理期间还需要其它的用户输入，则回滚当前的事务，并在提供了用户输入之后重新启动该事务。即使用户立即响应，作为人，其反应时间也要比计算机慢得多。事务占用的所有资源都要保持很长的时间，这就有可能造成阻塞问题。如果用户没有响应，该事务就会仍保持活动状态，并锁定关键资源，直到他们响应为止，但是用户可能会几分钟甚至几小时都不响应。
在所有预备的数据分析完成之前，不应启动事务。
在知道了必须要进行的修改之后，启动事务，执行修改语句，然后立即提交或回滚。只有在需要时，才打开事务。
可以很容易地编写出许多使用授权读事务隔离级别的应用程序。并不是所有的事务都要求可串行事务隔离级别。
在很少有可能并发更新的系统中，处理某个偶然的"别人在您读取数据后更改了该数据"错误的开销，要比在读取数据时始终锁定行的开销小得多。
这样可以减少锁定的行数，从而减少事务之间的争夺。 1.1.2. 避免并发问题 为了防止并发问题，应该小心地管理隐性事务。在使用隐性事务时，COMMIT 或 ROLLBACK 之后的下一个 Transact-SQL 语句会自动启动一个新事务。这可能在应用程序浏览数据时，甚至在要求用户输入时，打开新的事务。在完成保护数据修改所需要的最后一个事务之后和再次需要一个事务来保护数据修改之前，关闭隐性事务。该进程使 Microsoft® SQL Server™ 得以在应用程序正在浏览数据并获取来自用户的输入时使用自动提交模式。 1.2. 了解和避免阻塞 当来自应用程序的第一个连接控制锁而第二个连接需要相冲突的锁类型时，将发生阻塞。其结果是强制第二个连接等待，而在第一个连接上阻塞。不管是来自同一应用程序还是另外一台客户机上单独的应用程序，一个连接都可以阻塞另一个连接。 说明 一些需要锁保护的操作可能不明显，例如系统目录表和索引上的锁。 大多数阻塞问题的发生是因为一个进程控制锁的时间过长，导致阻塞的进程链都在其它进程上等待锁。 常见的阻塞情形包括：
长时间运行的查询会阻塞其它查询。例如，影响很多行的 DELETE 或 UPDATE 操作能获取很多锁，这些锁不论是否升级到表锁都阻塞其它查询。因此，一般不要将长时间运行的决策支持查询和联机事务处理 (OLTP) 查询混在一起。解决方案是想办法优化查询，如更改索引、将大的复杂查询分成简单的查询或在空闲时间或单独的计算机上运行查询。 查询运行时间长并由此导致阻塞的一个原因是这些查询不适当地使用游标。游标可能是在结果集中浏览的便利方法，但使用游标可能比使用面向集合的查询慢。
如果应用程序取消查询（如使用开放式数据库连接 (ODBC) sqlcancel 函数）但没有同时发出所需数目的 ROLLBACK 和 COMMIT 语句，则会发生这种情况。取消查询并不自动回滚或提交事务。取消查询后，所有在事务内获取的锁都将保留。应用程序必须提交或回滚已取消的事务，从而正确地管理事务嵌套级。
将查询发送到服务器后，所有应用程序必须立即完成提取所有结果行。如果应用程序没有提取所有结果行，锁可能会留在表上而阻塞其他用户。如果使用的应用程序将 Transact-SQL 语句透明地提交给服务器，则该应用程序必须提取所有结果行。如果应用程序没这样做（如果无法配置它执行此操作），则可能无法解决阻塞问题。为避免此问题，可以将这些应用程序限制在报表或决策支持数据库上。
与常规死锁不同，分布式死锁无法由 Microsoft® SQL Server™ 2000 自动检测到。如果应用程序打开多个与 SQL Server 的连接并异步提交查询，则可能会发生分布式客户端/服务器死锁。 例如，一个客户端应用程序线程有两个开放式连接。该线程异步启动事务并在第一个连接上发出查询。应用程序随后启动其它事务，在另一个连接上发出查询并等待结果。当 SQL Server 返回其中一个连接的结果时，应用程序开始处理这些结果。应用程序就这样处理结果，直到生成结果的查询被另一个连接上执行的查询阻塞而导致再没有可用的结果为止。此时第一个连接阻塞，无限期等待处理更多的结果。第二个连接没有在锁上阻塞，但仍试图将结果返回给应用程序。然而，由于应用程序阻塞而在第一个连接上等待结果，第二个连接的结果将得不到处理。 若要避免此问题，请执行下列任一操作：
对每个查询使用锁定超时。使用绑定连接。 SQL Server 本质上是受客户端应用程序操纵的傀儡。客户端应用程序对服务器上获取的锁几乎有完全的控制（并对锁负责）。虽然 SQL Server 锁管理器自动使用锁保护事务，但这受客户端应用程序发出的查询类型和对结果的处理方式的直接鼓动。因此，大多数阻塞问题的解决方案都涉及检查客户端应用程序。 阻塞问题常要求检查应用程序提交的 SQL 语句本身，以及检查与连接管理、所有结果行的处理等有关的应用程序行为本身。如果开发工具不允许显式控制连接管理、查询超时、结果处理等，阻塞问题可能得不到解决。 设计应用程序以避免阻塞的准则包括：
不要使用或设计使用户得以在事务内输入内容的应用程序。 允许取消查询。 使用查询或锁定超时，防止失控查询和避免分布式死锁。 立即完成提取所有结果行。 使事务尽可能简短。 显式控制连接管理。 在所预计的并发用户全负荷下对应用程序进行应力测试。 1.3.1. SQL的使用规范： i. 尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。 ii. 尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。 iii. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。 iv. 注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。 v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 vi. 尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。 vii. 尽量使用“>=”，不要使用“>”。 viii. 注意一些or子句和union子句之间的替换 ix. 注意表之间连接的数据类型，避免不同类型数据之间的连接。 x. 注意存储过程中参数和数据类型的关系。 xi. 注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁。 1.3.2. 索引的使用规范： i. 索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。 ii. 尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引 iii. 避免对大表查询时进行table scan，必要时考虑新建索引。 iv. 在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。 v. 要注意索引的维护，周期性重建索引，重新编译存储过程。 i. 尽量避免使用distinct、order by、group by、having、join、cumpute，因为这些语句会加重tempdb的负担。 ii. 避免频繁创建和删除临时表，减少系统表资源的消耗。 iii. 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。 iv. 如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。 v. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。 vi. 慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。 1.3.4. 合理的算法使用： 根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令：set statistics io on, set statistics time on , set showplan on 等。 在 Microsoft® SQL Server ™ 2000 数据库内，对象作为 8 KB 页的集合存储。本主题描述表和索引页的组织方式。 SQL Server 2000 支持视图上的索引。视图上第一个允许的索引是聚集索引。在视图上执行 CREATE INDEX 语句时，视图的结果集被具体化，并且存储在与有聚集索引的表具有相同结构的数据库中。存储的结果集与下面的语句所产生的结果集相同： SELECT * FROM ViewName 每个表或索引视图的数据行存储在 8 KB 数据页集合中。每个数据页都有一个 96 字节的页头，其中包含象拥有该页的表的标识符 (ID) 这样的系统信息。如果页链接在列表中，则页头还包含指向下一页及前面用过的页的指针。在页尾有行偏移表。数据行填充页的剩余部分。  SQL Server 2000 中的表使用下面两种方法组织其数据页。
基于聚集索引键按顺序存储数据行。索引按 B 树索引结构实现，B 树索引结构支持基于聚集索引键值对行进行快速检索。每级索引中的页（包括叶级中的数据页）链接在双向链接列表中，但使用键值在各级间导航。
不按任何特殊顺序存储数据行，数据页序列也没有任何特殊顺序。数据页不在链表内链接。 索引视图与聚集索引表具有相同的存储结构。 SQL Server 在每个表或索引视图上还支持多达 249 个非聚集索引。非聚集索引有一个与聚集索引中相似的 B 树索引结构。不同的是非聚集索引对数据行的顺序不起作用。聚集索引表和索引视图基于聚集索引键按顺序存储数据行。如果为表定义了非聚集索引，对堆集的数据页集合没有影响。除非定义了聚集索引，否则数据页保留在堆集内。 包含 text、ntext 和 image 数据的页作为每个表的单个单元进行管理。一个表的所有 text、ntext 和 image 数据存储在一个页集合内。 sysindexes 表内的页指针定位表、索引和索引视图的所有页集合。每个表和索引视图有一个数据页集合，以及其它一些实现为这个表或视图定义的各个索引的页集合。 每个表、索引和索引视图在 sysindexes 内有一行，由对象标识符 (id) 列和索引标识符 (indid) 列的组合唯一标识。IAM 页链管理分配给表、索引和索引视图的页。sysindexes.FirstIAM 列指向 IAM 页链的 IAM 首页，IAM 页链管理分配给表、索引或索引视图的空间。 每个表在 sysindexes 内有一个行集：
FirstIAM 列指向表的数据页集合的 IAM 链。服务器使用 IAM 页查找数据页集合内的页，因为这些页不链接在一起。
root 列指向聚集索引 B 树的顶端。服务器使用索引 B 树查找数据页。
每个非聚集索引的行内的 indid 值都从 2 到 250。root 列指向非聚集索引 B 树的顶端。
FirstIAM 列指向管理 text、ntext 和 image 页的 IAM 页链。 在 SQL Server 6.5 版及更早的版本中，sysindexes.first 始终指向堆集的起点、索引叶级的起点或者 text 和 image 页链的起点。在 SQL Server 7.0 版及更高的版本中，很少使用 sysindexes.first。在 SQL Server 6.5 版及更早的版本中，indid = 0 这行内的 sysindexes.root 指向堆集的最后一页。在 SQL Server 7.0 版及更高的版本中，不使用 indid = 0 这行内的 sysindexes.root。 1.4.2. 聚集索引 聚集索引在 sysindexes 内有一行，其 indid = 1。数据链内的页和其内的行按聚集索引键值排序。所有插入都在所插入行中的键值与排序顺序相匹配时执行。 Microsoft® SQL Server™ 2000 将索引组织为B树。索引内的每一页包含一个页首，页首后面跟着索引行。每个索引行都包含一个键值以及一个指向较低级页或数据行的指针。索引的每个页称为索引节点。B树的顶端节点称为根节点。索引的底层节点称为叶节点。每级索引中的页链接在双向链接列表中。在聚集索引内数据页组成叶节点。根和叶之间的任何索引级统称为中间级。 对于聚集索引，sysindexes.root 指向它的顶端。SQL Server 沿着聚集索引浏览以找到聚集索引键对应的行。为找到键的范围，SQL Server 浏览索引以找到这个范围的起始键值，然后用向前或向后指针扫描数据页。为找到数据页链的首页，SQL Server 从索引的根节点开始沿最左边的指针进行扫描。 下图说明聚集索引的结构。  非聚集索引与聚集索引一样有B树结构，但是有两个重大差别：
非聚集索引的叶层不包含数据页。 非聚集索引可以在有聚集索引的表、堆集或索引视图上定义。在 Microsoft® SQL Server™ 2000 中，非聚集索引中的行定位器有两种形式：
如果表没有聚集索引，或者索引在索引视图上，则行定位器就是行的聚集索引键。如果聚集索引不是唯一的索引，SQL Server 2000 将添加在内部生成的值以使重复的键唯一。用户看不到这个值，它用于使非聚集索引内的键唯一。SQL Server 通过使用聚集索引键搜索聚集索引来检索数据行，而聚集索引键存储在非聚集索引的叶行内。  |
一共有 1 条评论