索引 视图 事务 游标 锁 详细介绍

本文格式为Word版，下载可任意编辑——索引视图事务游标锁详细介绍

索引视图事务游标锁详细介绍

索引视图游标事务锁

1、索引

索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的规律指针清单。

优点：

大大加快数据的检索速度;

创立唯一性索引，保证数据库表中每一行数据的唯一性;

加速表和表之间的连接;

在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：

索引需要占物理空间占用物理空间过多会影响整个SQLServer性能。

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

建立索引的原则：

（1）主键的数据列一定要建立索引，由于主键可以加速定位到表中的某一行。

（2）外键的数据列一定要建立索引，外键列寻常用于表与表之间的连接，在其上创立索引可以加快表间的连接速度。

（3）对于经常查询的数据列最好建立索引。

索引的分类

普通索引：这是最基本的索引，它没有任何限制

唯一索引：它与前面的普通索引类似，不同的就是：索引列的值必需唯一，但允许有空值。假使是组合索引，则列值的组合必需唯一

主键索引（通过主键约束间接创立）：它是一种特别的唯一索引，不允许有空值。一般是在建表的时候同时创立主键索引

组合索引：在表中的多个列上创立的索引。组合索引中列的顺序是任意的，可以是相邻的列，也可以是不相邻的列。

理解索引：

（1）假使经常使用表中的某一列或某几列为条件进行查询，且表中的数据量比较大时，可以创立索引，以提高查询的速度。

（2）索引是与表关联的可选结构。

（3）通过有目的的创立索引，可以加快对表执行SELECT语句的速度。

（4）不管索引是否存在，都无需修改任何SQL语句的书写方式。索引只是一种快速访问数据的途径，它只影响查询执行的效率。

（5）可以使用CREATEINDEX命令在一列或若干列的组合上创立索引。

（6）创立索引时，将获取要创立索引的列，并对其进行排序。然后，将一个指针连同每一行的索引值存储起来，组成键值对（目录名和页码）。使用索引时，系统首先通过已排序的列值执行快速探寻，然后使用相关联的指针值来定位具有所要查找值的行。

（7）一旦创立了索引，SQL会自动维护和使用它们。

１

索引视图事务游标锁详细介绍

（8）只要修改了数据，如添加新行、更新现有行或删除行，SQL都会自动更新索引。

（9）但是为表创立过多的索引会降低更新、删除以及插入的性能，由于SQL还必需更新与该表关联的索引。

2、视图从用户角度来看，一个视图是从一个特定的角度来查看数据库中的数据。从数据库系统内部来看，一个视图是由SELECT语句组成的查询定义的虚拟表。从数据库系统内部来看，视图是由一张或多张表中的数据组成的，从数据库系统外部来看，视图就宛如一张表一样，对表能够进行的一般操作都可以应用于视图，例如查询，插入，修改，删除操作等。视图是一个虚拟表，其内容由查询定义。

概述:

视图以经过定制的方式显示来自一个或多个表的数据

视图是一种数据库对象，用户可以象查询普通表一样查询视图。

视图内其实没有存储任何数据，它只是对表的一个查询。

视图的定义保存在数据字典内。创立视图所基于的表为“基表〞。

视图一经定义以后，就可以像表一样被查询、修改、删除和更新

优点、作用：

简化数据查询语句，减少带宽流量、优化后还可提高执行效率

通过重命名列，从另一个角度提供数据，使用户能从多角度看到同一数据

提高了数据的安全性，对不同的用户定义不同的视图，使用户只能看到与自己相关的数据。提供了一定程度的规律独立性

提供了另外一种级别的表安全性

隐蔽的数据的繁杂性

缺点：

某些视图是不能更新数据的，union、groupby、avg、sum、max、distinct等及连接表的视图。

视图定义听从下述限制：

SELECT语句不能包含FROM子句中的子查询。

SELECT语句不能引用系统或用户变量。

SELECT语句不能引用预处理语句参数。

在存储子程序内，定义不能引用子程序参数或局部变量。

在定义中引用的表或视图必需存在。但是，创立了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECKTABLE语句。

在定义中不能引用TEMPORARY表，不能创立TEMPORARY视图。

在视图定义中命名的表必需已存在。

２

索引视图事务游标锁详细介绍

不能将触发程序与视图关联在一起。

概述：

视图的使用与表一样，有增删改查四种操作，且语法也与表一致。

在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE可以统称为“通过视图更新数据〞。

通过视图更新数据有如下限制：

视图更新操作：可更新的视图：要通过视图更新基本表数据，必需保证视图是可更新视图，即可以在INSET、

UPDATE或DELETE等语句当中使用它们。对于可更新的视图，在视图中的行和基表中的行之

间必需具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。假使视

图包含下述结构中的任何一种，那么它就是不可更新的：

注意：当视图所依靠的基本表有多个时，不能向该视图插入数据，由于这将会影响多个基本表。对INSERT语句还有一个限制：SELECT语句中必需包含FROM子句中指定表的所有不能为空的列。聚合函数；DISTINCT关键字；GROUPBY子句；ORDERBY子句；HAVING子句；UNION运算符；位于选择列表中的子查询；FROM子句中包含多个表；SELECT语句中引用了不可更新视图；一次只能修改一个底层的基表假使修改违反了基表的约束条件，则无法更新视图假使视图中的列不是表中的原始列（如创立视图时使用了连接操作符、聚合函数等），则不能通过视图更新。

3、游标

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次以行或者多行前进或向后浏览数据的能力。我们可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。

游标包含两个部分：游标结果集（定义该游标得SELECT语句返回的行的集合），游标位置（指向这个结果集某一行的当前指针）。

是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。

概述

游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL选择语句相关联由于游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必需声明一个指向该结果集的游标。３

索引视图事务游标锁详细介绍

假使曾经用C语言写过对文件进行处理的程序，那么游标就像您开启文件所得到的文件句柄一样，只要文件开启成功，该文件句柄就可代表该文件。对于游标而言，其道理是一致的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式浮现给程序。

游标的特性：

READONLY只读，只能取值而不能赋值；

NOTSCROOLABLE不可回滚，只能顺序读取；

ASENSITIVE敏感，不能在已经开启游标的表上执行update事务；

游标的优点：

1.允许程序对由查询语句Select返回的行集合中的每一行执行一致或不同的操作，而不是对整个行集合执行同一操作。

2.提供基于游标位置的表中的行进行删除和更新的能力。

3.游标实际上是作为面向数据库集合的数据库管理系统（DBMS）和面向行的程序设计之间的桥梁。

4、事务

被绑定在一起作为一个规律工作单元的SQL语句分组，假使任何一个语句操作失败那么整个操作就会失败，以后操作就会回滚到操作前状态，或者是上一个节点。为了确保要么执行，要么不执行，就可以使用事务。是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务寻常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begintransaction和endtransaction语句（或函数调用）来界定。事务由事务开始(begintransaction)和事务终止(endtransaction)之间执行的全体操作组成。一个事务可以是一条SQL语句，一组SQL语句或整个程序。

ACID特性：

原子性（atomicity）：一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，

要么都不做。就是说不允许事务部分的执行。即使由于故障而使事务不能完成，在rollback时也要消除对数据库的影响！

一致性（consistency）：事务必需是使数据库从一个一致性状态变到另一个一致性状态。一致

性与原子性是密切相关的。就拿网上购物来说，你只有既让商品出库又让商品进入顾客的购物车才能构成事务。

隔离性（isolation）：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的

数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰。假使多个事务并发执行，应象各个事务独立执行一样！

持久性（durability）：持续性也称永久性（permanence），指一个事务一旦提交，它对数据库

中数据的改变就应当是永久性的。接下来的其他操作或故障不应当对其有任何影响。

４

索引视图事务游标锁详细介绍

5、锁

在所有的DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。

锁主要用于多用户环境下保证数据库完整性和一致性。

MicrosoftSQLServer（以下简称SQLServer）作为一种中小型数据库管理系统，已经得到了广泛的应用，该系统更强调由系统来管理锁。在用户有SQL请求时，系统分析请求，自动在满足锁定条件和系统性能之间为数据库加上适当的锁，同时系统在运行期间往往自动进行优化处理，实行动态加锁。对于一般的用户而言，通过系统的自动锁定管理机制基本可以满足使用要求，但假使对数据安全、数据库完整性和一致性有特别要求，就必需自己控制数据库的锁定和解锁，这就需要了解SQLServer的锁机制，把握数据库锁定方法。

一、锁的多粒度性以及锁升级

数据库中的锁是指一种软件机制，用来指示某个用户（也即进程会话，下同）已经占用了某种资源，从而防止其他用户做出影响本用户的数据修改或导致数据库数据的非完整性和非一致性。这儿所谓资源，主要指用户可以操作的数据行、索引以及数据表等。根据资源的不同，锁有多粒度（multigranular）的概念，也就是指可以锁定的资源的层次。SQLServer中能够锁定的资源粒度包括：数据库、表、区域、页面、键值（指带有索引的行数据）、行标识符（RID，即表中的单行数据）。

采用多粒度锁的重要用途是用来支持并发操作和保证数据的完整性。SQLServer根据用户的请求，做出分析后自动给数据库加上适合的锁。假设某用户只操作一个表中的部分行数据，系统可能会只添加几个行锁（RID）或页面锁，这样可以尽可能多地支持多用户的并发操作。但是，假使用户事务中频繁对某个表中的多条记录操作，将导致对该表的大量记录行都加上了行级锁，数据库系统中锁的数目会急剧增加，这样就加重了系统负荷，影响系统性能。因此，在数据库系统中，一般都支持锁升级(lockescalation)。所谓锁升级是指调整锁的粒度，将多个低粒度的锁替换成少数的更高粒度的锁，以此来降低系统负荷。在SQLServer中当一个事务中的锁较多，达到锁升级门限时，系统自动将行级锁和页面锁升级为表级锁。特别值得注意的是，在SQLServer中，锁的升级门限以及锁升级是由系统自动来确定的，不需要用户设置。

二、锁的模式和兼容性

在数据库中加锁时，除了可以对不同的资源加锁，还可以使用不同程度的加锁方式，即锁有多种模式，SQLServer中锁模式包括：

1．共享锁

SQLServer中，共享锁用于所有的只读数据操作。共享锁是非独占的，允大量个并发事务读取其锁定的资源。默认状况下，数据被读取后，SQLServer马上释放共享锁。例如，执行查询“SELECT*FROMmy_table〞时，首先锁定第一页，读取之后，释放对第一页的锁定，然后锁定其次页。这样，就允许在读操作过程中，修改未被锁定的第一页。但是，事务隔离级别连接选项设置和SELECT语句中的锁定设置都可以改变SQLServer的这种默认设置。例如，“SELECT*FROMmy_tableHOLDLOCK〞就要求在整个查询过程中，保持对表的锁定，直到查询完成才释放锁定。

2．修改锁

修改锁在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。由于使用共享锁时，修改数据的操作分为两步，首先获得一个共享锁，读取数据，然后将共享锁升级为独占锁，然后再执行修改操作。这样假使同时有两个或多个事务同时对一个事务申请了共享锁，在修改数据的时候，这些事务都要将共享锁升级为独占锁。这时，这些事务都不会释放共享锁而是一直等待对方５

索引视图事务游标锁详细介绍

释放，这样就造成了死锁。假使一个数据在修改前直接申请修改锁，在数据修改的时候再升级为独占锁，就可以避免死锁。修改锁与共享锁是兼容的，也就是说一个资源用共享锁锁定后，允许再用修改锁锁定。

3．独占锁

独占锁是为修改数据而保存的。它所锁定的资源，其他事务不能读取也不能修改。独占锁不能和其他锁兼容。

4．结构锁

结构锁分为结构修改锁（Sch-M）和结构稳定锁（Sch-S）。执行表定义语言操作时，SQLServer采用Sch-M锁，编译查询时，SQLServer采用Sch-S锁。

5．意向锁

意向锁说明SQLServer有在资源的低层获得共享锁或独占锁的意向。例如，表级的共享意向锁说明事务意图将独占锁释放到表中的页或者行。意向锁又可以分为共享意向锁、独占意向锁和共享式独占意向锁。共享意向锁说明事务意图在共享意向锁所锁定的低层资源上放置共享锁来读取数据。独占意向锁说明事务意图在共享意向锁所锁定的低层资源上放置独占锁来修改数据。共享式独占锁说明事务允许其他事务使用共享锁来读取顶层资源，并意图在该资源低层上放置独占锁。

6．批量修改锁

批量复制数据时使用批量修改锁。可以通过表的TabLock提醒或者使用系统存储过程sp_tableoption的“tablelockonbulkload〞选项设定批量修改锁。

另外，SQLServer命令语句操作会影响锁定的方式，语句的组合也同样能产生不同的锁定。

三、锁冲突及其防止方法

在数据库系统中，死锁是指多个用户（进程）分别锁定了一个资源，并又试图请求锁定对方已经锁定的资源，这就产生了一个锁定请求环，导致多个用户（进程）都处于等待对方释放所锁定资源的状态。在SQLServer中，系统能够自动定期探寻和处理死锁问题。系统在每次探寻中标识所有等待锁定请求的进程会话，假使在下一次探寻中该被标识的进程仍处于等待状态，SQLServer就开始递归死锁探寻。当探寻检测到锁定请求环时，系统将根据各进程会话的死锁优先级别来终止一个优先级最低的事务，此后，系统回滚该事务，并向该进程发出1205号错误信息。这样，其他事务就有可能继续运行了。死锁优先级的设置语句为：

SETDEADLOCK_PRIORITY{LOW|NORMAL}

其中LOW说明该进程会话的优先级较低，在出现死锁时，可以首先中断该进程的事务。另外，各进程中通过设置LOCK_TIMEOUT选项能够设置进程处于锁定请求状态的最长等待时间。该设置的语句：SETLOCK_TIMEOUT{timeout_period}

其中，timeout_period以毫秒为单位。

四、理解了死锁的概念，在应用程序中就可以采用下面的一些方法来尽量避免死锁了：

（1）合理安排表访问顺序。

（2）在事务中尽量避免用户干预，尽量使一个事务处理的任务少些。

（3）采用脏读技术。脏读由于不对被访问的表加锁，而避免了锁冲突。在客户机/服务器应用环境中，有些事务往往不允许读脏数据，但在特定的条件下，我们可以用脏读。

（4）数据访问时域离散法。数据访问时域离散法是指在客户机/服务器结构中，采取各种控制手段控制对数据库或数据库中的对象访问时间段。主要通过以下方式实现:合理安排后台事务的执行时间，采用工作流对后台事务进行统一管理。工作流在管理任务时，一方面限制同一类任务的线程数（往往限制为1个），６

索引视图事务游标锁详细介绍

防止资源过多占用;另一方面合理安排不同任务执行时序、时间，尽量避免多个后台任务同时执行，另外，避免在前台交易高峰时间运行后台任务。

（5）数据存储空间离散法。数据存储空间离散法是指采取各种手段，将规律上在一个表中的数据分散到若干离散的空间上去，以便改善对表的访问性能。主要通过以下方法实现:第一，将大表按行或列分解为若干小表;其次，按不同的用户群分解。

（6）使用完可能低的隔离性级别。隔离性级别是指为保证数据库数据的完整性和一致性而使多用户事务隔离的程度，SQL92定义了4种隔离性级别：未提交读、提交读、可重复读和可串行。假使选择过高的隔离性级别，如可串行，虽然系统可以因实现更好隔离性而更大程度上保证数据的完整性和一致性，但各事务间冲突而死锁的机遇大大增加，大大影响了系统性能。

（7）使用BoundConnections。Boundconnections允许两个或多个事务连接共享事务和锁，而且任何一个事务连接要申请锁宛如另外一个事务要申请锁一样，因此可以允许这些事务共享数据而不会有加锁的冲突。

（8）考虑使用乐观锁定或使事务首先获得一个独占锁定。一个最常见的死锁状况发生在系列号生成器中，它们寻常是这样编写的：

假使有两个用户在同时运行这一事务，他们都会得到共享锁定并保持它。当两个用户都试图得到keytab表的独占锁定时，就会进入死锁。为了避免这种状况的发生，应将上述事务重写成如下形式：

以这种方式改写后，只有一个事务能得到keytab的独占锁定，其他进程必需等到第一个事务的完成，这样虽增加了执行时间，但避免了死锁。

假使要求在一个事务中具有读取的可重复能力，就要考虑以这种方式来编写事务，以获得资源的独占锁定，然后再去读数据。例如，假使一个事务需要检索出titles表中所有书的平均价格，并保证在update被应用前，结果不会改变，优化器就会分派一个独占的表锁定。考虑如下的SQL代码：

在这个事务中，重要的是没有其他进程修改表中任何行的price，或者说在事务终止时检索的值与事务开始时检索的值不同。这里的where子句看起来很奇怪，但是不管你相信与否，这是迄今为止优化器所遇到的最完美有效的where子句，尽管计算出的结果总是false。当优化器处理此查询时，由于它找不到任何有效的SARG，它的查询规划就会强制使用一个独占锁定来进行表扫描。此事务执行时，where子句马上得到一个false值，于是不会执行实际上的扫描，但此进程仍得到了一个独占的表锁定。

由于此进程现在已有一个独占的表锁，所以可以保证没有其他事务会修改任何数据行，能进行重复读，且避免了由于holdlock所引起的潜在性死锁。但是，要避免死锁，不可能不付出代价。在使用表锁定来尽可能地减少死锁的同时，也增加了对表锁定的争用。因此，在实现这种方法之前，你需要权衡一下：避免７

索引视图事务游标锁详细介绍

死锁是否比允许并发地对表进行访问更重要。

五、手工加锁

SQLServer系统中建议让系统自动管理锁，该系统会分析用户的SQL语句要求，自动为该请求加上适合的锁，而且在锁的数目太多时，系统会自动进行锁升级。如前所述，升级的门限由系统自动配置，并不需要用户配置。

在实际应用中，有时为了应用程序正确运行和保持数据的一致性，必需人为地给数据库的某个表加锁。譬如，在某应用程序的一个事务操作中，需要根据一编号对几个数据表做统计操作，为保证统计数据时间的一致性和正确性，从统计第一个表开始到全部表终止，其他应用程序或事务不能再对这几个表写入数据，这个时候，该应用程序希望在从统计第一个数据表开始或在整个事务开始时能够由程序人为地（显式地）锁定这几个表，这就需要用到手工加锁（也称显式加锁）技术。

六、

在SQLServer的SQL语句（SELECT、INSERT、DELETE、UPDATE）支持显式加锁。这4个语句在显式加锁的语法上类似，下面仅以SELECT语句为例给出语法：