对锁机制的研究要具备两个条件:
1.数据量大
2.多个用户同时并发
如果缺少这两个条件,数据库不容易产生死锁问题。研究起来可能会事倍功半。如果这两个条件都有,但你还是按数据库缺省设置来处理数据,则会带来很多的问题,比如:
1)丢失更新
A,B两个用户读同一数据并进行修改,其中一个用户的修改结果破坏了另一个修改的结果
2)脏读
A用户修改了数据时,B用户也在读该数据,但A用户因为某些原因取消了对数据的修改,数据恢复原值,此时B得到的数据就与数据库内的数据产生了不一致
3)不可重复读
B用户读出该数据并修改,同时,A用户也在读取数据,此时A用户再读取数据时发现前后两次的值不一致
SQL SERVER 作为多用户数据库系统,以事务为单位,使用锁来实现并发控制。SQLSERVER使用“锁”确保事务完整性和数据一致性。
一、锁的概念
锁(LOCKING)是最常用的并发控制机构。是防止其他事务访问指定的资源控制、实现并发控制的一种主要手段。锁是事务对某个数据库中的资源(如表和记录)存取前,先向系统提出请求,封锁该资源,事务获得锁后,即取得对数据的控制权,在事务释放它的锁之前,其他事务不能更新此数据。当事务撤消后,释放被锁定的资源。
当一个用户锁住数据库中的某个对象时,其他用户就不能再访问该对象
二、锁的粒度
SQL Server 2000 具有多粒度锁定,允许一个事务锁定不同类型的的资源。为了使锁定的成本减至最少,SQL Server 自动将资源锁定在适合任务的级别。锁定在较小的粒度(例如行)可以增加并发但需要较大的开销,因为如果锁定了许多行,则需要控制更多的锁。锁定在较大的粒度(例如表)就并发而言是相当昂贵的,因为锁定整个表限制了其它事务对表中任意部分进行访问,但要求的开销较低,因为需要维护的锁较少。SQL Server 可以锁定行、页、扩展盘区、表、库等资源。
- 资源 级别 描述
- RID 行锁 表中的单个行
- Key 行级锁 索引中的行
- Page 页级锁 一个数据页或者索引页
- Extent 页级锁 一组数据页或者索引页
- Table 表级锁 整个表
- Database 数据库级锁 整个数据库
选择多大的粒度,根据对数据的操作而定。如果是更新表中所有的行,则用表级锁;如果是更新表中的某一行,则用行级锁。
行级锁是一种最优锁,因为行级锁不可能出现数据既被占用又没有使用的浪费现象。但是,如果用户事务中频繁对某个表中的多条记录操作,将导致对该表的许多记录行都加上了行级锁,数据库系统中锁的数目会急剧增加,这样就加重了系统负荷,影响系统性能。因此,在SQL Server中,还支持锁升级(lock escalation)。
所谓锁升级是指调整锁的粒度,将多个低粒度的锁替换成少数的更高粒度的锁,以此来降低系统负荷。在SQL Server中当一个事务中的锁较多,达到锁升级门限时,系统自动将行级锁和页面锁升级为表级锁。
特别值得注意的是,在SQL Server中,锁的升级门限以及锁升级是由系统自动来确定的,不需要用户设置。
三、锁的模式
锁模式以及描述表
- 锁模式 描述
- 共享(S) 用于不更改或不更新数据(只读操作),如SELECT语句
- 更新(U) 用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
- 排它(X) 用于数据修改操作,例如 INSERT、UPDATE或DELETE。确保不会同时对同一资源进行多重更新
- 意向 当 Microsoft SQL Server 数据库引擎获取低级别的锁时,它还将在包含更低级别对象的对象上放置意向锁.例如: 当锁定行或索引键范围时,数据库引擎将在包含行或键的页上放置意向锁。当锁定页时,数据库引擎将在包含页的更高级别的对象上放置意向锁。
意向锁的类型为:意向共享(IS)、意向排它(IX)以及意向排它共享(SIX) - 架构 在执行依赖于表架构的操作时使用。架构锁的类型为:架构修改(Sch-M)和架构稳定(Sch-S)
- 大容量更新(BU) 向表中大容量复制数据并指定了TABLOCK提示时使用
四 SQL Server 中锁的设置
1 处理死锁和设置死锁优先级
死锁就是多个用户申请不同封锁,由于申请者均拥有一部分封锁权而又等待其他用户拥有的部分封锁而引起的无休止的等待
可以使用SET DEADLOCK_PRIORITY控制在发生死锁情况时会话的反应方式。
Syntax:
SET DEADLOCK_PRIORITY { LOW | NORMAL}
其中LOW说明该进程会话的优先级较低,在出现死锁时,可以首先中断该进程的事务。
2 处理超时和设置锁超时持续时间。
@@LOCK_TIMEOUT 返回当前会话的当前锁超时设置,单位为毫秒
SET LOCK_TIMEOUT 设置允许应用程序设置语句等待阻塞资源的最长时间。当语句等待的时间大于 LOCK_TIMEOUT 设置时,系统将自动取消阻塞的语句,并给应用程序返回"已超过了锁请求超时时段"的 1222 号错误信息
示例
1)将锁超时期限设置为 1,800 毫秒。
SET LOCK_TIMEOUT 1800
2) 配置索引的锁定粒度
可以使用 sp_indexoption 系统存储过程来设置用于索引的锁定粒度
3)设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL
五 查看锁的信息
1 执行 EXEC SP_LOCK 报告有关锁的信息
2 查询分析器中按Ctrl+2可以看到锁的信息
六、奇怪的sql语句
- begin tran
- update titles set title_idid=title_id where 1=2
- if (selectavg(price)fromtitles)>$15
- begin
- update titles set price=price*1.10
- where price<(select avg(price)from titles)
- end
- commit tran
begin tran update titles set title_idid=title_id where 1=2 if (selectavg(price)fromtitles)>$15 begin update titles set price=price*1.10 where price<(select avg(price)from titles) end commit tran
update titles set title_idid=title_id where 1=2,这个条件是永远也不会成立的,如此写的含义是什么呢?
这里的where子句看起来很奇怪,尽管计算出的结果总是false。当优化器处理此查询时,因为它找不到任何有效的SARG,它的查询规划就会强制使用一个独占锁定来进行表扫描。此事务执行时,where子句立即得到一个false值,于是不会执行实际上的扫描,但此进程仍得到了一个独占的表锁定。
因为此进程现在已有一个独占的表锁,所以可以保证没有其他事务会修改任何数据行,能进行重复读,且避免了由于holdlock所引起的潜在性死锁。
但是,在使用表锁定来尽可能地减少死锁的同时,也增加了对表锁定的争用。因此,在实现这种方法之前,你需要权衡一下:避免死锁是否比允许并发地对表进行访问更重要。
所以,在这个事务中,没有其他进程修改表中任何行的price。
七 如何避免死锁
1 使用事务时,尽量缩短事务的逻辑处理过程,及早提交或回滚事务;
2 设置死锁超时参数为合理范围,如:3分钟-10分种;超过时间,自动放弃本次操作,避免进程悬挂;
3 所有的SP都要有错误处理(通过@error)
4 一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁
5 优化程序,检查并避免死锁现象出现;
1)合理安排表访问顺序
2)在事务中尽量避免用户干预,尽量使一个事务处理的任务少些。
3)采用脏读技术。脏读由于不对被访问的表加锁,而避免了锁冲突。在客户机/服务器应用环境中,有些事务往往不允许读脏数据,但在特定的条件下,我们可以用脏读。
4)数据访问时域离散法。数据访问时域离散法是指在客户机/服务器结构中,采取各种控制手段控制对数据库或数据库中的对象访问时间段。主要通过以下方式实现: 合理安排后台事务的执行时间,采用工作流对后台事务进行统一管理。工作流在管理任务时,一方面限制同一类任务的线程数(往往限制为1个),防止资源过多占用; 另一方面合理安排不同任务执行时序、时间,尽量避免多个后台任务同时执行,另外,避免在前台交易高峰时间运行后台任务
5)数据存储空间离散法。数据存储空间离散法是指采取各种手段,将逻辑上在一个表中的数据分散到若干离散的空间上去,以便改善对表的访问性能。主要通过以下方法实现: 第一,将大表按行或列分解为若干小表; 第二,按不同的用户群分解。
6)使用尽可能低的隔离性级别。隔离性级别是指为保证数据库数据的完整性和一致性而使多用户事务隔离的程度,SQL92定义了4种隔离性级别:未提交读、提交读、可重复读和可串行。如果选择过高的隔离性级别,如可串行,虽然系统可以因实现更好隔离性而更大程度上保证数据的完整性和一致性,但各事务间冲突而死锁的机会大大增加,大大影响了系统性能。
7)使用Bound Connections。Bound connections 允许两个或多个事务连接共享事务和锁,而且任何一个事务连接要申请锁如同另外一个事务要申请锁一样,因此可以允许这些事务共享数据而不会有加锁的冲突。
8)考虑使用乐观锁定或使事务首先获得一个独占锁定。
八如何对行、 表、数据库加锁
1 如何锁一个表的某一行
- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
- SELECT * FROM table1 ROWLOCK WHERE A = 'a1'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM table1 ROWLOCK WHERE A = 'a1'
2 锁定数据库的一个表
select col1 from 表 (tablockx) where 1=1 ;
加锁后其它人不可操作,直到加锁用户解锁,用commit或rollback解锁
3.实例
建表
- create table table1(A varchar(50) not null, B varchar(50) ,C varchar(50));
- create table table2(D varchar(50),E varchar(50))
- insert table1 (A,B,C) values(‘a1’,’b1’,’c1’);
- insert table1 (A,B,C) values(‘a2’,’b2’,’c2’);
- insert table1 (A,B,C) values(‘a3’,’b3’,’c3’);
- insert table2 (D,E) values(‘d1’,’e1’);
- insert table2 (D,E) values(‘d2’,’e2’);
create table table1(A varchar(50) not null, B varchar(50) ,C varchar(50)); create table table2(D varchar(50),E varchar(50)) insert table1 (A,B,C) values(‘a1’,’b1’,’c1’); insert table1 (A,B,C) values(‘a2’,’b2’,’c2’); insert table1 (A,B,C) values(‘a3’,’b3’,’c3’); insert table2 (D,E) values(‘d1’,’e1’); insert table2 (D,E) values(‘d2’,’e2’);
1)排它锁
- -- A事务先更新table1表,在更新时,对其他事务进行排他
- begin tran
- update table1 set A='aa' where B='b2';
- waitfor delay '00:00:30'; --等待30秒
- commit tran
- -- A事务先更新table2表
- begin tran
- select * from table1 where B='b2';
- commit tran
-- A事务先更新table1表,在更新时,对其他事务进行排他 begin tran update table1 set A='aa' where B='b2'; waitfor delay '00:00:30'; --等待30秒 commit tran -- A事务先更新table2表 begin tran select * from table1 where B='b2'; commit tran
若同时执行上述两个事务,则select查询必须等待update执行完毕才能执行即要等待30秒
2)共享锁
- -- A事务先查询table1表,在查询时,加共享锁,防止其他事务对该表进行修改操作
- begin tran
- select * from table1 holdlock where B='b2' ;
- -holdlock人为加锁
- waitfor delay '00:00:30';--等待30秒
- commit tran
- -- A事务先查询table1表,后更改table1表
- begin tran
- select A,C from table1 where B='b2';
- update table1 set A='aa' where B='b2';
- commit tran
-- A事务先查询table1表,在查询时,加共享锁,防止其他事务对该表进行修改操作 begin tran select * from table1 holdlock where B='b2' ; -holdlock人为加锁 waitfor delay '00:00:30';--等待30秒 commit tran -- A事务先查询table1表,后更改table1表 begin tran select A,C from table1 where B='b2'; update table1 set A='aa' where B='b2'; commit tran
若并发执行上述两个事务,则B事务中的select查询可以执行,而update必须等待第一个事务释放共享锁转为排它锁后才能执行即要等待30秒
3)死锁
- -- A事务先更新table1表,然后延时30秒,再更新table2表;
- begin tran
- update table1 set A='aa' where B='b2';
- --这将在 Table1 中生成排他行锁,直到事务完成后才会释放该锁。
- waitfor delay '00:00:30';
- --进入延时
- update table2 set D='d5' where E='e1' ;
- commit tran
- -- B事务先更新table2表,然后延时10秒,再更新table1表;
- begin tran
- update table2 set D='d5' where E='e1';
- --这将在 Table2 中生成排他行锁,直到事务完成后才会释放该锁
- waitfor delay '00:00:10'
- --进入延时
- update table1 set A='aa' where B='b2' ;
- commit tran
-- A事务先更新table1表,然后延时30秒,再更新table2表; begin tran update table1 set A='aa' where B='b2'; --这将在 Table1 中生成排他行锁,直到事务完成后才会释放该锁。 waitfor delay '00:00:30'; --进入延时 update table2 set D='d5' where E='e1' ; commit tran -- B事务先更新table2表,然后延时10秒,再更新table1表; begin tran update table2 set D='d5' where E='e1'; --这将在 Table2 中生成排他行锁,直到事务完成后才会释放该锁 waitfor delay '00:00:10' --进入延时 update table1 set A='aa' where B='b2' ; commit tran
若并发执行上述两个事务,A,B两事务都要等待对方释放排他锁,这样便形成了死锁。
九、sqlserver提供的表级锁
sqlserver所指定的表级锁定提示有如下几种
1. HOLDLOCK: 在该表上保持共享锁,直到整个事务结束,而不是在语句执行完立即释放所添加的锁。
2. NOLOCK:不添加共享锁和排它锁,当这个选项生效后,可能读到未提交读的数据或“脏数据”,这个选项仅仅应用于SELECT语句。
3. PAGLOCK:指定添加页锁(否则通常可能添加表锁)
4. READCOMMITTED用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下,SQL Server 2000 在此隔离级别上操作。
5. READPAST: 跳过已经加锁的数据行,这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行,而不是阻塞直到其他事务释放锁,READPAST仅仅应用于READ COMMITTED隔离性级别下事务操作中的SELECT语句操作
6. READUNCOMMITTED:等同于NOLOCK。
7. REPEATABLEREAD:设置事务为可重复读隔离性级别。
8. ROWLOCK:使用行级锁,而不使用粒度更粗的页级锁和表级锁。
9. SERIALIZABLE:用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于 HOLDLOCK。
10. TABLOCK:指定使用表级锁,而不是使用行级或页面级的锁,SQL Server在该语句执行完后释放这个锁,而如果同时指定了HOLDLOCK,该锁一直保持到这个事务结束。
11. TABLOCKX:指定在表上使用排它锁,这个锁可以阻止其他事务读或更新这个表的数据,直到这个语句或整个事务结束。
12. UPDLOCK :指定在读表中数据时设置更新锁(update lock)而不是设置共享锁,该锁一直保持到这个语句或整个事务结束,使用UPDLOCK的作用是允许用户先读取数据(而且不阻塞其他用户读数据),并且保证在后来再更新数据时,这一段时间内这些数据没有被其他用户修改
SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表,但不能更新删除
SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删除
十、应用程序锁
应用程序锁就是客户端代码生成的锁,而不是sql server本身生成的锁处理应用程序锁的两个系统存储过程
sp_getapplock: 锁定应用程序资源
sp_releaseapplock: 为应用程序资源解锁
相关推荐
以下是对SQL Server锁机制的详细解释: 1. **丢失更新**:这是并发操作可能引发的问题之一,当两个事务同时对同一行数据进行更新时,最终可能导致其中一个事务的更新被另一个事务覆盖。SQL Server通过行级锁定避免...
SQL Server的锁机制是数据库管理系统中用于确保数据一致性、避免并发操作冲突的重要机制。在高并发的数据库环境中,正确理解和使用锁是至关重要的,因为它直接影响到系统的性能和稳定性。 首先,我们需要了解SQL ...
以下是关于SQL Server锁机制的一些关键知识点: 1. **锁的类型**:SQL Server支持多种锁类型,包括共享锁(S锁),用于读取数据;排他锁(X锁),用于写入数据;更新锁(U锁),在读取并可能更新数据时使用;意向锁...
"SQL Server 锁机制详解" SQL Server 中的锁机制是为了提供并发控制,防止多个事务同时访问同一个资源时出现的问题。锁机制可以分为悲观锁和乐观锁两种。 悲观锁是一种保守的锁机制,为任何操作(即使是 select)...
在SQL Server 2008中,锁机制是数据库管理系统(DBMS)为了确保数据的一致性和完整性,以及实现多用户并发访问时的一种关键机制。它通过控制对数据的访问来防止并发操作间的冲突,从而避免数据不一致的情况。本文将...
本文将详细探讨SQL Server中的锁机制及其工作原理。 #### 一、为什么需要锁机制? 在多用户环境下,同一时间可能会有多个用户尝试修改相同的数据,如果不加以控制,则可能导致数据不一致或丢失。例如,假设两个...
SQL Server的锁机制是数据库管理系统中用于控制并发访问和维护数据一致性的关键机制。通过深入理解锁的概念和工作原理,数据库管理员和开发人员能够更好地优化应用程序的性能,并避免潜在的并发问题,如丢失更新、脏...
以下是对SQL Server锁机制的详细解释: 1. 共享锁(Shared Locks):共享锁用于读取数据,允许多个事务同时读取同一资源,但不允许任何事务进行写操作。这种锁确保了“读读”并发,防止了脏读。 2. 更新锁(Update...
MS SQL Server 提供了多种锁机制,包括行锁、页锁、表锁等,每种锁机制都有其特点和应用场景。 在 MS SQL Server 中,锁机制可以分为两种:自动锁和手动锁。自动锁是由系统自动分配的锁,用于确保数据库的安全和...
SQL Server锁机制是数据库管理系统中用于解决多用户并发访问和操作数据库时可能出现的数据不一致问题的一种机制。锁机制的核心在于保证数据的完整性和一致性,尤其是在多用户同时操作同一个数据库时。 首先,锁机制...
资源名称:SQL server锁的机制资源截图: 资源太大,传百度网盘了,链接在附件中,有需要的同学自取。
### MSSQLServer数据库事务锁机制分析 #### 一、引言 锁机制是数据库系统中一个...通过这些资料的学习,开发者们可以更全面地掌握SQL Server锁机制的细节,并将其应用于实际项目中,以提高数据库应用的性能和可靠性。
### 深入理解SQL Server 2008的锁机制 #### 一、锁机制的重要性及背景 在任何多用户的数据库系统中,确保数据修改的一致性规则至关重要。特别是对于那些支持真正事务处理的数据库而言,当多个进程尝试同时修改相同...
在介绍SQL Server数据库事务锁机制之前,首先需要了解锁的概念。锁是网络数据库中的一个非常重要的概念,主要用于在多用户环境下保证数据库的完整性和一致性。锁通过指示某个用户(即进程会话)已经占用了某种资源,...
SQL Server 锁和事务隔离级别的比较与使用 在数据库系统中,锁和事务隔离级别是两个非常重要的概念,它们之间存在着紧密的关系。在本文中,我们将对 SQL Server 锁和事务隔离级别进行比较和使用的介绍。 首先,让...
1. 锁定:SQL Server 2000使用多种类型的锁,如共享锁(读锁)、排他锁(写锁)、更新锁等,来防止多个事务对同一资源的并发修改。锁定策略可以是行级、页级或表级,取决于事务的隔离级别。 2. 快照隔离:这种隔离...
SQL Server的锁机制是数据库管理系统中用于确保数据完整性与一致性的关键机制,尤其是在多用户环境中。它通过控制不同事务对共享资源的访问,防止并发操作带来的数据冲突,如丢失更新、脏读、不可重复读和幻觉读等...
MS SQL Server 数据库的事务锁机制是确保数据库完整性和一致性的关键组成部分,它涉及到多用户环境下的并发控制和数据安全。锁是一种软件机制,用于防止多个用户在同一时间对同一资源进行冲突操作,确保数据的一致性...
SQL Server自旋锁争用是一个高级数据库管理问题,通常出现在高性能、高并发的系统中。自旋锁是操作系统中的一个同步机制,用于控制对共享资源的访问。在数据库系统中,自旋锁用于保护数据结构在并发访问时的完整性。...