Go homepage(回首页) Upload pictures (上传图片) Write articles (发文字帖)
The author:(作者)归海一刀published in(发表于) 2014/2/1 0:12:50 SQL,Server数据库事务锁机制的分析_[SQL,Server教程]
当搜索检测到锁定请求环时,系统将根据各进程会话的死锁优先级别来结束一个优先级最低的事务,此后,系统回滚该事务,并向该进程发出1205号错误信息。这样,其他事务就有可能继续运行了。死锁优先级的设置语句为:
SET DEADLOCK_PRIORITY { LOW | NORMAL}
其中LOW说明该进程会话的优先级较低,在出现死锁时,可以首先中断该进程的事务。另外,各进程中通过设置LOCK_TIMEOUT选项能够设置进程处于锁定请求状态的最长等待时间。该设置的语句:
SET LOCK_TIMEOUT { timeout_period }
其中,timeout_period以毫秒为单位。 理解了死锁的概念,在应用程序中就可以采用下面的一些方法来尽量避免死锁了: (1)合理安排表访问顺序。 (2)在事务中尽量避免用户干预,尽量使一个事务处理的任务少些。 (3)采用脏读技术。脏读由于不对被访问的表加锁,而避免了锁冲突。在客户机/服务器应用环境中,有些事务往往不允许读脏数据,但在特定的条件下,我们可以用脏读。 (4)数据访问时域离散法。数据访问时域离散法是指在客户机/服务器结构中,采取各种控制手段控制对数据库或数据库中的对象访问时间段。主要通过以下方式实现: 合理安排后台事务的执行时间,采用工作流对后台事务进行统一管理。工作流在管理任务时,一方面限制同一类任务的线程数(往往限制为1个),防止资源过多占用; 另一方面合理安排不同任务执行时序、时间,尽量避免多个后台任务同时执行,另外, 避免在前台交易高峰时间运行后台任务。 (5)数据存储空间离散法。数据存储空间离散法是指采取各种手段,将逻辑上在一个表中的数据分散到若干离散的空间上去,以便改善对表的访问性能。主要通过以下方法实现: 第一,将大表按行或列分解为若干小表; 第二,按不同的用户群分解。 (6)使用尽可能低的隔离性级别。隔离性级别是指为保证数据库数据的完整性和一致性而使多用户事务隔离的程度,SQL92定义了4种隔离性级别:未提交读、提交读、可重复读和可串行。如果选择过高的隔离性级别,如可串行,虽然系统可以因实现更好隔离性而更大程度上保证数据的完整性和一致性,但各事务间冲突而死锁的机会大大增加,大大影响了系统性能。 (7)使用Bound Connections。Bound connections 允许两个或多个事务连接共享事务和锁,而且任何一个事务连接要申请锁如同另外一个事务要申请锁一样,因此可以允许这些事务共享数据而不会有加锁的冲突。 (8)考虑使用乐观锁定或使事务首先获得一个独占锁定。一个最常见的死锁情况发生在系列号生成器中,它们通常是这样编写的:
begin tran select new_id from keytab holdlock update keytab set new_id=new_id+l commit tran 如果有两个用户在同时运行这一事务,他们都会得到共享锁定并保持它。当两个用户都试图得到keytab表的独占锁定时,就会进入死锁。为了避免这种情况的发生,应将上述事务重写成如下形式:
begin tran update keytab set new_id=new_id+l select new_id from keytab commit tran
以这种方式改写后,只有一个事务能得到keytab的独占锁定,其他进程必须等到第一个事务的完成,这样虽增加了执行时间,但避免了死锁。 如果要求在一个事务中具有读取的可重复能力,就要考虑以这种方式来编写事务,以获得资源的独占锁定,然后再去读数据。例如,如果一个事务需要检索出titles表中所有书的平均价格,并保证在update被应用前,结果不会改变,优化器就会分配一个独占的表锁定。考虑如下的SQL代码:
begin tran update titles set title_idid=title_id . where 1=2 if (selectavg(price)fromtitles)>15 begin /**//* perform some additional processing */ end update titles set price=price*1.10 where price<(select avg(price)from titles) commit tran
在这个事务中,重要的是没有其他进程修改表中任何行的price,或者说在事务结束时检索的值与事务开始时检索的值不同。这里的where子句看起来很奇怪,但是不管你相信与否,这是迄今为止优化器所遇到的最完美有效的where子句,尽管计算出的结果总是false。当优化器处理此查询时,因为它找不到任何有效的SARG,它的查询规划就会强制使用一个独占锁定来进行表扫描。此事务执行时,where子句立即得到一个false值,于是不会执行实际上的扫描,但此进程仍得到了一个独占的表锁定。 因为此进程现在已有一个独占的表锁,所以可以保证没有其他事务会修改任何数据行,能进行重复读,且避免了由于holdlock所引起的潜在性死锁。但是,要避免死锁,不可能不付出代价。在使用表锁定来尽可能地减少死锁的同时,也增加了对表锁定的争用。因此,在实现这种方法之前,你需要权衡一下:避免死锁是否比允许并发地对表进行访问更重要。
手工加锁 SQL Server系统中建议让系统自动管理锁,该系统会分析用户的SQL语句要求,自动为该请求加上合适的锁,而且在锁的数目太多时,系统会自动进行锁升级。如前所述,升级的门限由系统自动配置,并不需要用户配置。 在实际应用中,有时为了应用程序正确运行和保持数据的一致性,必须人为地给数据库的某个表加锁。比如,在某应用程序的一个事务操作中,需要根据一编号对几个数据表做统计操作,为保证统计数据时间的一致性和正确性,从统计第一个表开始到全部表结束,其他应用程序或事务不能再对这几个表写入数据,这个时候,该应用程序希望在从统计第一个数据表开始或在整个事务开始时能够由程序人为地(显式地)锁定这几个表,这就需要用到手工加锁(也称显式加锁)技术。 在SQL Server 的SQL语句(SELECT、INSERT、DELETE、UPDATE)支持显式加锁。这4个语句在显式加锁的语法上类似,下面仅以SELECT语句为例给出语法: SELECT FROM [ WITH ] 其中,指需要在该语句执行时添加在该表上的锁类型。所指定的锁类型有如下几种: 1.HOLDLOCK: 在该表上保持共享锁,直到整个事务结束,而不是在语句执行完立即释放所添加的锁。 2.NOLOCK:不添加共享锁和排它锁,当这个选项生效后,可能读到未提交读的数据或“脏数据”,这个选项仅仅应用于SELECT语句。 3. PAGLOCK:指定添加页面锁(否则通常可能添加表锁)。 4.READCOMMITTED:设置事务为读提交隔离性级别。 5.READPAST: 跳过已经加锁的数据行,这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行,而不是阻塞直到其他事务释放锁,READPAST仅仅应用于READ COMMITTED隔离性级别下事务操作中的SELECT语句操作。 6.READUNCOMMITTED:等同于NOLOCK。 7.REPEATABLEREAD:设置事务为可重复读隔离性级别。 8.ROWLOCK:指定使用行级锁。 9.SERIALIZABLE:设置事务为可串行的隔离性级别。 10.TABLOCK:指定使用表级锁,而不是使用行级或页面级的锁,SQL Server在该语句执行完后释放这个锁,而如果同时指定了HOLDLOCK,该锁一直保持到这个事务结束。 11.TABLOCKX:指定在表上使用排它锁,这个锁可以阻止其他事务读或更新这个表的数据,直到这个语句或整个事务结束。 12. UPDLOCK :指定在读表中数据时设置修改锁(update lock)而不是设置共享锁,该锁一直保持到这个语句或整个事务结束,使用UPDLOCK的作用是允许用户先读取数据(而且不阻塞其他用户读数据),并且保证在后来再更新数据时,这一段时间内这些数据没有被其他用户修改。 由上可见,在SQL Server中可以灵活多样地为SQL语句显式加锁,若适当使用,我们完全可以完成一些程序的特殊要求,保证数据的一致性和完整性。对于一般使用者而言,了解锁机制并不意味着必须使用它。事实上,SQL Server建议让系统自动管理数据库中的锁,而且一些关于锁的设置选项也没有提供给用户和数据库管理人员,对于特殊用户,通过给数据库中的资源显式加锁,可以满足很高的数据一致性和可靠性要求,只是需要特别注意避免死锁现象的出现。
来源:网络
赞