SQL知识点培训讲义.docx
《SQL知识点培训讲义.docx》由会员分享，可在线阅读，更多相关《SQL知识点培训讲义.docx（28页珍藏版）》请在冰豆网上搜索。
SQL知识点培训讲义
提纲：
1、SQLServer锁的概念
2、SQLServer事物概念
3、解决数据库异常膨胀的技术思路
4、操作系统崩溃后数据库重新安装的流程
5、数据库性能优化
6、常见的SQLServer连接失败错误及解决方法
7、无法合理使用已建立的索引的错误写法
8、常见系统函数
9、编写可读性强的代码
1.SQLServer锁的概念
1.1锁定
MicrosoftSQLServer使用锁定确保事务完整性和数据库一致性。
锁定可以防止用户读取正在由其他用户更改的数据，并可以防止多个用户同时更改相同数据。
如果不使用锁定，则数据库中的数据可能在逻辑上不正确，并且对数据的查询可能会产生意想不到的结果。
虽然SQLServer自动强制锁定，但可以通过了解锁定并在应用程序中自定义锁定来设计更有效的应用程序
1.2并发问题
如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题。
并发问题包括：
∙丢失或覆盖更新。
∙未确认的相关性（脏读）。
∙不一致的分析（非重复读）。
∙幻像读。
1.2.1丢失更新
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。
每个事务都不知道其它事务的存在。
最后的更新将重写由其它事务所做的更新，这将导致数据丢失。
例如，两个编辑人员制作了同一文档的电子复本。
每个编辑人员独立地更改其复本，然后保存更改后的复本，这样就覆盖了原始文档。
最后保存其更改复本的编辑人员覆盖了第一个编辑人员所做的更改。
如果在第一个编辑人员完成之后第二个编辑人员才能进行更改，则可以避免该问题。
1.2.2未确认的相关性（脏读）
当第二个事务选择其它事务正在更新的行时，会发生未确认的相关性问题。
第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。
例如，一个编辑人员正在更改电子文档。
在更改过程中，另一个编辑人员复制了该文档（该复本包含到目前为止所做的全部更改）并将其分发给预期的用户。
此后，第一个编辑人员认为目前所做的更改是错误的，于是删除了所做的编辑并保存了文档。
分发给用户的文档包含不再存在的编辑内容，并且这些编辑内容应认为从未存在过。
如果在第一个编辑人员确定最终更改前任何人都不能读取更改的文档，则可以避免该问题。
1.2.3不一致的分析（非重复读）
当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。
不一致的分析与未确认的相关性类似，因为其它事务也是正在更改第二个事务正在读取的数据。
然而，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。
而且，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都由其它事务更改；因而该行被非重复读取。
例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。
当编辑人员第二次读取文档时，文档已更改。
原始读取不可重复。
如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。
1.2.4幻像读
当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读问题。
事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其它事务删除。
同样，由于其它事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。
例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。
如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。
1.3隔离级别
当锁定用作并发控制机制时，它可以解决并发问题。
这使所有事务得以在彼此完全隔离的环境中运行，但是任何时候都可以有多个正在运行的事务。
可串行性是通过运行一组并发事务达到的数据库状态，等同于这组事务按某种顺序连续执行时所达到的数据库状态。
SQL-92隔离级别
尽管可串行性对于事务确保数据库中的数据在所有时间内的正确性相当重要，然而许多事务并不总是要求完全的隔离。
例如，多个作者工作于同一本书的不同章节。
新章节可以在任意时候提交到项目中。
但是，对于已经编辑过的章节，没有编辑人员的批准，作者不能对此章节进行任何更改。
这样，尽管有未编辑的新章节，但编辑人员仍可以确保在任意时间该书籍项目的正确性。
编辑人员可以查看以前编辑的章节以及最近提交的章节。
事务准备接受不一致数据的级别称为隔离级别。
隔离级别是一个事务必须与其它事务进行隔离的程度。
较低的隔离级别可以增加并发，但代价是降低数据的正确性。
相反，较高的隔离级别可以确保数据的正确性，但可能对并发产生负面影响。
应用程序要求的隔离级别确定了SQLServer使用的锁定行为。
SQL-92定义了下列四种隔离级别，SQLServer支持所有这些隔离级别：
∙未提交读（事务隔离的最低级别，仅可保证不读取物理损坏的数据）。
∙提交读（SQLServer默认级别）。
∙可重复读。
∙可串行读（事务隔离的最高级别，事务之间完全隔离）。
如果事务在可串行读隔离级别上运行，则可以保证任何并发重叠事务均是串行的。
下面四种隔离级别允许不同类型的行为。
隔离级别
脏读
不可重复读取
幻像
未提交读
是
是
是
提交读
否
是
是
可重复读
否
否
是
可串行读
否
否
否
事务必须运行于可重复读或更高的隔离级别以防止丢失更新。
当两个事务检索相同的行，然后基于原检索的值对行进行更新时，会发生丢失更新。
如果两个事务使用一个UPDATE语句更新行，并且不基于以前检索的值进行更新，则在默认的提交读隔离级别不会发生丢失更新。
1.4SQLServer中的锁定介绍
Microsoft®SQLServer™2000具有多粒度锁定，允许一个事务锁定不同类型的资源。
为了使锁定的成本减至最少，SQLServer自动将资源锁定在适合任务的级别。
锁定在较小的粒度（例如行）可以增加并发但需要较大的开销，因为如果锁定了许多行，则需要控制更多的锁。
锁定在较大的粒度（例如表）就并发而言是相当昂贵的，因为锁定整个表限制了其它事务对表中任意部分进行访问，但要求的开销较低，因为需要维护的锁较少。
SQLServer可以锁定以下资源（按粒度增加的顺序列出）。
资源
描述
RID
行标识符。
用于单独锁定表中的一行。
键
索引中的行锁。
用于保护可串行事务中的键范围。
页
8千字节（KB）的数据页或索引页。
扩展盘区
相邻的八个数据页或索引页构成的一组。
表
包括所有数据和索引在内的整个表。
DB
数据库。
SQLServer使用不同的锁模式锁定资源，这些锁模式确定了并发事务访问资源的方式。
SQLServer使用以下资源锁模式。
锁模式
描述
共享（S）
用于不更改或不更新数据的操作（只读操作），如SELECT语句。
更新（U）
用于可更新的资源中。
防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
排它（X）
用于数据修改操作，例如INSERT、UPDATE或DELETE。
确保不会同时对同一资源进行多重更新。
意向
用于建立锁的层次结构。
意向锁的类型为：
意向共享（IS）、意向排它（IX）以及与意向排它共享（SIX）。
架构
在执行依赖于表架构的操作时使用。
架构锁的类型为：
架构修改（Sch-M）和架构稳定性（Sch-S）。
大容量更新（BU）
向表中大容量复制数据并指定了TABLOCK提示时使用。
共享锁
共享（S）锁允许并发事务读取（SELECT）一个资源。
资源上存在共享（S）锁时，任何其它事务都不能修改数据。
一旦已经读取数据，便立即释放资源上的共享（S）锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享（S）锁。
更新锁
更新（U）锁可以防止通常形式的死锁。
一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享（S）锁，然后修改行，此操作要求锁转换为排它（X）锁。
如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它（X）锁。
共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。
第二个事务试图获取排它（X）锁以进行更新。
由于两个事务都要转换为排它（X）锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。
若要避免这种潜在的死锁问题，请使用更新（U）锁。
一次只有一个事务可以获得资源的更新（U）锁。
如果事务修改资源，则更新（U）锁转换为排它（X）锁。
否则，锁转换为共享锁。
排它锁
排它（X）锁可以防止并发事务对资源进行访问。
其它事务不能读取或修改排它（X）锁锁定的数据。
意向锁
意向锁表示SQLServer需要在层次结构中的某些底层资源上获取共享（S）锁或排它（X）锁。
例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享（S）锁。
在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它（X）锁。
意向锁可以提高性能，因为SQLServer仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。
而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。
意向锁包括意向共享（IS）、意向排它（IX）以及与意向排它共享（SIX）。
锁模式
描述
意向共享（IS）
通过在各资源上放置S锁，表明事务的意向是读取层次结构中的部分（而不是全部）底层资源。
意向排它（IX）
通过在各资源上放置X锁，表明事务的意向是修改层次结构中的部分（而不是全部）底层资源。
IX是IS的超集。
与意向排它共享（SIX）
通过在各资源上放置IX锁，表明事务的意向是读取层次结构中的全部底层资源并修改部分（而不是全部）底层资源。
允许顶层资源上的并发IS锁。
例如，表的SIX锁在表上放置一个SIX锁（允许并发IS锁），在当前所修改页上放置IX锁（在已修改行上放置X锁）。
虽然每个资源在一段时间内只能有一个SIX锁，以防止其它事务对资源进行更新，但是其它事务可以通过获取表级的IS锁来读取层次结构中的底层资源。
架构锁
执行表的数据定义语言（DDL）操作（例如添加列或除去表）时使用架构修改（Sch-M）锁。
当编译查询时，使用架构稳定性（Sch-S）锁。
架构稳定性（Sch-S）锁不阻塞任何事务锁，包括排它（X）锁。
因此在编译查询时，其它事务（包括在表上有排它（X）锁的事务）都能继续运行。
但不能在表上执行DDL操作。
大容量更新锁
当将数据大容量复制到表，且指定了TABLOCK提示或者使用sp_tableoption设置了tablelockonbulk表选项时，将使用大容量更新（BU）锁。
大容量更新（BU）锁允许进程将数据并发地大容量复制到同一表，同时防止其它不进行大容量复制数据的进程访问该表。
1.5阻塞
任何基于锁的并发系统都有可能在某种情况下发生阻塞。
当一个连接控制了一个锁，而另一个连接需要冲突的锁类型时，将发生阻塞。
其结果是强制第二个连接等待，而在第一个连接上阻塞。
不管是来自同一应用程序还是另外一台客户机上单独的应用程序，一个连接都可以阻塞另另一个连接。
大多数阻塞问题的发生是因为一个进程控制锁的时间过长，导致阻塞的进程链都在其他进程上等待锁。
常见的阻塞情形如下：
（1）提交执行的时间长的查询
长时间运行的查询会阻塞其它查询。
例如，影响很多行的DELETE或UPDATE操作能获取很多锁，这些锁不论是否升级到表锁都堵塞其它查询。
因此，一般不要将长时间运行的决策支持查询和联机事物处理（OLTP）查询混在一起。
解决方案是想办法优化查询，如更改索引、将大的复杂查询分成简单的查询或在空闲时间或单独的计算机运行查询。
（2）取消没有提交或回滚的查询
如果应用程序取消查询（如采用ODBC的SQLCANCEL函数），但没有同时发出所需数目的ROLLBACK和commit语句，则会发生这种情况。
取消查询并不自动回滚偶或提交事物。
取消查询后，所有在事物内获取的锁都将保留。
应用程序必须提交或回滚已取消的事物，从而正确地管理事物嵌套。
（3）应用程序没处理完所有结果
将查询发送到服务器后，所用应用程序必须理解完成提取所用结果行的过程。
如果应用程序没有提取所用结果行，锁可能会留在表上而阻塞其它用户。
如果使用的应用程序将Transact-SQL语句透明地提交给服务器，则该应用程序必须提取所用结果行。
如果应用程序没这样做，则可能无法解决阻塞问题。
为避免此问题，可以将这些应用程序限制在报表或决策支持数据库上。
若要避免此此问题，请执行下列任一操作。
对每个查询使用查询超时。
设定查询超时的方法如下。
使用系统存储过程sp_serveroption设置。
对每个查询使用锁定超时
LOCK_TIMEOUT设置运行应用程序设置语句等待阻塞资源的最长时间
使用绑定连接
避免阻塞
设计应用程序以比慢阻塞的准则包括如下方面：
（1）不要试用或设计试用户得以填写编辑框的应用程序，因为编辑框会运行时间较长的查询。
例如，不要使用或设计提示用户输入的应用程序，运行某些字段保留空白或允许输入通配符，这可能导致应用程序提交运行时间过长的查询，从而导致阻塞问题。
（2）不要使用或设计使用户得以在事物内输入内容的应用程序。
（3）允许取消查询。
（4）使用查询或锁定超时，防止失控查询和避免分布式死锁。
（5）立即完成提取所有结果行。
（6）使事物尽可能简短。
（7）在所预计的并发用户全符合下对应用程序进行应力测试。
1.6死锁
当某组资源的两个或多个线程之间有循环相关性时，将发生死锁。
死锁是一种可能发生在任何多线程系统中的状态，而不仅仅发生在关系数据库管理系统中。
多线程系统中的一个线程可能获取一个或多个资源（如锁）。
如果正获取的资源当前为另一线程所拥有，则第一个线程可能必须等待拥有线程释放目标资源。
这时就说等待线程在那个特定资源上与拥有线程有相关性。
如果拥有线程需要获取另外一个资源，而该资源当前为等待线程所拥有，则这种情形将成为死锁：
在事务提交或回滚之前两个线程都不能释放资源，而且它们因为正等待对方拥有的资源而不能提交或回滚事务。
例如，运行事务1的线程T1具有Supplier表上的排它锁。
运行事务2的线程T2具有Part表上的排它锁，并且之后需要Supplier表上的锁。
事务2无法获得这一锁，因为事务1已拥有它。
事务2被阻塞，等待事务1。
然后，事务1需要Part表的锁，但无法获得锁，因为事务2将它锁定了。
事务在提交或回滚之前不能释放持有的锁。
因为事务需要对方控制的锁才能继续操作，所以它们不能提交或回滚。
说明 死锁经常与正常阻塞混淆。
当一个事务锁定了另一个事务需要的资源，第二个事务等待锁被释放。
默认情况下，SQLServer事务不会超时（除非设置了LOCK_TIMEOUT）。
第二个事务被阻塞，而不是被死锁。
检测和结束死锁
在识别死锁后，SQLServer通过自动选择可以打破死锁的线程（死锁牺牲品）来结束死锁。
SQLServer回滚作为死锁牺牲品的事务，通知线程的应用程序（通过返回1205号错误信息），取消线程的当前请求，然后允许不间断线程的事务继续进行。
SQLServer通常选择运行撤消时花费最少的事务的线程作为死锁牺牲品。
另外，用户可以使用SET语句将会话的DEADLOCK_PRIORITY设置为LOW。
DEADLOCK_PRIORITY选项控制在死锁情况下如何衡量会话的重要性。
如果会话的设置为LOW，则当会话陷入死锁情况时将成为首选牺牲品。
识别死锁后，SQLServer选择特定的线程作为死锁牺牲品，并返回一条列出死锁中涉及的资源的错误信息。
该死锁信息采用下列形式：
Yourtransaction（processID#52）wasdeadlockedon{lock|communicationbuffer|thread}resourceswithanotherprocessandhasbeenchosenasthedeadlockvictim.Rerunyourtransaction.
死锁中涉及的线程和资源位于错误日志中
将死锁减至最少
虽然不能完全避免死锁，但可以使死锁的数量减至最少。
将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务：
∙回滚，而回滚会取消事务执行的所有工作。
∙由于死锁时回滚而由应用程序重新提交。
下列方法有助于最大限度地降低死锁：
∙按同一顺序访问对象。
∙避免事务中的用户交互。
∙保持事务简短并在一个批处理中。
∙使用低隔离级别。
∙使用绑定连接。
按同一顺序访问对象
如果所有并发事务按同一顺序访问对象，则发生死锁的可能性会降低。
例如，如果两个并发事务获得Supplier表上的锁，然后获得Part表上的锁，则在其中一个事务完成之前，另一个事务被阻塞在Supplier表上。
第一个事务提交或回滚后，第二个事务继续进行。
不发生死锁。
将存储过程用于所有的数据修改可以标准化访问对象的顺序。
避免事务中的用户交互
避免编写包含用户交互的事务，因为运行没有用户交互的批处理的速度要远远快于用户手动响应查询的速度，例如答复应用程序请求参数的提示。
例如，如果事务正在等待用户输入，而用户去吃午餐了或者甚至回家过周末了，则用户将此事务挂起使之不能完成。
这样将降低系统的吞吐量，因为事务持有的任何锁只有在事务提交或回滚时才会释放。
即使不出现死锁的情况，访问同一资源的其它事务也会被阻塞，等待该事务完成。
保持事务简短并在一个批处理中
在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。
事务运行时间越长，其持有排它锁或更新锁的时间也就越长，从而堵塞了其它活动并可能导致死锁。
保持事务在一个批处理中，可以最小化事务的网络通信往返量，减少完成事务可能的延迟并释放锁。
使用低隔离级别
确定事务是否能在更低的隔离级别上运行。
执行提交读允许事务读取另一个事务已读取（未修改）的数据，而不必等待第一个事务完成。
使用较低的隔离级别（例如提交读）而不使用较高的隔离级别（例如可串行读）可以缩短持有共享锁的时间，从而降低了锁定争夺。
使用绑定连接
使用绑定连接使同一应用程序所打开的两个或多个连接可以相互合作。
次级连接所获得的任何锁可以象由主连接获得的锁那样持有，反之亦然，因此不会相互阻塞。
2.SQLServer事物概念
2.1事务
事务是作为单个逻辑工作单元执行的一系列操作。
一个逻辑工作单元必须有四个属性，称为ACID（原子性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务：
原子性
事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。
一致性
事务在完成时，必须使所有的数据都保持一致状态。
在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。
事务结束时，所有的内部数据结构（如B树索引或双向链表）都必须是正确的。
隔离性
由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。
事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。
这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。
持久性
事务完成之后，它对于系统的影响是永久性的。
该修改即使出现系统故障也将一直保持
2.2显式事务
显式事务可以显式地在其中定义事务的启动和结束。
在Microsoft®SQLServer™的早期版本中，显式事务也称为用户定义或用户指定的事务。
DB-Library应用程序和Transact-SQL脚本使用BEGINTRANSACTION、COMMITTRANSACTION、COMMITWORK、ROLLBACKTRANSACTION或ROLLBACKWORKTransact-SQL语句定义显式事务。
BEGINTRANSACTION
为连接标记显式事务的起始点。
COMMITTRANSACTION或COMMITWORK
如果没有遇到错误，可使用该语句成功地结束事务。
该事务中的所有数据修改在数据库中都将永久有效。
事务占用的资源将被释放。
ROLLBACKTRANSACTION或ROLLBACKWORK
用来清除遇到错误的事务。
该事务修改的所有数据都返回到事务开始时的状态。
事务占用的资源将被释放。
2.3嵌套事务
显式事务可以嵌套。
这主要是为了支持存储过程中的一些事务，这些事务可以从事务中已有的进程中调用，也可以从没有活动事务的进程中调用。
下面的示例显示了嵌套事务的用途。
TransProc过程强制执行其事务，而不管执行该事务的进程的事务模式如何。
如果在事务活动时调用TransProc，则TransProc中的嵌套事务几乎被忽略，而且其INSERT语句将根据对外部事务采取的最终操作提交或回滚。
如果不含有未完成事务的进程执行TransProc，则在该过程结束时，COMMITTRANSACTION将有效地提交INSERT语句。
SETQUOTED_IDENTIFIEROFF
GO
SETNOCOUNTOFF
GO
USEpubs
GO
CREATETABLETestTrans（ColaINTPRIMARYKEY,
ColbCHAR（3）NOTNULL）
GO
CREATEPROCEDURETransProc@PriKeyINT,@CharColCHAR（3）AS
BEGINTRANSACTIONInProc
INSERTINTOTestTransVALUES（@PriKey,@CharCol）
INSERTINTOTestTransVALUES（@PriKey+1,@CharCol）
COMMITTRANSACTIONInProc
GO
/*StartatransactionandexecuteTransProc*/
BEGINTRANSACTIONOutOfProc
GO
EXECTransProc1,'aaa'
GO
/*Rollbacktheoutertransaction,thiswill
rollbackTransProc'snestedtransaction*/
ROLLBACKTRANSACTIONOutOfProc
GO
EXECUTETransProc3,'bbb'
GO
/*ThefollowingSELECTstatementshowsonlyrows3and4are
stillinthetable.Thisindicatesthatthecommit
oftheinnertransactio
升级会员 