搜索
首页数据库mysql教程sqlserver锁机制
sqlserver锁机制Jun 07, 2016 pm 03:40 PM
sqlserver数据机制条件研究

对锁机制的研究要具备两个条件: 1.数据量大 2.多个用户同时并发 如果缺少这两个条件,数据库不容易产生死锁问题。研究起来可能会事倍功半。如果这两个条件都有,但你还是按数据库缺省设置来处理数据,则会带来很多的问题,比如: 1)丢失更新 A,B两个用户

对锁机制的研究要具备两个条件:
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语句

Java代码  sqlserver锁机制

  1. begin tran  
  2. update titles set title_idid=title_id  where 12  
  3. if (selectavg(price)fromtitles)>$15  
  4. begin  
  5. update titles set price=price*1.10  
  6. where price
  7. end  
  8. 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 如何锁一个表的某一行

Java代码  sqlserver锁机制

  1. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
  2. SELECT * FROM table1 ROWLOCK WHERE A = 'a1'  


2 锁定数据库的一个表
select col1 from 表 (tablockx) where 1=1 ;
加锁后其它人不可操作,直到加锁用户解锁,用commit或rollback解锁
3.实例
建表

Java代码  sqlserver锁机制

  1. create table table1(A varchar(50)  not  null, B varchar(50) ,C varchar(50));  
  2. create table table2(D varchar(50),E varchar(50))  
  3. insert table1 (A,B,C) values(‘a1’,’b1’,’c1’);  
  4. insert table1 (A,B,C) values(‘a2’,’b2’,’c2’);  
  5. insert table1 (A,B,C) values(‘a3’,’b3’,’c3’);  
  6. insert table2 (D,E) values(‘d1’,’e1’);  
  7. insert table2 (D,E) values(‘d2’,’e2’);  


1)排它锁

Java代码  sqlserver锁机制

  1. -- A事务先更新table1表,在更新时,对其他事务进行排他  
  2. begin tran  
  3. update table1 set A='aa' where B='b2';  
  4. waitfor delay '00:00:30'; --等待30秒  
  5. commit tran  
  6. -- A事务先更新table2表  
  7. begin tran  
  8. select * from table1 where B='b2';  
  9. commit tran  

若同时执行上述两个事务,则select查询必须等待update执行完毕才能执行即要等待30秒
2)共享锁

Java代码  sqlserver锁机制

  1. -- A事务先查询table1表,在查询时,加共享锁,防止其他事务对该表进行修改操作  
  2. begin tran  
  3. select * from table1 holdlock where B='b2' ;  
  4.  -holdlock人为加锁  
  5. waitfor delay '00:00:30';--等待30秒  
  6. commit tran  
  7. -- A事务先查询table1表,后更改table1表  
  8. begin tran  
  9. select A,C from table1 where B='b2';  
  10. update table1 set A='aa' where B='b2';  
  11. commit tran  

若并发执行上述两个事务,则B事务中的select查询可以执行,而update必须等待第一个事务释放共享锁转为排它锁后才能执行即要等待30秒
3)死锁

Java代码  sqlserver锁机制

  1. -- A事务先更新table1表,然后延时30秒,再更新table2表;  
  2. begin tran  
  3. update table1 set A='aa' where B='b2';  
  4. --这将在 Table1 中生成排他行锁,直到事务完成后才会释放该锁。  
  5. waitfor delay '00:00:30';  
  6. --进入延时  
  7. update table2 set D='d5' where E='e1' ;  
  8. commit tran  
  9. -- B事务先更新table2表,然后延时10秒,再更新table1表;  
  10. begin tran  
  11. update table2 set D='d5' where E='e1';  
  12. --这将在 Table2 中生成排他行锁,直到事务完成后才会释放该锁  
  13. waitfor delay '00:00:10'  
  14. --进入延时  
  15. update table1 set A='aa' where B='b2' ;  
  16. 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: 为应用程序资源解锁

声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
sqlserver数据库中已存在名为的对象怎么解决sqlserver数据库中已存在名为的对象怎么解决Apr 05, 2024 pm 09:42 PM

对于 SQL Server 数据库中已存在同名对象,需要采取以下步骤:确认对象类型(表、视图、存储过程)。如果对象为空,可使用 IF NOT EXISTS 跳过创建。如果对象有数据,使用不同名称或修改结构。使用 DROP 删除现有对象(谨慎操作,建议备份)。检查架构更改,确保没有引用删除或重命名的对象。

sqlserver服务无法启动怎么办sqlserver服务无法启动怎么办Apr 05, 2024 pm 10:00 PM

当 SQL Server 服务无法启动时,可采取以下步骤解决:检查错误日志以确定根本原因。确保服务帐户具有启动服务的权限。检查依赖项服务是否正在运行。禁用防病毒软件。修复 SQL Server 安装。如果修复不起作用,重新安装 SQL Server。

怎么查看sqlserver端口号怎么查看sqlserver端口号Apr 05, 2024 pm 09:57 PM

要查看 SQL Server 端口号:打开 SSMS,连接到服务器。在对象资源管理器中找到服务器名称,右键单击它,然后选择“属性”。在“连接”选项卡中,查看“TCP 端口”字段。

sqlserver数据库在哪里sqlserver数据库在哪里Apr 05, 2024 pm 08:21 PM

SQL Server 数据库文件通常存储在以下默认位置:Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data可通过修改数据库文件路径设置来自定义数据库文件位置。

Java连接SqlServer错误如何解决Java连接SqlServer错误如何解决May 01, 2023 am 09:22 AM

问题发现这次使用的是SqlServer数据库,之前并没有使用过,但是问题不大,我按照需求文档的步骤连接好SqlServer之后,启动SpringBoot项目,发现了一个报错,如下:刚开始我以为是SqlServer连接问题呢,于是便去查看数据库,发现数据库一切正常,我首先第一时间问了我的同事,他们是否有这样的问题,发现他们并没有,于是我便开始了我最拿手的环节,面向百度编程。开始解决具体报错信息是这样,于是我便开始了百度报错:ERRORc.a.d.p.DruidDataSource$CreateCo

sqlserver英文安装怎么更改中文sqlserver英文安装怎么更改中文Apr 05, 2024 pm 10:21 PM

SQL Server 英文安装可通过以下步骤更改为中文:下载相应语言包;停止 SQL Server 服务;安装语言包;更改实例语言;更改用户界面语言;重启应用程序。

Win11无法安装SQL Server的原因及解决方案Win11无法安装SQL Server的原因及解决方案Dec 27, 2023 pm 07:48 PM

有网友反馈,在win11上无法安装sqlserver这款软件,不知道是怎么回事,根据目前的测试来看,win11存在硬盘问题,部分接口硬盘无法安装这款软件。win11为啥不能安装sqlserver:答:win11不能安装sqlserver是硬盘的问题。1、据了解,win11存在对于硬盘的检测bug。2、这导致sqlserver无法在“三星m.2接口”硬盘上安装。3、因此,如果我们要安装的话,需要准备一块其他硬盘。4、然后将该硬盘安装到电脑里,如果没有额外插槽的话就要换掉之前的硬盘。5、安装完成后,

sqlserver数据库日志怎么查询sqlserver数据库日志怎么查询Apr 05, 2024 pm 09:06 PM

可以通过以下步骤查询 SQL Server 数据库日志:1. 打开 SQL Server Management Studio,连接到数据库服务器;2. 展开“管理”节点,导航到“SQL Server 日志”;3. 选择要查询的日志文件,右键单击并选择“查看日志文件”;4. 浏览日志记录。其他查询日志方法:使用 Transact-SQL 查询、PowerShell Cmdlet。

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热门文章

R.E.P.O.能量晶体解释及其做什么(黄色晶体)
2 周前By尊渡假赌尊渡假赌尊渡假赌
仓库:如何复兴队友
4 周前By尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险:如何获得巨型种子
3 周前By尊渡假赌尊渡假赌尊渡假赌

热工具

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

功能强大的PHP集成开发环境

适用于 Eclipse 的 SAP NetWeaver 服务器适配器

适用于 Eclipse 的 SAP NetWeaver 服务器适配器

将Eclipse与SAP NetWeaver应用服务器集成。

EditPlus 中文破解版

EditPlus 中文破解版

体积小,语法高亮,不支持代码提示功能

DVWA

DVWA

Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序,非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具,帮助Web开发人员更好地理解保护Web应用程序的过程,并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞,难度各不相同。请注意,该软件中

Atom编辑器mac版下载

Atom编辑器mac版下载

最流行的的开源编辑器