社区
基础和管理
帖子详情
Record is locked by another user?
whchh
2002-08-03 10:40:48
我在更新一个表时有这样的提示,是不是表被锁了?怎么样解决?~
...全文
336
8
打赏
收藏
Record is locked by another user?
我在更新一个表时有这样的提示,是不是表被锁了?怎么样解决?~
复制链接
扫一扫
分享
转发到动态
举报
写回复
配置赞助广告
用AI写文章
8 条
回复
切换为时间正序
请发表友善的回复…
发表回复
打赏红包
jaguarcts
2002-08-04
打赏
举报
回复
v$lock
developer2002
2002-08-03
打赏
举报
回复
从v$locked_objects视图和v$process视图查一下spid,然后kill
jiezhi
2002-08-03
打赏
举报
回复
很可能是有DELETE/UPDATE之类的执行完了之后没有提交,造成其他
操作不能进行。
whchh
2002-08-03
打赏
举报
回复
我想Drop这个表都不行,提示“ORA-00054:资源正忙,要求指定NOWAIT”,怎么回事?~
whchh
2002-08-03
打赏
举报
回复
能KILL的我都KILL了,怎么不行呢?是不是这个表被锁了?~
whchh
2002-08-03
打赏
举报
回复
PL/SQL Developer:
用的就是正常的用户……
zhoubf725
2002-08-03
打赏
举报
回复
查锁
kill session
riyeye
2002-08-03
打赏
举报
回复
你是什么用户登录的呢?
oracle数据库解决死锁
oracle数据库解决死锁,使用plsql语句手动解决死锁问题
Radmin自动登录器v3.0-多国语言绿色版-Release1-20150615
Radmin自动登陆器 v3.0 - By: ybmj@vip.163.com 20150615 By: ybmj@vip.163.com , http://dep.yibinu.cn/wgzxnew/ 1、程序功能和使用环境介绍 2、程序操作方法介绍 3、登录信息文件RadminM.txt介绍 4、登录信息文件RadminM.txt的转换和编制 5、v3.0版新增解锁远程桌面功能 6、相关配置和多种语言支持介绍 7、免责申明 1、程序功能和使用环境介绍 (1)、程序功能 为了安全高效地使用Radmin Viewer来自动登录和管理多台服务器,故编制RadminM (Radmin Connection Manager,Radmin自动登录器)。 v3.0版的可执行文件是RadminM.exe,一台电脑只能运行一个实例,再次运行只是将已运行的实例调到前台。v3.0版之前的老版本的可执行文件是RadminM2.exe。 新版的功能已经比较完善,基本上可以代替Radmin Viewer 3.5进行管理(除Intel AMT功能外),另外还增加了一些实用功能,支持Windows Xp、Vista、Win7、Win8、2003、2000、9x及相应Windows Server版等操作系统。 (2)、程序使用环境要求 使用前请将Radmin Viewer 3.5的Radmin.exe文件直接拷贝到该目录中,其它Radmin Viewer 3.x版本也可以,中文版、英文版均可; 请设置防火墙允许Radmin.exe和RadminM.exe(仅扫描功能用)访问网络; 若要用到聊天、语音聊天、传送信息等连接模式,必须将相应的8个dll文件也拷贝到该目录中:ChatLPCx.dll、raudiox.dll、rchatx.dll、unicows.dll、vcintcx.dll、vcintsx.dll、voicex.dll、WinLpcDl.dll。 (3)、Radmin Server使用权限设置(新版本可选) 注意:在v1.5及以前的老版本中,Radmin Server被控端必须将“使用权限...”(Permissions)设置为“Windows NT 安全性”(Security),如果设置为“Radmin安全性”(Security)将不能实现自动登录功能。在新版本中,这两种安全性模式下,都可以实现自动登录功能。 (4)、开发环境 v1.5及以前的老版本用AutoIt语言开发,AutoIt是解释性语言,功能和稳定性有限,并且一些防病毒软件会报警。 为了在功能和稳定性方面进一步提高和改进,v2.0版使用VC++ Unicode(MFC)编程,程序在编译时已经集成了VC运行库,可独立运行。 由于MFC越益臃肿笨重,为了提高稳定性和效率,v3.0版使用WTL VC++ Unicode编程,程序短小精悍、可独立运行。WTL是Windows Template Library,可参见 http://wtl.sourceforge.net/ 。 2、程序操作方法介绍 (1)、程序中的鼠标操作 * 双击某条记录以默认模式自动连接(等待6秒);若该记录包含私有代理将自动进行代理连接(代理登录和目标登录各等待6秒); * 左上角的选择框或主菜单都可以选择默认连接模式; * 先右击某条记录(或F9)填为强制代理(支持域名),并选中强制代理选项,便可对另一条记录强制进行代理连接(将忽略私有代理); * 支持鼠标滚轮; * 主菜单和右键菜单均可完成本程序的常规操作;记录窗格的右键菜单或单击工具栏的相应按钮可直接选择进行指定模式的连接(将忽略默认连接模式); * 主菜单中的“配置”菜单可以选择程序的各项相关配置; * 工具栏各个按钮的功能均有提示; * 单击工具栏上的“显示隐藏树状目录”按钮可以显示隐藏目录树窗格,目录树窗格的右键菜单可完成目录树的一些常规操作; * 单击工具栏上的“选择切换图标查看模式”按钮可以切换或选择记录窗格的图标查看模式; * 记录窗格和目录树窗格都支持鼠标拖放功能,强烈建议用户使用该功能前备份RadminM.txt,以免损坏或丢失数据;直接鼠标拖放为移动,Ctrl+鼠标拖放为复制。拖放时状态栏有提示信息; * 程序启动时,记录自动按记录名称升序排列;在记录窗格单击列表框某列表头,可以按该列进行记录排序,再次单击可以反向排序。 (2)、程序中的常用快捷键 * Enter :以默认模式连接记录; * Insert :新建记录; * Ctrl+e :编辑记录; * Ctrl+c
Radmin自动登录器v3.0
Radmin自动登陆器 v3.0 - By: ybmj@vip.163.com 20180106 By: ybmj@vip.163.com , http://dep.yibinu.cn/wgzxnew/ 1、程序功能和使用环境介绍 2、程序操作方法介绍 3、登录信息文件RadminM.txt介绍 4、登录信息文件RadminM.txt的转换和编制 5、v3.0版新增解锁 远程桌面功能 6、相关配置和多种语言支持介绍 7、免责申明 1、程序功能和使用环境介绍 (1)、程序功能 为了安全高效地使用Radmin Viewer来自动登录和管理多台服务器,故编制RadminM (Radmin Connection Manager,Radmin自动登录器)。 v3.0版的可执行文件是RadminM.exe,一台电脑只能运行一个实例,再次运行只是将已运行的实例调到前台。v3.0版之前的老版本的可执行文件是RadminM2.exe。 新版的功能已经比较完善,基本上可以代替Radmin Viewer 3.5进行管理(除Intel AMT功能外),另外还增加了一些实用功能,支持Windows Xp、Vista、Win7、Win8、2003、2000、9x及相应Windows Server版等操作系统。 (2)、程序使用环境要求 使用前请将Radmin Viewer 3.5的Radmin.exe文件直接拷贝到该目录中,其它Radmin Viewer 3.x版本也可以,中文版、英文版均可; 请设置防火墙允许Radmin.exe和RadminM.exe(仅扫描功能用)访问网络; 若要用到聊天、语音聊天、传送信息等连接模式,必须将相应的8个dll文件也拷贝到该目录中:ChatLPCx.dll、raudiox.dll、rchatx.dll、unicows.dll、vcintcx.dll、vcintsx.dll、voicex.dll、WinLpcDl.dll。 (3)、Radmin Server使用权限设置(新版本可选) 注意:在v1.5及以前的老版本中,Radmin Server被控端必须将“使用权限...”(Permissions)设置为“Windows NT 安全性”(Security),如果设置为“Radmin安全性”(Security)将不能实现自动登录功能。在新版本中,这两种安全性模式下,都可以实现自动登录功能。 (4)、开发环境 v1.5及以前的老版本用AutoIt语言开发,AutoIt是解释性语言,功能和稳定性有限,并且一些防病毒软件会报警。 为了在功能和稳定性方面进一步提高和改进,v2.0版使用VC++ Unicode(MFC)编程,程序在编译时已经集成了VC运行库,可独立运行。 由于MFC越益臃肿笨重,为了提高稳定性和效率,v3.0版使用WTL VC++ Unicode编程,程序短小精悍、可独立运行。WTL是Windows Template Library,可参见 http://wtl.sourceforge.net/ 。 2、程序操作方法介绍 (1)、程序中的鼠标操作 * 双击某条记录以默认模式自动连接(等待6秒);若该记录包含私有代理将自动进行代理连接(代理登录和目标登录各等待6秒); * 左上角的选择框或主菜单都可以选择默认连接模式; * 先右击某条记录(或F9)填为强制代理(支持域名),并选中强制代理选项,便可对另一条记录强制进行代理连接(将忽略私有代理); * 支持鼠标滚轮; * 主菜单和右键菜单均可完成本程序的常规操作;记录窗格的右键菜单或单击工具栏的相应按钮可直接选择进行指定模式的连接(将忽略默认连接模式); * 主菜单中的“配置”菜单可以选择程序的各项相关配置; * 工具栏各个按钮的功能均有提示; * 单击工具栏上的“显示隐藏树状目录”按钮可以显示隐藏目录树窗格,目录树窗格的右键菜单可完成目录树的一些常规操作; * 单击工具栏上的“选择切换图标查看模式”按钮可以切换或选择记录窗格的图标查看模式; * 记录窗格和目录树窗格都支持鼠标拖放功能,强烈建议用户使用该功能前备份RadminM.txt,以免损坏或丢失数据;直接鼠标拖放为移动,Ctrl+鼠标拖放为复制。拖放时状态栏有提示信息; * 程序启动时,记录自动按记录名称升序排列;在记录窗格单击列表框某列表头,可以按该列进行记录排序,再次单击可以反向排序。 (2)、程序中的常用快捷键 * Enter :以默认模式连接记录; * Insert :新建记录; * Ctrl+e :编辑记录; * Ctrl+c :复制记录; * Ctrl+x :剪切记录; * Ctrl+v :粘贴记录; * Delete :删除记录或目录(在记录窗格),或删除树状目录(在目录树窗格); * F1 :显示程序信息; * F2 :更名树状目录; * F3 :单条扫描(等待5秒,用于扫描网速较慢的记录); * F5 :全部扫描(多线程同时扫描,每条记录等待5秒); 扫描过程中左下角状态栏会有提示,扫描完成后提示消失,扫描过程中建议不要新建、修改、删除、粘贴、剪切、排序记录,不然可能出现扫描结果错乱,其它功能可正常使用; * F7 :新建树状目录; * F9 :将选中记录填为强制代理(主菜单上“强制代理信息”项显示将从[无]变为[有],打开该菜单可查看信息); * Ctrl+- :隐藏窗口到系统托盘; * Ctrl+= :显示窗口; * 双击系统托盘图标可隐藏或显示窗口; * 窗口大小可调整,支持最大化和还原; * 支持Home、End、PageUp、Pag
eD
own等操作。 3、登录信息文件RadminM.txt介绍 (1)、RadminM.txt内容说明 登录信息存放在RadminM.txt文件中,若没有会自动创建,密码用RC4加密,请用户注意保管。RadminM.txt是遵循CSV格式的ANSI文本文件,所有字段内容都不能包含英文惊叹号“!”、英文逗号“,”、竖线分隔符“|”。 第一行为登录记录各字段的名称。每行存放一条记录,每条记录包含用17个英文逗号分隔的18个字段。
Record
Name 记录名称是关键字段,支持中文记录名称,不能为空、不要有重名; IP、Port、
User
、Password 分别是IP地址、端口、用户名、密码。IP地址不能为空,若端口为空程序将使用缺省端口4899; Domain 是域名,该字段有内容在登录时便会自动填写; ColorDepth 是在“完全控制”或“仅限查看”连接模式,指定传输图像的色彩深度。色彩深度大小与传输速度成反比; Updates 是在“完全控制”或“仅限查看”连接模式,指定屏幕每秒最大刷新率,为1到100之间的数值; Un
lock
Desktop 是在“完全控制”连接模式连接成功后,若远程桌面已登录锁定、且焦点位于密码输入框,可用连接Radmin的密码解锁远程桌面、或 (当服务器端为Radmin Server v3.5时) 先锁定再解锁远程桌面。具体配置参见后面的介绍; Fullscreen 是在“完全控制”或“仅限查看”连接模式,以全屏幕方式、或全屏伸展方式显示远程PC窗口; Nofullkbcontrol 是在“完全控制”连接模式,阻止系统热键(如ALT-TAB)传递到远程PC; Monitor 是在“完全控制”或“仅限查看”连接模式,若远程PC有多个监视器,可指定显示其中某个监视器上的图像。比如:/monitor"\\.\DISPLAY1"。注意:只能指定在已连接窗口的菜单中显示出来的监视器; Sendrequest 是请求Radmin服务器发送Radmin服务器激活文件。将忽略其它选项。详情请参见Radmin帮助文档; Pbpath 是以指定的电话薄文件启动Radmin Viewer。比如:/pbpath"C:\my.rpb"。将忽略其它选项; Proxy 是记录的私有代理信息。私有代理格式:记录名称+目录路径。需要先将某条已有记录设置为强制代理,再选作私有代理。 AsProxyBy 是被用作私有代理字段。是指该记录被其它哪些记录用作私有代理,由程序自动处理(只读); Memory 是备注字段; TreePath 是目录路径字段,由若干英文惊叹号“!”(目录分隔符)分隔的字符串构成,支持中文目录名,如根目录下DirA子目录下的DirB子目录:!DirA!DirB 。 (2)、私有代理字段Proxy 本程序除了支持强制代理外,每条记录都可以指定私有代理。Proxy字段便是存放用作私有代理的记录信息,只能有一条;注意:只能从已有记录中指定私有代理;Proxy字段的格式:记录名称+目录路径;建议先将某条已有记录设置为强制代理,再到新建记录或编辑记录对话框中填写为私有代理;当然,若熟悉后也可以手工填写。 (3)、被用作私有代理字段AsProxyBy AsProxyBy是被用作私有代理字段,用于存放该记录被其它哪些记录用作私有代理的信息,多条记录间用竖线分隔符“|”分隔,由程序自动处理(只读);该字段主要用于当该记录名称或目录路径更改时,程序会自动更新将该记录用作私有代理的其它记录的私有代理信息;建议用户不要随意修改RadminM.txt文件中该字段的内容,不然可能会出现程序功能错乱。 (4)、格式符合要求的RadminM.txt文件示范
Record
Name,IP,Port,
User
,Password,Domain,ColorDepth,Updates,Un
lock
Desktop,Fullscreen,Nofullkbcontrol,Monitor,Sendrequest,Pbpath,Proxy,AsProxyBy,Memory,TreePath sample01,192.168.0.6,4899,
user
01,,,,,,,,,,,,,,! sample02,192.168.0.8,4899,
user
02,,,,,,,,,,,,,,!DirA!DirB sample03,192.168.0.9,4899,
user
03,,,,,,,,,,,,,,!DirC!DirD 4、登录信息文件RadminM.txt的转换和编制 (1)、V2.0转V3.0记录文件 单击主菜单、帮助中的“V2.0转V3.0记录文件”菜单项,可以将RadminM V2.0的记录文件转换为RadminM V3.0的记录文件。执行转换之前,请先备份好RadminM.txt。新生成的文件可能覆盖RadminM.txt。 (2)、v1.5的RadminM.txt文件需先转换为v2.0的格式,再导入新版本v3.0中使用 v1.5的RadminM.txt简单修改一下就可以转换为v2.0的格式。修改的具体方法是: (A)用Ultra
Ed
it编辑器打开v1.5的RadminM.txt(用其它编辑器也可参照完成类似修改); (B)Ctrl+R调出替换对话框,在上面需要替换栏输入:^p ,在下面替换为栏输入:,!^p ,(这里,^p代表回车换行),设置好后再单击“全部替换”按钮即可,需要时可单击“帮助”按钮查看帮助信息; (C)将第一行末尾的 “!” 手工改为 “TreePath”; (D)处理完后保存为RadminM.txt。 用其它编辑器也可参照完成类似修改。转换完成后,再用上面介绍的“V2.0转V3.0记录文件”菜单项导入v3.0中使用。 (3)、用记事本、Ultra
Ed
it、Excel等编制RadminM.txt RadminM.txt可以用记事本、Ultra
Ed
it、Excel等编制。也可将已有RadminM.txt导入Excel处理,具体方法是: (A)启动Excel,选择菜单“数据->导入外部数据->导入数据”,选择RadminM.txt文件; (B)文本导入向导第1步,直接单击“下一步”; (C)第2步必须选中“逗号”分隔符,再单击“下一步”; (D)第3步必须将所有18列都设置为文本,依次选中下面数据预览里的各列,再选择右上面列数据格式里的“文本”。全部设置好后,再单击“完成”、“确定”即可成功导入; (E)处理完后须保存为CSV格式文件,再更名为RadminM.txt便可使用。 5、v3.0版新增解锁远程桌面功能 (1)、解锁远程桌面功能简介 当以“完全控制”连接远程PC成功后,若远程桌面已登录锁定、且焦点位于密码输入框,可用连接Radmin的密码解锁远程桌面、或 (当服务器端为Radmin Server v3.5时) 先锁定再解锁远程桌面。 要正常使用这一功能,必须满足以下条件:远程PC已经登录、锁定远程桌面的用户密码与连接Radmin的密码一致、远程桌面的焦点位于密码输入框。 (2)、可能存在的安全隐患 注意:当服务器端为Radmin Server v3.5之前的老版本、解锁前远程桌面并未锁定而焦点又正好位于文本编辑框中,启用该功能可能会出现明文密码。 (3)、相关配置 用户可以为每条记录单独配置解锁远程桌面功能,相关配置信息保存在每条记录的Un
lock
Desktop字段中。慎重起见,默认并未启用该功能。用户可以根据实际情况,单独为每条记录选择不使用(该字段为空白)、或者“Un
lock
Desktop”、或者“
Lock
ThenUn
lock
”。 该字段为空白,也就是不使用该功能,便不会出现明文密码。 “Un
lock
Desktop”是指直接解锁远程桌面,适用于Radmin Server各版本,但可能出现明文密码。 “
Lock
ThenUn
lock
”是指若解锁前远程桌面处于未锁定状态、可以先锁定远程桌面再解锁,这样可以避免出现明文密码。但这要求必须使用Radmin Viewer 3.5的Radmin.exe文件,并且只对连接Radmin Server v3.5版本才有效。Radmin Viewer 3.5之前的老版本无法发送锁屏组合键Win+L,Radmin Server v3.5之前的老版本无法接收锁屏组合键Win+L,仍然存在出现明文密码的可能性。 6、相关配置和多种语言支持介绍 (1)、配置文件RadminM.ini 主菜单中的“配置”菜单可以选择程序的各项相关配置。程序的各项配置都保存在RadminM.ini配置文件中,若不存在程序会自动创建。若由于配置混乱、异常关闭等原因导致程序运行后无法显示主窗口,可以先备份然后删除RadminM.ini文件即可正常运行。 (2)、多种语言支持 本程序使用INI文件实现多种语言支持,每种语言信息用一个扩展名为lng的INI格式文件存放。语言文件可以使用Unicode或ANSI格式,一般建议使用Unicode格式。这种方式具有更多扩展性,用户可以非常简单方便地添加自己的语言文件。 本程序的默认语言是简体中文,另外提供英文语言文件English.lng。本程序启动时若没有外部语言文件,将使用内置的默认语言(简体中文)。若本程序目录下有*.lng的外部语言,程序启动后便会自动在“关于->语言”菜单下列出外部语言(以语言文件的文件名命名)。用户选择某种外部语言便可以动态切换到新语言界面,无需重新启动程序,用户的语言选择将自动保存到RadminM.ini文件中,关闭程序后下次启动也会自动使用用户选择的新语言界面。 用户可以参照English.lng语言文件的格式和内容,方便地编制修改自己的语言文件,比如French.lng。用户只需将自己编制好的语言文件拷贝到本程序目录下,重新启动程序后便会自动在“关于->语言”菜单下列出用户添加的新语言French。选择该语言便可以动态切换到新语言界面,无需重新启动程序,关闭程序后下次启动也会自动使用用户选择的新语言界面。 注意:语言文件中间不能有空行,空行就意味文件结束,空行之后就无法查找翻译。若需要空行标识分隔,可以在空行前加英文分号 ;,也即注释行。 语言文件中的字符串,若需要前导和后导空格,可以将字符串用英文双引号或英文单引号包含即可。不需要空格的就无需加引号。 本程序的多种语言支持功能参照网友Yonsm提供的方式实现,有兴趣的用户可以访问网站 http://yonsm.net/ini-language-engine/。 (3)、启用Radmin帮助 在本程序中,若要启用菜单项“帮助->Radmin帮助”,需要将Radmin的chm帮助拷贝为本程序目录中的Radmin35.chm。 7、免责申明 用户可自行斟酌选用该程序,若转载请注明出处。对一切后果,作者不承担任何责任! ======================================================================================== RadminM v3.0 - By: ybmj@vip.163.com 20150615 By: ybmj@vip.163.com , http://dep.yibinu.cn/wgzxnew/ 1. The features and the running environmental of RadminM 2. The operation of RadminM 3. RadminM.txt of login information file 4. Translate and Preparation RadminM.txt 5. Un
lock
Desktop feature of v3.0 newly additional 6. Relat
ed
settings and Multilanguage support 7. Disclaimer 1. The features and the running environmental of RadminM (1). The features of RadminM In order to safely and efficiently use Radmin Viewer to automatically login and manage multiple servers, so the program RadminM(Radmin Connection Manager) was intend
ed
to develop. The executable file is RadminM.exe of RadminM v3.0. Only one instance can run in a computer, and it only bring the running instance to the foreground if RadminM run again. And the executable file of RadminM before v3.0 is RadminM2.exe. The new version have been more improvement of the function, and
user
basically can manage instead of Radmin Viewer 3.5 (except Intel AMT technology). And there ars some useful features else. It support for Windows Xp, Vista, Win7, Win8, 2003, 2000, 9x and the corresponding version of Windows Server operating systems. (2). The environmental requirements of RadminM running Before use, please copy the Radmin.exe of Radmin Viewer 3.5 to this directory, Other Radmin Viewer 3.x versions of Chinese or English are also available. Please set the firewall to allow Radmin.exe and RadminM.exe (only scan function us
ed
) to access the network. To use Text Chat, Voice Chat, Send Message such as connection mode,
user
must copy also corresponding 8 dll files to this directory: ChatLPCx.dll, raudiox.dll, rchatx.dll, unicows.dll, vcintcx.dll, vcintsx.dll, voicex.dll, WinLpcDl.dll. (3). To set Radmin Server's Permissions(Option in the new version) Note: In v1.5 and previous versions, Radmin Server's "Permissions ..." must be set to "Windows NT security", RadminM can not be automatically login feature if it was set to "Radmin security". The new version can be automatically login in these two security modes. (4). The development environments of RadminM RadminM v1.5 and the previous version is develop
ed
by AutoIt language. As AutoIt is an interpret
ed
language, it is limit
ed
in the functionality and stability, and some anti-virus software will alarm. In order to further improve the functionality and stability, the new version using VC++ UNICODE(MFC) programming environment. The program had already integrat
ed
VC runtime library when it was compil
ed
, it can run independently. Because the MFC is more and more bloat
ed
and heavy, in order to further improve the stability and efficiency, v3.0 using WTL VC++ UNICODE programming environment. The program dapper, and can run independently. WTL is the abbreviation of Windows Template Library,
user
may refer to http://wtl.sourceforge.net/ . 2. The operation of RadminM (1). The mouse operation of RadminM * Double-click a
record
, RadminM will automatically connect in the default mode(wait 6 second), or RadminM will automatically Proxy connect if the
record
has Proxy(each of Proxy login and Target login wait respectively 6 second). * You may select the default connection mode at the ComboBox of the top left corner or the main menu. * Right-click a
record
(or F9) to fill as Forc
ed
Proxy (supports domain name) at first, and check the CheckBox of Forc
ed
Proxy or the menu item, you can connect another
record
via Forc
ed
Proxy (the Proxy will be ignor
ed
). * Mouse wheel support. * You can complete the normal operation with the main menu and right-click menu. By the right-click menu of
record
s pane (Right Pane) or click the corresponding button on the toolbar, you can directly connect in specify mode (the default connection mode will be ignor
ed
). * There is the function prompt
ed
for each button on the toolbar. * Clicking "Show or Hide Tree" button on the toolbar will show or hide the directory tree pane. You may complete some normal trees operations by right-click menu in trees pane (Left Pane). *
Record
s pane and trees pane support the mouse drag-and-drop function. To avoid damage or loss of data, we strongly recommend
ed
to backup RadminM.txt before useing this function. Directly drag-and-drop to move, Ctrl + drag-and-drop to copy. There is the prompt
ed
information at the status bar as drag-and-drop. *
Record
s automatically sort in ascending order by
record
name when the program starts. Clicking the table head of listctrl in
record
s pane will sort
record
s according to this column, and clicking again will sort reverse. (2). The common Shortcuts of RadminM: * Enter : automatically connection in the default mode. * Insert : new
record
. * Ctrl+e :
ed
it
record
. * Ctrl+c : copy
record
. * Ctrl+x : cut
record
. * Ctrl+v : paste
record
. * Delete : delete
record
and subdir (in
record
s pane). delete subdir (in trees pane). * F1 : help information. * F2 : rename subdir (in trees pane) or
ed
it
record
(in
record
s pane). * F3 : scan one
record
(wait 5 seconds, us
ed
for slowly network). * F5 : scan all
record
s by multithreading (wait 5 second for each
record
). There is prompt at the status bar of bottom left corner when the scan is processing, and prompt will disappear after the scan has finish
ed
. We are not recommend
ed
to create, modify, delete, paste, cut, sort
record
s when the scan is processing, otherwise the scan results may appear confusion. But other functions may be us
ed
normally. * F7 : new subdir. * F9 : fill the select
ed
record
as Forc
ed
Proxy. (The "Forc
ed
-Proxy" item of the main menu will show from [No] to [Yes]. To click the menu, you can view the information.) * Ctrl+- : hide RadminM window to the system tray. * Ctrl+= : show RadminM window. * Double-click the system tray icon to hide or show RadminM window. * RadminM window is resizable, maximize and restore support. * RadminM supports Home, End, PageUp, Pag
eD
own, etc. 3. RadminM.txt of login information file (1). RadminM.txt description Login information is stor
ed
in RadminM.txt file. RadminM will automatically create if RadminM.txt is not exist. PassWord is encrypt
ed
by RC4,
user
keep attention to store. RadminM.txt is text file to follow CSV (ANSI) formatt
ed
. The contents of all field can not contain English exclamation mark "!", English comma ",", vertical separator "|". The first line is the the names of login
record
fields. There is only a
record
each line, what contains 18 fields delimit
ed
by 17 English comma.
Record
Name (
Record
Name) is the key field, support Chinese
record
name, but can not be empty, do not have the same
record
name. IP, Port,
User
, Password are the IP address, port,
user
name, password. IP address can not be empty.The program will use the default port 4899 if the Port is empty. Domain is the domain name, it will be us
ed
to automatically fill in the login if Domain has content. ColorDepth is us
ed
with the "Full Control" connection mode (no mode switches) or "View Only" connection mode (the "/noinput" switch). Defines the color depth of images that Radmin Server will transfer to Radmin Viewer. ColorDepth is in inverse proportion to transmission rate. Updates is us
ed
with "Full Control" connection mode or "View Only" connection mode. Will display the remote computer window with no more than the specifi
ed
number of updates per second. That is the maximum updates per second. Please input number between 1 to 100. Un
lock
Desktop is us
ed
with "Full Control" connection mode . After "Full Control" connection has success
ed
, it can un
lock
the remote desktop with the connection password if the remote desktop has
lock
ed
and the focus is in the password input box, or
lock
first the remote desktop then un
lock
when remote PC run Radmin Server v3.5. Fullscreen is us
ed
with the "Full Control" connection mode or "View Only" connection mode. Will display the remote computer window in full screen, or expanding the image if the screens resolution of the remote and local computer differs. Nofullkbcontrol is us
ed
with "Full Control" mode. The key prevents the transfer of system hotkeys (such as ALT-TAB) to the remote computer. Monitor is us
ed
with "Full Control" or "View Only" connection mode. If there are several monitors on the remote computer, this key makes it possible for you to display an image on one of them. Example: /monitor"\\.\DISPLAY1" . Note, you can only specify a monitor from which show on the connect
ed
window's menu. Sendrequest is to send specifi
ed
Radmin Server activation request file to the Famatech Activation Server and saves receiv
ed
license file. Will ignore other options. Please refer to Radmin help for details. Pbpath is to start Radmin Viewer with specifi
ed
phonebook file. Example: /pbpath"C:\my.rpb". Will ignore other options. Proxy is us
ed
to store private-proxy information of the
record
. Proxy Format:
Record
Name+TreePath.
User
ne
ed
to select a
record
and set to forc
ed
-proxy at first, then fill as Proxy in the New
Record
or
Ed
it
Record
dialog. AsProxyBy is being us
ed
as Proxy field. It is automatically process
ed
by the program (Read Only). Memory is memo field. TreePath is the directory path field. TreePath is a string that consist of multiple strings division by English exclamation mark '!' (directory separator). It support Chinese directory name. Example: !DirA!DirB, the DirB under the DirA under the root. (2). The Proxy field This program not noly supports forc
ed
-proxy, each
record
but also can be specifi
ed
private-proxy (abbreviat
ed
as Proxy). The Proxy field is us
ed
to store private-proxy information of the
record
, it can be only one. Note,
user
can only specify Proxy from existing
record
s. Proxy Format:
Record
Name+TreePath. We recommend to select a
record
and set to forc
ed
-proxy at first, then fill as Proxy in the New
Record
or
Ed
it
Record
dialog. Of course, you can also fill it by hand after familiar. (3). The AsProxyBy field The AsProxyBy field is being us
ed
as Proxy field, it is us
ed
to store the information which other
record
s use this
record
as Proxy. Multiple
record
s are delimit
ed
with vertical separator "|". It is automatically process
ed
by the program (Read Only). This field is mainly us
ed
to automatically update the Proxy information of other
record
s which use this
record
as Proxy when this
record
's
Record
Name or TreePath is chang
ed
. We recommend that
user
do not arbitrarily modify the contents of this field in RadminM.txt, otherwise the program may appear functional disorder. (4). Example: a fitting format RadminM.txt
Record
Name,IP,Port,
User
,Password,Domain,ColorDepth,Updates,Un
lock
Desktop,Fullscreen,Nofullkbcontrol,Monitor,Sendrequest,Pbpath,Proxy,AsProxyBy,Memory,TreePath sample01,192.168.0.6,4899,
user
01,,,,,,,,,,,,,,! sample02,192.168.0.8,4899,
user
02,,,,,,,,,,,,,,!DirA!DirB sample03,192.168.0.9,4899,
user
03,,,,,,,,,,,,,,!DirC!DirD 4. Translate and Preparation RadminM.txt (1). V2.0 to V3.0 RadminM.txt
User
can translate RadminM V2.0
record
file to V3.0 by "V2.0 to V3.0 RadminM.txt" menu of Help in the main menu. Please backup RadminM.txt before performing the transfer. New file may overwrite RadminM.txt. (2). The v1.5 RadminM.txt ne
ed
to translate to v2.0 fromat, then it can be translat
ed
to v3.0 The v1.5 RadminM.txt can be us
ed
in the new version after simply modification. The modification proc
ed
ure is: (A) To open the v1.5 RadminM.txt by Ultra
Ed
it
ed
itor. (B) Ctrl+R to bring up the Replace dialog box, input in the above Find What pane: ^p, input in the below Replace With pane: ,!^p . (^p is CRLF here.). After properly setting, then click "Replace All" button. You may click the "Help" button to get the help information if you ne
ed
. (C) Replace "!" with "TreePath" by hand at end of the first line. (D) Save the file to RadminM.txt after process
ed
. You can also refer to complete a similar modification with other
ed
itors. After this,
user
can Translate it to v3.0 by fore-mention
ed
"V2.0 to V3.0 RadminM.txt" menu. Then it can be us
ed
in the new version v3.0. (3). To prepare RadminM.txt by Notepad, Ultra
Ed
it, Excel, etc RadminM.txt can be prepar
ed
using Notepad, Ultra
Ed
it, Excel, etc. You can also import RadminM.txt to Excel to process. The proc
ed
ure is: (A) Start Excel, then click the menu "Data|Import External Data|Import Data", select RadminM.txt file. (B) Text Import Wizard - Step 1 of 3, direct click "Next". (C) Text Import Wizard - Step 2 of 3, you must check the "Comma" delimiter and then click "Next". (D) Text Import Wizard - Step 3 of 3, you must set all 18 columns to text format. You should select the data columns in turn below Data preview, and then check the "Text" above Column data format. After properly setting, to click "Finish" and "OK" to complete successfully import. (E) The file must save as CSV format after process
ed
. The file can be us
ed
for RadminM after direct renam
ed
to RadminM.txt. 5. Un
lock
Desktop feature of v3.0 newly additional (1). The Un
lock
Desktop feature After "Full Control" connection has success
ed
, it can un
lock
the remote desktop with the connection password if the remote desktop has
lock
ed
and the focus is in the password input box, or
lock
first the remote desktop then un
lock
when remote PC run Radmin Server v3.5. To successfully use this feature, there must be conditions: remote PC has already login
ed
, and the password for
lock
ed
remote desktop as same as the password for connec
ed
Radmin Server, and the focus of remote desktop is in the password input box. (2). Possible security risk Note, there may be plaintext password, if the Radmin Server is old version before v3.5, and the remote desktop has not
lock
ed
before to un
lock
, and the focus of remote desktop is exactly on a text
ed
it box. (3). Un
lock
Desktop setting
User
can singly prepare Un
lock
Desktop setting for every
record
. Un
lock
Desktop setting is sav
ed
in Un
lock
Desktop fil
ed
of every
record
in RadminM.txt. To be deliberate, this feature is not enabl
ed
by default.
User
can choose and use this feature according to the actual situation.
User
can choose disabl
ed
this feature(Blank fil
ed
), or "Un
lock
Desktop", or "
Lock
ThenUn
lock
" for every
record
. When this fil
ed
is blank, that is disabl
ed
this feature, and there wiil not be plaintext password. When this fil
ed
is “Un
lock
Desktop”, that is directly un
lock
remote desktop. That suit each versions of Radmin Server. But there may be plaintext password. When this fil
ed
is “
Lock
ThenUn
lock
”, that is
lock
ed
first the remote desktop then un
lock
ed
if the remote desktop has not
lock
ed
before to un
lock
. This will avoid to appear plaintext password. But it must use the Radmin.exe of Radmin Viewer 3.5, and it only suit to connect Radmin Server v3.5. It can not send the Win+L
Lock
Screen combination key to the remote computer by old version before Radmin Viewer 3.5. It can not receive the Win+L
Lock
Screen combination key by old version before Radmin Server 3.5. The possibility to appear plaintext password still exists. 6. Relat
ed
settings and Multilanguage support (1). Settings file RadminM.ini
User
may select the RadminM's relat
ed
settings by Settings Menu of the main menu. All RadminM's relat
ed
settings is stor
ed
in file RadminM.ini. RadminM will automatically create if RadminM.ini is not exist. If the RadminM main window can not display, it maybe the settings is confusion, or abnormal shutdown.
User
may backup the file RadminM.ini and then delete it. After this,
user
can run RadminM normal. (2). Multilanguage support This program achieve multilingual support by INI file. Each language information is stor
ed
in an INI format file with .lng extension. Language files is text file to follow Unicode or ANSI formatt
ed
. We generally recommend
ed
to use Unicode format. This mode has more scalability, the
user
can very simply and easily add your own language file. The default language of this program is simplifi
ed
Chinese, there is other English language file English.lng as example. If there is no external language file, RadminM use the built-in default language(Chinese) when the program starts. If there are any *.lng external languages under this program directory, RadminM will automatically load and list in the "Settings|Language" menu after the program starts. In this menu, the name of external language is us
ed
the filename of the language file. By choos
ed
a language in the menu,
user
s can dynamically switch to the new language interface without having to restart the program. The
user
's language selection will be automatically sav
ed
to RadminM.ini file. After closeing the program, RadminM will automatically use the new language interface select
ed
by the
user
when the next start.
User
s can refer to the format and content of English.lng, and easily prepar
ed
to modify their own language files, such French.lng.
User
s only ne
ed
to copy the prepar
ed
language file to this program directory. After restarting the program, RadminM will automatically load and list the new language add
ed
by the
user
(such French) in the "Settings|Language" menu. By choos
ed
the new language in the menu,
user
s can dynamically switch to the new language interface without having to restart the program. After closeing the program, RadminM will automatically use the new language interface select
ed
by the
user
when the next start. Note, Interm
ed
iate of language file can not have blank lines. Blank lines will mean the end of the file, the items will not be able to find the translation after the blank line. If you ne
ed
a blank line identification or separator, you can add english semicolon in front of blank line. That is a comment line. If you ne
ed
leading or rear guide space, the string of language file may be contain
ed
with double quotes or single quotes.Or it ne
ed
not quotation marks. This program's multilingual support features achieve by reference of Yonsm's way, interest
ed
user
s may visit the website http://yonsm.net/ini-language-engine/. (3). Use Radmin Help If
user
want to use Help->Radmin Help of this program,
user
ne
ed
to copy the chm help file to Radmin35.chm in the RadminM directory. 7. Disclaimer
User
s can choose and use this program at their discretion. Please indicate the source if reproduc
ed
. The author does not assume any responsibility for all the consequences!
微软内部资料-SQL性能优化3
Contents Overview 1 Lesson 1: Concepts –
Lock
s and
Lock
Manager 3 Lesson 2: Concepts – Batch and Transaction 31 Lesson 3: Concepts –
Lock
s and Applications 51 Lesson 4: Information Collection and Analysis 63 Lesson 5: Concepts – Formulating and Implementing Resolution 81 Module 4: Troubleshooting
Lock
ing and B
lock
ing Overview At the end of this module, you will be able to: Discuss how
lock
manager uses
lock
mode,
lock
resources, and
lock
compatibility to achieve transaction isolation. Describe the various transaction types and how transactions differ from batches. Describe how to troubleshoot b
lock
ing and
lock
ing issues. Analyze the output of b
lock
ing scripts and Microsoft® SQL Server™ Profiler to troubleshoot
lock
ing and b
lock
ing issues. Formulate hypothesis to resolve
lock
ing and b
lock
ing issues. Lesson 1: Concepts –
Lock
s and
Lock
Manager This lesson outlines some of the common causes that contribute to the perception of a slow server. What You Will Learn After completing this lesson, you will be able to: Describe
lock
ing architecture us
ed
by SQL Server. Identify the various
lock
modes us
ed
by SQL Server. Discuss
lock
compatibility and concurrent access. Identify different types of
lock
resources. Discuss dynamic
lock
ing and
lock
escalation. Differentiate
lock
s, latches, and other SQL Server internal “
lock
ing” mechanism such as spin
lock
s and other synchronization objects. Recommend
ed
Reading Chapter 14 “
Lock
ing”, Inside SQL Server 2000 by Kalen Delaney SOX000821700049 – SQL 7.0 How to interpret
lock
resource Ids SOX000925700237 – TITLE:
Lock
escalation in SQL 7.0 SOX001109700040 – INF: Queries with PREFETCH in the plan hold
lock
until the end of transaction
Lock
ing Concepts Delivery Tip Prior to delivering this material, test the class to see if they fully understand the different isolation levels. If the class is not confident in their understanding, review appendix A04_
Lock
ing and its accompanying PowerPoint® file. Transactions in SQL Server provide the ACID properties: Atomicity A transaction either commits or aborts. If a transaction commits, all of its effects remain. If it aborts, all of its effects are undone. It is an “all or nothing” operation. Consistency An application should maintain the consistency of a database. For example, if you defer constraint checking, it is your responsibility to ensure that the database is consistent. Isolation Concurrent transactions are isolat
ed
from the updates of other incomplete transactions. These updates do not constitute a consistent state. This property is often call
ed
serializability. For example, a second transaction traversing the doubly link
ed
list mention
ed
above would see the list before or after the insert, but it will see only complete changes. Durability After a transaction commits, its effects will persist even if there are system failures. Consistency and isolation are the most important in describing SQL Server’s
lock
ing model. It is up to the application to define what consistency means, and isolation in some form is ne
ed
ed
to achieve consistent results. SQL Server uses
lock
ing to achieve isolation. Definition of Dependency: A set of transactions can run concurrently if their outputs are disjoint from the union of one another’s input and output sets. For example, if T1 writes some object that is in T2’s input or output set, there is a dependency between T1 and T2. Bad Dependencies These include lost updates, dirty reads, non-repeatable reads, and phantoms. ANSI SQL Isolation Levels An isolation level determines the degree to which data is isolat
ed
for use by one process and guard
ed
against interference from other processes. Prior to SQL Server 7.0, REPEATABLE READ and SERIALIZABLE isolation levels were synonymous. There was no way to prevent non-repeatable reads while not preventing phantoms. By default, SQL Server 2000 operates at an isolation level of READ COMMITT
ED
. To make use of either more or less strict isolation levels in applications,
lock
ing can be customiz
ed
for an entire session by setting the isolation level of the session with the SET TRANSACTION ISOLATION LEVEL statement. To determine the transaction isolation level currently set, use the DBCC
USER
OPTIONS statement, for example: USE pubs GO SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO DBCC
USER
OPTIONS GO Multigranular
Lock
ing Multigranular
Lock
ing In our example, if one transaction (T1) holds an exclusive
lock
at the table level, and another transaction (T2) holds an exclusive
lock
at the row level, each of the transactions believe they have exclusive access to the resource. In this scenario, since T1 believes it
lock
s the entire table, it might inadvertently make changes to the same row that T2 thought it has
lock
ed
exclusively. In a multigranular
lock
ing environment, there must be a way to effectively overcome this scenario. Intent
lock
is the answer to this problem. Intent
Lock
Intent
Lock
is the term us
ed
to mean placing a marker in a higher-level
lock
queue. The type of intent
lock
can also be call
ed
the multigranular
lock
mode. An intent
lock
indicates that SQL Server wants to acquire a shar
ed
(S)
lock
or exclusive (X)
lock
on some of the resources lower down in the hierarchy. For example, a shar
ed
intent
lock
plac
ed
at the table level means that a transaction intends on placing shar
ed
(S)
lock
s on pages or rows within that table. Setting an intent
lock
at the table level prevents another transaction from subsequently acquiring an exclusive (X)
lock
on the table containing that page. Intent
lock
s improve performance because SQL Server examines intent
lock
s only at the table level to determine whether a transaction can safely acquire a
lock
on that table. This removes the requirement to examine every row or page
lock
on the table to determine whether a transaction can
lock
the entire table.
Lock
Mode The code shown in the slide represents how the
lock
mode is stor
ed
internally. You can see these codes by querying the master.dbo.spt_values table: SELECT * FROM master.dbo.spt_values WHERE type = N'L' However, the req_mode column of master.dbo.sys
lock
info has
lock
mode code that is one less than the code values shown here. For example, value of req_mode = 3 represents the Shar
ed
lock
mode rather than the Schema Modification
lock
mode.
Lock
Compatibility These
lock
s can apply at any coarser level of granularity. If a row is
lock
ed
, SQL Server will apply intent
lock
s at both the page and the table level. If a page is
lock
ed
, SQL Server will apply an intent
lock
at the table level. SIX
lock
s imply that we have shar
ed
access to a resource and we have also plac
ed
X
lock
s at a lower level in the hierarchy. SQL Server never asks for SIX
lock
s directly, they are always the result of a conversion. For example, suppose a transaction scann
ed
a page using an S
lock
and then subsequently decid
ed
to perform a row level update. The row would obtain an X
lock
, but now the page would require an IX
lock
. The resultant mode on the page would be SIX. Another type of table
lock
is a schema stability
lock
(Sch-S) and is compatible with all table
lock
s except the schema modification
lock
(Sch-M). The schema modification
lock
(Sch-M) is incompatible with all table
lock
s.
Lock
ing Resources Delivery Tip Note the differences between Key and Key Range
lock
s. Key Range
lock
s will be cover
ed
in a couple of slides. SQL Server can
lock
these resources: Item Description DB A database. File A database file Index An entire index of a table. Table An entire table, including all data and indexes. Extent A contiguous group of data pages or index pages. Page An 8-KB data page or index page. Key Row
lock
within an index. Key-range A key-range. Us
ed
to
lock
ranges between
record
s in a table to prevent phantom insertions or deletions into a set of
record
s. Ensures serializable transactions. RID A Row Identifier. Us
ed
to individually
lock
a single row within a table. Application A
lock
resource defin
ed
by an application. The
lock
manager knows nothing about the resource format. It simply compares the 'strings' representing the
lock
resources to determine whether it has found a match. If a match is found, it knows that resource is already
lock
ed
. Some of the resources have “sub-resources.” The followings are sub-resources display
ed
by the sp_
lock
output: Database
Lock
Sub-Resources: Full Database
Lock
(default) [BULK-OP-DB] – Bulk Operation
Lock
for Database [BULK-OP-LOG] – Bulk Operation
Lock
for Log Table
Lock
Sub-Resources: Full Table
Lock
(default) [UPD-STATS] – Update statistics
Lock
[COMPILE] – Compile
Lock
Index
Lock
sub-Resources: Full Index
Lock
(default) [INDEX_ID] – Index ID
Lock
[INDEX_NAME] – Index Name
Lock
[BULK_ALLOC] – Bulk Allocation
Lock
[DEFRAG] – Defragmentation
Lock
For more information, see also… SOX000821700049 SQL 7.0 How to interpret
lock
resource Ids
Lock
Resource B
lock
The resource type has the following resource b
lock
format: Resource Type (Code) Content DB (2) Data 1: sub-resource; Data 2: 0; Data 3: 0 File (3) Data 1: File ID; Data 2: 0; Data 3: 0 Index (4) Data 1: Object ID; Data 2: sub-resource; Data 3: Index ID Table (5) Data 1: Object ID; Data 2: sub-resource; Data 3: 0. Page (6) Data 1: Page Number; Data 3: 0. Key (7) Data 1: Object ID; Data 2: Index ID; Data 3: Hash
ed
Key Extent (8) Data 1: Extent ID; Data 3: 0. RID (9) Data 1: RID; Data 3: 0. Application (10) Data 1: Application resource name The rsc_bin column of master..sys
lock
info contains the resource b
lock
in hexadecimal format. For an example of how to decode value from this column using the information above, let us assume we have the following value: 0x000705001F83D775010002014F0BEC4E With byte swapping within each field, this can be decod
ed
as: Byte 0: Flag – 0x00 Byte 1: Resource Type – 0x07 (Key) Byte 2-3: DBID – 0x0005 Byte 4-7: ObjectID – 0x 75D7831F (1977058079) Byte 8-9: IndexID – 0x0001 Byte 10-16: Hash Key value – 0x 02014F0BEC4E For more information about how to decode this value, see also… Inside SQL Server 2000, pages 803 and 806. Key Range
Lock
ing Key Range
Lock
ing To support SERIALIZABLE transaction semantics, SQL Server ne
ed
s to
lock
sets of rows specifi
ed
by a pr
ed
icate, such as WHERE salary BETWEEN 30000 AND 50000 SQL Server ne
ed
s to
lock
data that does not exist! If no rows satisfy the WHERE condition the first time the range is scann
ed
, no rows should be return
ed
on any subsequent scans. Key range
lock
s are similar to row
lock
s on index keys (whether cluster
ed
or not). The
lock
s are plac
ed
on individual keys rather than at the node level. The hash value consists of all the key components and the locator. So, for a noncluster
ed
index over a heap, where columns c1 and c2 where index
ed
, the hash would contain contributions from c1, c2 and the RID. A key range
lock
appli
ed
to a particular key means that all keys between the value
lock
ed
and the next value would be
lock
ed
for all data modification. Key range
lock
s can
lock
a slightly larger range than that impli
ed
by the WHERE clause. Suppose the following select was execut
ed
in a transaction with isolation level SERIALIZABLE: SELECT * FROM members WHERE first_name between ‘Al’ and ‘Carl’ If 'Al', 'Bob', and 'Dave' are index keys in the table, the first two of these would acquire key range
lock
s. Although this would prevent anyone from inserting either 'Alex' or 'Ben', it would also prevent someone from inserting 'Dan', which is not within the range of the WHERE clause. Prior to SQL Server 7.0, page
lock
ing was us
ed
to prevent phantoms by
lock
ing the entire set of pages on which the phantom would exist. This can be too conservative. Key Range
lock
ing lets SQL Server
lock
only a much more restrictive area of the table. Impact Key-range
lock
ing ensures that these scenarios are SERIALIZABLE: Range scan query Singleton fetch of nonexistent row Delete operation Insert operation However, the following conditions must be satisfi
ed
before key-range
lock
ing can occur: The transaction-isolation level must be set to SERIALIZABLE. The operation perform
ed
on the data must use an index range access. Range
lock
ing is activat
ed
only when query processing (such as the optimizer) chooses an index path to access the data. Key Range
Lock
Mode Again, the req_mode column of master.dbo.sys
lock
info has
lock
mode code that is one less than the code values shown here. Dynamic
Lock
ing When modifying individual rows, SQL Server typically would take row
lock
s to maximize concurrency (for example, OLTP, order-entry application). When scanning larger volumes of data, it would be more appropriate to take page or table
lock
s to minimize the cost of acquiring
lock
s (for example, DSS, data warehouse, reporting).
Lock
ing Decision The decision about which unit to
lock
is made dynamically, taking many factors into account, including other activity on the system. For example, if there are multiple transactions currently accessing a table, SQL Server will tend to favor row
lock
ing more so than it otherwise would. It may mean the difference between scanning the table now and paying a bit more in
lock
ing cost, or having to wait to acquire a more coarse
lock
. A preliminary
lock
ing decision is made during query optimization, but that decision can be adjust
ed
when the query is actually execut
ed
.
Lock
Escalation When the
lock
count for the transaction exce
ed
s and is a multiple of ESCALATION_THRESHOLD (1250), the
Lock
Manager attempts to escalate. For example, when a transaction acquir
ed
1250
lock
s,
lock
manager will try to escalate. The number of
lock
s held may continue to increase after the escalation attempt (for example, because new tables are access
ed
, or the previous
lock
escalation attempts fail
ed
due to incompatible
lock
s held by another spid). If the
lock
count for this transaction reaches 2500 (1250 * 2),
Lock
Manager will attempt escalation again. The
Lock
Manager looks at the
lock
memory it is using and if it is more than 40 percent of SQL Server’s allocat
ed
buffer pool memory, it tries to find a scan (SDES) where no escalation has already been perform
ed
. It then repeats the search operation until all scans have been escalat
ed
or until the memory us
ed
drops under the MEMORY_LOAD_ESCALATION_THRESHOLD (40%) value. If
lock
escalation is not possible or fails to significantly r
ed
uce
lock
memory footprint, SQL Server can continue to acquire
lock
s until the total
lock
memory reaches 60 percent of the buffer pool (MAX_
LOCK
_RESOURCE_MEMORY_PERCENTAGE=60).
Lock
escalation may be also done when a single scan (SDES) holds more than
LOCK
_ESCALATION_THRESHOLD (765)
lock
s. There is no
lock
escalation on temporary tables or system tables. Trace Flag 1211 disables
lock
escalation. Important Do not relay this to the customer without careful consideration.
Lock
escalation is a necessary feature, not something to be avoid
ed
completely. Trace flags are global and disabling
lock
escalation could lead to out of memory situations, extremely poor performing queries, or other problems.
Lock
escalation tracing can be seen using the Profiler or with the general
lock
ing trace flag, -T1200. However, Trace Flag 1200 shows all
lock
activity so it should not be usable on a production system. For more information, see also… SOX000925700237 “TITLE: SQL 7.0
Lock
escalation in SQL 7.0”
Lock
Timeout Application
Lock
Timeout An application can set
lock
timeout for a session with the SET option: SET
LOCK
_TIMEOUT N where N is a number of milliseconds. A value of -1 means that there will be no timeout, which is equivalent to the version 6.5 behavior. A value of 0 means that there will be no waiting; if a process finds a resource
lock
ed
, it will generate error message 1222 and continue with the next statement. The current value of
LOCK
_TIMEOUT is stor
ed
in the global variable @@
lock
_timeout. Note After a
lock
timeout any transaction containing the statement, is roll
ed
back or cancel
ed
by SQL Server 2000 (bug#352640 was fil
ed
). This behavior is different from that of SQL Server 7.0. With SQL Server 7.0, the application must have an error handler that can trap error 1222 and if an application does not trap the error, it can proce
ed
unaware that an individual statement within a transaction has been cancel
ed
, and errors can occur because statements later in the transaction may depend on the statement that was never execut
ed
. Bug#352640 is fix
ed
in hotfix build 8.00.266 whereby a
lock
timeout will only Internal
Lock
Timeout At time, internal operations within SQL Server will attempt to acquire
lock
s via
lock
manager. Typically, these
lock
requests are issu
ed
with “no waiting.” For example, the ghost
record
processing might try to clean up rows on a particular page, and before it can do that, it ne
ed
s to
lock
the page. Thus, the ghost
record
manager will request a page
lock
with no wait so that if it cannot
lock
the page, it will just move on to other pages; it can always come back to this page later. If you look at SQL Profiler
Lock
: Timeout events, internal
lock
timeout typically have a duration value of zero.
Lock
Duration
Lock
Mode and Transaction Isolation Level For REPEATABLE READ transaction isolation level, update
lock
s are held until data is read and process
ed
, unless promot
ed
to exclusive
lock
s. "Data is process
ed
" means that we have decid
ed
whether the row in question match
ed
the search criteria; if not then the update
lock
is releas
ed
, otherwise, we get an exclusive
lock
and make the modification. Consider the following query: use northwind go dbcc traceon(3604, 1200, 1211) -- turn on
lock
tracing -- and disable escalation go set transaction isolation level repeatable read begin tran update dbo.[order details] set discount = convert (real, discount) where discount = 0.0 exec sp_
lock
Update
lock
s are promot
ed
to exclusive
lock
s when there is a match; otherwise, the update
lock
is releas
ed
. The sp_
lock
output verifies that the SPID does not hold any update
lock
s or shar
ed
lock
s at the end of the query.
Lock
escalation is turn
ed
off so that exclusive table
lock
is not held at the end. Warning Do not use trace flag 1200 in a production environment because it produces a lot of output and slows down the server. Trace flag 1211 should not be us
ed
unless you have done extensive study to make sure it helps with performance. These trace flags are us
ed
here for illustration and learning purposes only.
Lock
Ownership Most of the
lock
ing discussion in this lesson relates to
lock
s own
ed
by “transactions.” In addition to transaction, cursor and session can be owners of
lock
s and they both affect how long
lock
s are held. For every row that is fetch
ed
, when SCROLL_
LOCK
S option is us
ed
, regardless of the state of a transaction, a cursor
lock
is held until the next row is fetch
ed
or when the cursor is clos
ed
.
Lock
s own
ed
by session are outside the scope of a transaction. The duration of these
lock
s are bound
ed
by the connection and the process will continue to hold these
lock
s until the process disconnects. A typical
lock
own
ed
by session is the database (DB)
lock
.
Lock
ing – Read Committ
ed
Scan Under read committ
ed
isolation level, when database pages are scann
ed
, shar
ed
lock
s are held when the page is read and process
ed
. The shar
ed
lock
s are releas
ed
“behind” the scan and allow other transactions to update rows. It is important to note that the shar
ed
lock
currently acquir
ed
will not be releas
ed
until shar
ed
lock
for the next page is successfully acquir
ed
(this is commonly know as “crabbing”). If the same pages are scann
ed
again, rows may be modifi
ed
or delet
ed
by other transactions.
Lock
ing – Repeatable Read Scan Under repeatable read isolation level, when database pages are scann
ed
, shar
ed
lock
s are held when the page is read and process
ed
. SQL Server continues to hold these shar
ed
lock
s, thus preventing other transactions to update rows. If the same pages are scann
ed
again, previously scann
ed
rows will not change but new rows may be add
ed
by other transactions.
Lock
ing – Serializable Read Scan Under serializable read isolation level, when database pages are scann
ed
, shar
ed
lock
s are held not only on rows but also on scann
ed
key range. SQL Server continues to hold these shar
ed
lock
s until the end of transaction. Because key range
lock
s are held, not only will this prevent other transactions from modifying the rows, no new rows can be insert
ed
. Prefetch and Isolation Level Prefetch and
Lock
ing Behavior The prefetch feature is available for use with SQL Server 7.0 and SQL Server 2000. When searching for data using a noncluster
ed
index, the index is search
ed
for a particular value. When that value is found, the index points to the disk address. The traditional approach would be to imm
ed
iately issue an I/O for that row, given the disk address. The result is one synchronous I/O per row and, at most, one disk at a time working to evaluate the query. This does not take advantage of strip
ed
disk sets. The prefetch feature takes a different approach. It continues looking for more
record
pointers in the noncluster
ed
index. When it has collect
ed
a number of them, it provides the storage engine with prefetch hints. These hints tell the storage engine that the query processor will ne
ed
these particular
record
s soon. The storage engine can now issue several I/Os simultaneously, taking advantage of strip
ed
disk sets to execute multiple operations simultaneously. For example, if the engine is scanning a noncluster
ed
index to determine which rows qualify but will eventually ne
ed
to visit the data page as well to access columns that are not in the index, it may decide to submit asynchronous page read requests for a group of qualifying rows. The prefetch data pages are then revisit
ed
later to avoid waiting for each individual page read to complete in a serial fashion. This data access path requires that a
lock
be held between the prefetch request and the row lookup to stabilize the row on the page so it is not to be mov
ed
by a page split or cluster
ed
key update. For our example, the isolation level of the query is escalat
ed
to REPEATABLE READ, overriding the transaction isolation level. With SQL Server 7.0 and SQL Server 2000, portions of a transaction can execute at a different transaction isolation level than the entire transaction itself. This is implement
ed
as
lock
classes.
Lock
classes are us
ed
to control
lock
lifetime when portions of a transaction ne
ed
to execute at a stricter isolation level than the underlying transaction. Unfortunately, in SQL Server 7.0 and SQL Server 2000, the
lock
class is creat
ed
at the topmost operator of the query and hence releas
ed
only at the end of the query. Currently there is no support to release the
lock
(
lock
class) after the row has been discard
ed
or fetch
ed
by the filter or join operator. This is because isolation level can be set at the query level via a
lock
class, but no lower. Because of this,
lock
s acquir
ed
during the query will not be releas
ed
until the query completes. If prefetch is occurring you may see a single SPID that holds hundr
ed
s of Shar
ed
KEY or PAG
lock
s even though the connection’s isolation level is READ COMMITT
ED
. Isolation level can be determin
ed
from DBCC PSS output. For details about this behavior see “SOX001109700040 INF: Queries with PREFETCH in the plan hold
lock
until the end of transaction”. Other
Lock
ing Mechanism
Lock
manager does not manage latches and spin
lock
s. Latches Latches are internal mechanisms us
ed
to protect pages while doing operations such as placing a row physically on a page, compressing space on a page, or retrieving rows from a page. Latches can roughly be divid
ed
into I/O latches and non-I/O latches. If you see a high number of non-I/O relat
ed
latches, SQL Server is usually doing a large number of hash or sort operations in tempdb. You can monitor latch activities via DBCC SQLPERF(‘WAITSTATS’) command. Spin
lock
A spin
lock
is an internal data structure that is us
ed
to protect vital information that is shar
ed
within SQL Server. On a multi-processor machine, when SQL Server tries to access a particular resource protect
ed
by a spin
lock
, it must first acquire the spin
lock
. If it fails, it executes a loop that will check to see if the
lock
is available and if not, decrements a counter. If the counter reaches zero, it yields the processor to another thread and goes into a “sleep” (wait) state for a pre-determin
ed
amount of time. When it wakes, hopefully, the
lock
is free and available. If not, the loop starts again and it is terminat
ed
only when the
lock
is acquir
ed
. The reason for implementing a spin
lock
is that it is probably less costly to “spin” for a short time rather than yielding the processor. Yielding the processor will force an expensive context switch where: The old thread’s state must be sav
ed
The new thread’s state must be reload
ed
The data stor
ed
in the L1 and L2 cache are useless to the processor On a single-processor computer, the loop is not useful because no other thread can be running and thus, no one can release the spin
lock
for the currently executing thread to acquire. In this situation, the thread yields the processor imm
ed
iately. Lesson 2: Concepts – Batch and Transaction This lesson outlines some of the common causes that contribute to the perception of a slow server. What You Will Learn After completing this lesson, you will be able to: Review batch processing and error checking. Review explicit, implicit and autocommit transactions and transaction nesting level. Discuss how commit and rollback transaction done in stor
ed
proc
ed
ure and trigger affects transaction nesting level. Discuss various transaction isolation level and their impact on
lock
ing. Discuss the difference between aborting a statement, a transaction, and a batch. Describe how @@error, @@transcount, and @@rowcount can be us
ed
for error checking and handling. Recommend
ed
Reading Charter 12 “Transactions and Triggers”, Inside SQL Server 2000 by Kalen Delaney Batch Definition SQL Profiler Statements and Batches To help further your understanding of what is a batch and what is a statement, you can use SQL Profiler to study the definition of batch and statement. Try This: Using SQL Profiler to Analyze Batch 1. Log on to a server with Query Analyzer 2. Startup the SQL Profiler against the same server 3. Start a trace using the “StandardSQLProfiler” template 4. Execute the following using Query Analyzer: SELECT @@VERSION SELECT @@SPID The ‘SQL:BatchComplet
ed
’ event is captur
ed
by the trace. It shows both the statements as a single batch. 5. Now execute the following using Query Analyzer {call sp_who()} What shows up? The ‘RPC:Complet
ed
’ with the sp_who information. RPC is simply another entry point to the SQL Server to call stor
ed
proc
ed
ures with native data types. This allows one to avoid parsing. The ‘RPC:Complet
ed
’ event should be consider
ed
the same as a batch for the purposes of this discussion. Stop the current trace and start a new trace using the “SQLProfilerTSQL_SPs” template. Issue the same command as outlines in step 5 above. Looking at the output, not only can you see the batch markers but each statement as execut
ed
within the batch. Autocommit, Explicit, and Implicit Transaction Autocommit Transaction Mode (Default) Autocommit mode is the default transaction management mode of SQL Server. Every Transact-SQL statement, whether it is a standalone statement or part of a batch, is committ
ed
or roll
ed
back when it completes. If a statement completes successfully, it is committ
ed
; if it encounters any error, it is roll
ed
back. A SQL Server connection operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions. Autocommit mode is also the default mode for ADO, OLE DB, ODBC, and DB-Library. A SQL Server connection operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction, or implicit transaction mode is set on. When the explicit transaction is committ
ed
or roll
ed
back, or when implicit transaction mode is turn
ed
off, SQL Server returns to autocommit mode. Explicit Transaction Mode An explicit transaction is a transaction that starts with a BEGIN TRANSACTION statement. An explicit transaction can contain one or more statements and must be terminat
ed
by either a COMMIT TRANSACTION or a ROLLBACK TRANSACTION statement. Implicit Transaction Mode SQL Server can automatically or, more precisely, implicitly start a transaction for you if a SET IMPLICIT_TRANSACTIONS ON statement is run or if the implicit transaction option is turn
ed
on globally by running sp_configure ‘
user
options’ 2. (Actually, the bit mask 0x2 must be turn
ed
on for the
user
option so you might have to perform an ‘OR’ operation with the existing
user
option value.) See SQL Server 2000 Books Online on how to turn on implicit transaction under ODBC and OLE DB (acdata.chm::/ac_8_md_06_2g6r.htm). Transaction Nesting Explicit transactions can be nest
ed
. Committing inner transactions is ignor
ed
by SQL Server other than to decrements @@TRANCOUNT. The transaction is either committ
ed
or roll
ed
back bas
ed
on the action taken at the end of the outermost transaction. If the outer transaction is committ
ed
, the inner nest
ed
transactions are also committ
ed
. If the outer transaction is roll
ed
back, then all inner transactions are also roll
ed
back, regardless of whether the inner transactions were individually committ
ed
. Each call to COMMIT TRANSACTION applies to the last execut
ed
BEGIN TRANSACTION. If the BEGIN TRANSACTION statements are nest
ed
, then a COMMIT statement applies only to the last nest
ed
transaction, which is the innermost transaction. Even if a COMMIT TRANSACTION transaction_name statement within a nest
ed
transaction refers to the transaction name of the outer transaction, the commit applies only to the innermost transaction. If a ROLLBACK TRANSACTION statement without a transaction_name parameter is execut
ed
at any level of a set of nest
ed
transaction, it rolls back all the nest
ed
transactions, including the outermost transaction. The @@TRANCOUNT function
record
s the current transaction nesting level. Each BEGIN TRANSACTION statement increments @@TRANCOUNT by one. Each COMMIT TRANSACTION statement decrements @@TRANCOUNT by one. A ROLLBACK TRANSACTION statement that does not have a transaction name rolls back all nest
ed
transactions and decrements @@TRANCOUNT to 0. A ROLLBACK TRANSACTION that uses the transaction name of the outermost transaction in a set of nest
ed
transactions rolls back all the nest
ed
transactions and decrements @@TRANCOUNT to 0. When you are unsure if you are already in a transaction, SELECT @@TRANCOUNT to determine whether it is 1 or more. If @@TRANCOUNT is 0 you are not in a transaction. You can also find the transaction nesting level by checking the sysprocess.open_tran column. See SQL Server 2000 Books Online topic “Nesting Transactions” (acdata.chm::/ac_8_md_06_66nq.htm) for more information. Statement, Transaction, and Batch Abort One batch can have many statements and one transaction can have multiple statements, also. One transaction can span multiple batches and one batch can have multiple transactions. Statement Abort Currently executing statement is abort
ed
. This can be a bit confusing when you start talking about statements in a trigger or stor
ed
proc
ed
ure. Let us look closely at the following trigger: CREATE TRIGGER TRG8134 ON TBL8134 AFTER INSERT AS BEGIN SELECT 1/0 SELECT 'Next command in trigger' END To fire the INSERT trigger, the batch could be as simple as ‘INSERT INTO TBL8134 VALUES(1)’. However, the trigger contains two statements that must be execut
ed
as part of the batch to satisfy the clients insert request. When the ‘SELECT 1/0’ causes the divide by zero error, a statement abort is issu
ed
for the ‘SELECT 1/0’ statement. Batch and Transaction Abort On SQL Server 2000 (and SQL Server 7.0) whenever a non-informational error is encounter
ed
in a trigger, the statement abort is promot
ed
to a batch and transactional abort. Thus, in the example the statement abort for ‘select 1/0’ promotion results in an entire batch abort. No further statements in the trigger or batch will be execut
ed
and a rollback is issu
ed
. On SQL Server 6.5, the statement aborts imm
ed
iately and results in a transaction abort. However, the rest of the statements within the trigger are execut
ed
. This trigger could return ‘Next command in trigger’ as a result set. Once the trigger completes the batch abort promotion takes effect. Conversely, submitting a similar set of statements in a standalone batch can result in different behavior. SELECT 1/0 SELECT 'Next command in batch' Not considering the set option possibilities, a divide by zero error generally results in a statement abort. Since it is not in a trigger, the promotion to a batch abort is avoid
ed
and subsequent SELECT statement can execute. The programmer should add an “if @@ERROR” check imm
ed
iately after the ‘select 1/0’ to T-SQL execution to control the flow correctly. Aborting and Set Options ARITHABORT If SET ARITHABORT is ON, these error conditions cause the query or batch to terminate. If the errors occur in a transaction, the transaction is roll
ed
back. If SET ARITHABORT is OFF and one of these errors occurs, a warning message is display
ed
, and NULL is assign
ed
to the result of the arithmetic operation. When an INSERT, DELETE, or UPDATE statement encounters an arithmetic error (overflow, divide-by-zero, or a domain error) during expression evaluation when SET ARITHABORT is OFF, SQL Server inserts or updates a NULL value. If the target column is not nullable, the insert or update action fails and the
user
receives an error. XACT_ABORT When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminat
ed
and roll
ed
back. When OFF, only the Transact-SQL statement that rais
ed
the error is roll
ed
back and the transaction continues processing. Compile errors, such as syntax errors, are not affect
ed
by SET XACT_ABORT. For example: CREATE TABLE t1 (a int PRIMARY KEY) CREATE TABLE t2 (a int REFERENCES t1(a)) GO INSERT INTO t1 VALUES (1) INSERT INTO t1 VALUES (3) INSERT INTO t1 VALUES (4) INSERT INTO t1 VALUES (6) GO SET XACT_ABORT OFF GO BEGIN TRAN INSERT INTO t2 VALUES (1) INSERT INTO t2 VALUES (2) /* Foreign key error */ INSERT INTO t2 VALUES (3) COMMIT TRAN SELECT 'Continue running batch 1...' GO SET XACT_ABORT ON GO BEGIN TRAN INSERT INTO t2 VALUES (4) INSERT INTO t2 VALUES (5) /* Foreign key error */ INSERT INTO t2 VALUES (6) COMMIT TRAN SELECT 'Continue running batch 2...' GO /* Select shows only keys 1 and 3 add
ed
. Key 2 insert fail
ed
and was roll
ed
back, but XACT_ABORT was OFF and rest of transaction succe
ed
ed
. Key 5 insert error with XACT_ABORT ON caus
ed
all of the second transaction to roll back. Also note that 'Continue running batch 2...' is not Return
ed
to indicate that the batch is abort
ed
. */ SELECT * FROM t2 GO DROP TABLE t2 DROP TABLE t1 GO Compile and Run-time Errors Compile Errors Compile errors are encounter
ed
during syntax checks, security checks, and other general operations to prepare the batch for execution. These errors can prevent the optimization of the query and thus lead to imm
ed
iate abort. The statement is not run and the batch is abort
ed
. The transaction state is generally left untouch
ed
. For example, assume there are four statements in a particular batch. If the third statement has a syntax error, none of the statements in the batch is execut
ed
. Optimization Errors Optimization errors would include rare situations where the statement encounters a problem when attempting to build an optimal execution plan. Example: “too many tables referenc
ed
in the query” error is report
ed
because a “work table” was add
ed
to the plan. Runtime Errors Runtime errors are those that are encounter
ed
during the execution of the query. Consider the following batch: SELECT * FROM pubs.dbo.titles UPDATE pubs.dbo.authors SET au_lname = au_lname SELECT * FROM foo UPDATE pubs.dbo.authors SET au_lname = au_lname If you run the above statements in a batch, the first two statements will be execut
ed
, the third statement will fail because table foo does not exist, and the batch will terminate. Deferr
ed
Name Resolution is the feature that allows this batch to start executing before resolving the object foo. This feature allows SQL Server to delay object resolution and place a “placeholder” in the query’s execution. The object referenc
ed
by the placeholder is resolv
ed
until the query is execut
ed
. In our example, the execution of the statement “SELECT * FROM foo” will trigger another compile process to resolve the name again. This time, error message 208 is return
ed
. Error: 208, Level 16, State 1, Line 1 Invalid object name 'foo'. Message 208 can be encounter
ed
as a runtime or compile error depending on whether the Deferr
ed
Name Resolution feature is available. In SQL Server 6.5 this would be consider
ed
a compile error and on SQL Server 2000 (and SQL Server7.0) as a runtime error due to Deferr
ed
Name Resolution. In the following example, if a trigger referenc
ed
authors2, the error is detect
ed
as SQL Server attempts to execute the trigger. However, under SQL Server 6.5 the create trigger statement fails because authors2 does not exist at compile time. When errors are encounter
ed
in a trigger, generally, the statement, batch, and transaction are abort
ed
. You should be able to observe this by running the following script in pubs database: Create table tblTest(iID int) go create trigger trgInsert on tblTest for INSERT as begin select * from authors select * from authors2 select * from titles end go begin tran select 'Before' insert into tblTest values(1) select 'After' go select @@TRANCOUNT go When run in a batch, the statement and the batch are abort
ed
but the transaction remains active. The follow script illustrates this: begin tran select 'Before' select * from authors2 select 'After' go select @@TRANCOUNT go One other factor in a compile versus runtime error is implicit data type conversions. If you were to run the following statements on SQL Server 6.5 and SQL Server 2000 (and SQL Server 7.0): create table tblData(dtData datetime) go select 1 insert into tblData values(12/13/99) go On SQL Server 6.5, you get an error before execution of the batch begins so no statements are execut
ed
and the batch is abort
ed
. Error: 206, Level 16, State 2, Line 2 Operand type clash: int is incompatible with datetime On SQL Server 2000, you get the default value (1900-01-01 00:00:00.000) insert
ed
into the table. SQL Server 2000 implicit data type conversion treats this as integer division. The integer division of 12/13/99 is 0, so the default date and time value is insert
ed
, no error return
ed
. To correct the problem on either version is to wrap the date string with quotes. See Bug #56118 (sqlbug_70) for more details about this situation. Another example of a runtime error is a 605 message. Error: 605 Attempt to fetch logical page %S_PGID in database '%.*ls' belongs to object '%.*ls', not to object '%.*ls'. A 605 error is always a runtime error. However, depending on the transaction isolation level, (e.g. using the NO
LOCK
lock
hint), establish
ed
by the SPID the handling of the error can vary. Specifically, a 605 error is consider
ed
an ACCESS error. Errors associat
ed
with buffer and page access are found in the 600 series of errors. When the error is encounter
ed
, the isolation level of the SPID is examin
ed
to determine proper handling bas
ed
on information or fatal error level. Transaction Error Checking Not all errors cause transactions to automatically rollback. Although it is difficult to determine exactly which errors will rollback transactions and which errors will not, the main idea here is that programmers must perform error checking and handle errors appropriately. Error Handling Raiserror Details Raiserror seems to be a source of confusion but is really rather simple. Raiserror with severity levels of 20 or higher will terminate the connection. Of course, when the connection is terminat
ed
a full rollback of any open transaction will imm
ed
iately be instantiat
ed
by the SQL Server (except distribut
ed
transaction with DTC involv
ed
). Severity levels lower than 20 will simply result in the error message being return
ed
to the client. They do not affect the transaction scope of the connection. Consider the following batch: use pubs begin tran update authors set au_lname = 'smith' raiserror ('This is bad', 19, 1) with log select @@trancount With severity set at 19, the 'select @@trancount' will be execut
ed
after the raiserror statement and will return a value of 1. If severity is chang
ed
to 20, then the select statement will not run and the connection is broken. Important Error handling must occur not only in T-SQL batches and stor
ed
proc
ed
ures, but also in application program code. Transactions and Triggers (1 of 2) Basic behavior assumes the implicit transactions setting is set to OFF. This behavior makes it possible to identify business logic errors in a trigger, raise an error, rollback the action, and add an audit table entry. Logically, the insert to the audit table cannot take place before the ROLLBACK action and you would not want to build in the audit table insert into every applications error handler that violat
ed
the business rule of the trigger. For more information, see also… SQL Server 2000 Books Online topic “Rollbacks in stor
ed
proc
ed
ure and triggers“ (acdata.chm::/ac_8_md_06_4qcz.htm) IMPLICIT_TRANSACTIONS ON Behavior The behavior of firing other triggers on the same table can be tricky. Say you add
ed
a trigger that checks the CODE field. Read only versions of the rows contain the code ‘RO’ and read/write versions use ‘RW.’ Whenever someone tries to delete a row with a code ‘RO’ the trigger issues the rollback and logs an audit table entry. However, you also have a second trigger that is responsible for cascading delete operations. One client could issue the delete without implicit transactions on and only the current trigger would execute and then terminate the batch. However, a second client with implicit transactions on could issue the same delete and the secondary trigger would fire. You end up with a situation in which the cascading delete operations can take place (are committ
ed
) but the initial row remains in the table because of the rollback operation. None of the delete operations should be allow
ed
but because the transaction scope was restart
ed
because of the implicit transactions setting, they did. Transactions and Triggers (2 of 2) It is extremely difficult to determine the execution state of a trigger when using explicit rollback statements in combination with implicit transactions. The RETURN statement is not allow
ed
to return a value. The only way I have found to set the @@ERROR is using a ‘raiserror’ as the last execution statement in the last trigger to execute. If you modify the example, this following RAISERROR statement will set @@ERROR to 50000: CREATE TRIGGER trgTest on tblTest for INSERT AS BEGIN ROLLBACK INSERT INTO tblAudit VALUES (1) RAISERROR('This is bad', 14,1) END However, this value does not carry over to a secondary trigger for the same table. If you raise an error at the end of the first trigger and then look at @@ERROR in the secondary trigger the @@ERROR remains 0. Carrying Forward an Active/Open Transaction It is possible to exit from a trigger and carry forward an open transaction by issuing a BEGIN TRAN or by setting implicit transaction on and doing INSERT, UPDATE, or DELETE. Warning It is never recommend
ed
that a trigger call BEGIN TRANSACTION. By doing this you increment the transaction count. Invalid code logic, not calling commit transaction, can lead to a situation where the transaction count remains elevat
ed
upon exit of the trigger. Transaction Count The behavior is better explain
ed
by understanding how the server works. It does not matter whether you are in a transaction, when a modification takes place the transaction count is increment
ed
. So, in the simplest form, during the processing of an insert the transaction count is 1. On completion of the insert, the server will commit (and thus decrement the transaction count). If the commit identifies the transaction count has return
ed
to 0, the actual commit processing is complet
ed
. Issuing a commit when the transaction count is greater than 1 simply decrements the nest
ed
transaction counter. Thus, when we enter a trigger, the transaction count is 1. At the completion of the trigger, the transaction count will be 0 due to the commit issu
ed
at the end of the modification statement (insert). In our example, if the connection was already in a transaction and call
ed
the second INSERT, since implicit transaction is ON, the transaction count in the trigger will be 2 as long as the ROLLBACK is not execut
ed
. At the end of the insert, the commit is again issu
ed
to decrement the transaction reference count to 1. However, the value does not return to 0 so the transaction remains open/active. Subsequent triggers are only fir
ed
if the transaction count at the end of the trigger remains greater than or equal to 1. The key to continuation of secondary triggers and the batch is the transaction count at the end of a trigger execution. If the trigger that performs a rollback has done an explicit begin transaction or uses implicit transactions, subsequent triggers and the batch will continue. If the transaction count is not 1 or greater, subsequent triggers and the batch will not execute. Warning Forcing the transaction count after issuing a rollback is dangerous because you can easily loose track of your transaction nesting level. When performing an explicit rollback in a trigger, you should imm
ed
iately issue a return statement to maintain consistent behavior between a connection with and without implicit transaction settings. This will force the trigger(s) and batch to terminate imm
ed
iately. One of the methods of dealing with this issue is to run ‘SET IMPLICIT_TRANSACTIONS OFF’ as the first statement of any trigger. Other methods may entails checking @@TRANCOUNT at the end of the trigger and continue to COMMIT the transaction as long as @@TRANCOUNT is greater than 1. Examples The following examples are bas
ed
on this table: create table tbl50000Insert (iID int NOT NULL) go Note If more than one trigger is us
ed
, to guarantee the trigger firing sequence, the sp_settriggerorder command should be us
ed
. This command is omitt
ed
in these examples to simplify the complexity of the statements. First Example In the first example, the second trigger was never fir
ed
and the batch, starting with the insert statement, was abort
ed
. Thus, the print statement was never issu
ed
. print('Trigger issues rollback - cancels batch') go create trigger trg50000Insert on tbl50000Insert for INSERT as begin select 'Insert
ed
', * from insert
ed
rollback tran select 'End of trigger', @@TRANCOUNT as 'TRANCOUNT' end go create trigger trg50000Insert2 on tbl50000Insert for INSERT as begin select 'In Trigger2' select 'Trigger 2 Insert
ed
', * from insert
ed
end go insert into tbl50000Insert values(1) print('---------------------- In same batch') select * from tbl50000Insert go -- Cleanup drop trigger trg50000Insert drop trigger trg50000Insert2 go delete from tbl50000Insert Second Example The next example shows that since a new transaction is start
ed
, the second trigger will be fir
ed
and the print statement in the batch will be execut
ed
. Note that the insert is roll
ed
back. print('Trigger issues rollback - increases tran count to continue batch') go create trigger trg50000Insert on tbl50000Insert for INSERT as begin select 'Insert
ed
', * from insert
ed
rollback tran begin tran end go create trigger trg50000Insert2 on tbl50000Insert for INSERT as begin select 'In Trigger2' select 'Trigger 2 Insert
ed
', * from insert
ed
end go insert into tbl50000Insert values(2) print('---------------------- In same batch') select * from tbl50000Insert go -- Cleanup drop trigger trg50000Insert drop trigger trg50000Insert2 go delete from tbl50000Insert Third Example In the third example, the raiserror statement is us
ed
to set the @@ERROR value and the BEGIN TRAN statement is us
ed
in the trigger to allow the batch to continue to run. print('Trigger issues rollback - uses raiserror to set @@ERROR') go create trigger trg50000Insert on tbl50000Insert for INSERT as begin select 'Insert
ed
', * from insert
ed
rollback tran begin tran -- Increase @@trancount to allow -- batch to continue select @@trancount as ‘Trancount’ raiserror('This is from the trigger', 14,1) end go insert into tbl50000Insert values(3) select @@ERROR as 'ERROR', @@TRANCOUNT as 'Trancount' go -- Cleanup drop trigger trg50000Insert go delete from tbl50000Insert Fourth Example For the fourth example, a second trigger is add
ed
to illustrate the fact that @@ERROR value set in the first trigger will not be seen in the second trigger nor will it show up in the batch after the second trigger is fir
ed
. print('Trigger issues rollback - uses raiserror to set @@ERROR, not seen in second trigger and clear
ed
in batch') go create trigger trg50000Insert on tbl50000Insert for INSERT as begin select 'Insert
ed
', * from insert
ed
rollback begin tran -- Increase @@trancount to -- allow batch to continue select @@TRANCOUNT as 'Trancount' raiserror('This is from the trigger', 14,1) end go create trigger trg50000Insert2 on tbl50000Insert for INSERT as begin select @@ERROR as 'ERROR', @@TRANCOUNT as 'Trancount' end go insert into tbl50000Insert values(4) select @@ERROR as 'ERROR', @@TRANCOUNT as 'Trancount' go -- Cleanup drop trigger trg50000Insert drop trigger trg50000Insert2 go delete from tbl50000Insert Lesson 3: Concepts –
Lock
s and Applications This lesson outlines some of the common causes that contribute to the perception of a slow server. What You Will Learn After completing this lesson, you will be able to: Explain how
lock
hints are us
ed
and their impact. Discuss the effect on
lock
ing when an application uses Microsoft Transaction Server. Identify the different kinds of dead
lock
s including distribut
ed
dead
lock
. Recommend
ed
Reading Charter 14 “
Lock
ing”, Inside SQL Server 2000 by Kalen Delaney Charter 16 “Query Tuning”, Inside SQL Server 2000 by Kalen Delaney Q239753 – Dead
lock
Situation Not Detect
ed
by SQL Server Q288752 – B
lock
ed
SPID Not Participating in Dead
lock
May Incorrectly be Chosen as victim
Lock
ing Hints UPD
LOCK
If update
lock
s are us
ed
instead of shar
ed
lock
s while reading a table, the
lock
s are held until the end of the statement or transaction. UPD
LOCK
has the advantage of allowing you to read data (without b
lock
ing other readers) and update it later with the assurance that the data has not chang
ed
since you last read it. READPAST READPAST is an optimizer hint for use with SELECT statements. When this hint is us
ed
, SQL Server will read past
lock
ed
rows. For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, and 5. If transaction A changes the value of 3 to 8 but has not yet committ
ed
, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5. Tip READPAST only applies to transactions operating at READ COMMITT
ED
isolation and only reads past row-level
lock
s. This
lock
hint can be us
ed
to implement a work queue on a SQL Server table. For example, assume there are many external work requests being thrown into a table and they should be servic
ed
in approximate insertion order but they do not have to be completely FIFO. If you have 4 worker threads consuming work items from the queue they could each pick up a
record
using read past
lock
ing and then delete the entry from the queue and commit when they're done. If they fail, they could rollback, leaving the entry on the queue for the next worker thread to pick up. Caution The READPAST hint is not compatible with HOLD
LOCK
. Try This: Using
Lock
ing Hints 1. Open a Query Window and connect to the pubs database. 2. Execute the following statements (--Conn 1 is optional to help you keep track of each connection): BEGIN TRANSACTION -- Conn 1 UPDATE titles SET price = price * 0.9 WHERE title_id = 'BU1032' 3. Open a second connection and execute the following statements: SELECT @@
lock
_timeout -- Conn 2 GO SELECT * FROM titles SELECT * FROM authors 4. Open a third connection and execute the following statements: SET
LOCK
_TIMEOUT 0 -- Conn 3 SELECT * FROM titles SELECT * FROM authors 5. Open a fourth connection and execute the following statement: SELECT * FROM titles (READPAST) -- Conn 4 WHERE title_ID < 'C' SELECT * FROM authors How many
record
s were return
ed
? 3 6. Open a fifth connection and execute the following statement: SELECT * FROM titles (NO
LOCK
) -- Conn 5 WHERE title_ID 0 the
lock
manager also checks for dead
lock
s every time a SPID gets b
lock
ed
. So a single dead
lock
will trigger 20 seconds of more imm
ed
iate dead
lock
detection, but if no additional dead
lock
s occur in that 20 seconds, the
lock
manager no longer checks for dead
lock
s at each b
lock
and detection again only happens every 5 seconds. Although normally not ne
ed
ed
, you may use trace flag -T1205 to trace the dead
lock
detection process. Note Please note the distinction between application
lock
and other
lock
s’ dead
lock
detection. For application
lock
, we do not rollback the transaction of the dead
lock
victim but simply return a -3 to sp_getapp
lock
, which the application ne
ed
s to handle itself. Dead
lock
Resolution How is a dead
lock
resolv
ed
? SQL Server picks one of the connections as a dead
lock
victim. The victim is chosen bas
ed
on either which is the least expensive transaction (calculat
ed
using the number and size of the log
record
s) to roll back or in which process “SET DEAD
LOCK
_PRIORITY LOW” is specifi
ed
. The victim’s transaction is roll
ed
back, held
lock
s are releas
ed
, and SQL Server sends error 1205 to the victim’s client application to notify it that it was chosen as a victim. The other process can then obtain access to the resource it was waiting on and continue. Error 1205: Your transaction (process ID #%d) was dead
lock
ed
with another process and has been chosen as the dead
lock
victim. Rerun your transaction. Symptoms of dead
lock
ing Error 1205 usually is not written to the SQL Server errorlog. Unfortunately, you cannot use sp_altermessage to cause 1205 to be written to the errorlog. If the client application does not capture and display error 1205, some of the symptoms of dead
lock
occurring are: Clients complain of mysteriously cancel
ed
queries when using certain features of an application. May be accompani
ed
by excessive b
lock
ing.
Lock
contention increases the chances that a dead
lock
will occur. Triggers and Dead
lock
Triggers promote the dead
lock
priority of the SPID for the life of the trigger execution when the DEAD
LOCK
PRIORITY is not set to low. When a statement in a trigger causes a dead
lock
to occur, the SPID executing the trigger is given preferential treatment and will not become the victim. Warning Bug 235794 is fil
ed
against SQL Server 2000 where a b
lock
ed
SPID that is not a participant of a dead
lock
may incorrectly be chosen as a dead
lock
victim if the SPID is b
lock
ed
by one of the dead
lock
participants and the SPID has the least amount of transaction logging. See KB article Q288752: “B
lock
ed
Spid Not Participating in Dead
lock
May Incorrectly be Chosen as victim” for more information. Distribut
ed
Dead
lock
– Scenario 1 Distribut
ed
Dead
lock
s The term distribut
ed
dead
lock
is ambiguous. There are many types of distribut
ed
dead
lock
s. Scenario 1 Client application opens connection A, begins a transaction, acquires some
lock
s, opens connection B, connection B gets b
lock
ed
by A but the application is design
ed
to not commit A’s transaction until B completes. Note SQL Server has no way of knowing that connection A is somehow dependent on B – they are two distinct connections with two distinct transactions. This situation is discuss
ed
in scenario #4 in “Q224453 INF: Understanding and Resolving SQL Server 7.0 B
lock
ing Problems”. Distribut
ed
Dead
lock
– Scenario 2 Scenario 2 Distribut
ed
dead
lock
involving bound connections. Two connections can be bound into a single transaction context with sp_getbindtoken/sp_bindsession or via DTC. Spid 60 enlists in a transaction with spid 61. A third spid 62 is b
lock
ed
by spid 60, but spid 61 is b
lock
ed
by spid 62. Because they are doing work in the same transaction, spid 60 cannot commit until spid 61 finishes his work, but spid 61 is b
lock
ed
by 62 who is b
lock
ed
by 60. This scenario is describ
ed
in article “Q239753 - Dead
lock
Situation Not Detect
ed
by SQL Server.” Note SQL Server 6.5 and 7.0 do not detect this dead
lock
. The SQL Server 2000 dead
lock
detection algorithm has been enhanc
ed
to detect this type of distribut
ed
dead
lock
. The diagram in the slide illustrates this situation. Resources
lock
ed
by a spid are below that spid (in a box). Arrows indicate b
lock
ing and are drawn from the b
lock
ed
spid to the resource that the spid requires. A circle represents a transaction; spids in the same transaction are shown in the same circle. Distribut
ed
Dead
lock
– Scenario 3 Scenario 3 Distribut
ed
dead
lock
involving link
ed
servers or server-to-server RPC. Spid 60 on Server 1 executes a stor
ed
proc
ed
ure on Server 2 via link
ed
server. This stor
ed
proc
ed
ure does a loopback link
ed
server query against a table on Server 1, and this connection is b
lock
ed
by a
lock
held by Spid 60. Note No version of SQL Server is currently design
ed
to detect this distribut
ed
dead
lock
. Lesson 4: Information Collection and Analysis This lesson outlines some of the common causes that contribute to the perception of a slow server. What You Will Learn After completing this lesson, you will be able to: Identify specific information ne
ed
ed
for troubleshooting issues. Locate and collect information ne
ed
ed
for troubleshooting issues. Analyze output of DBCC Inputbuffer, DBCC PSS, and DBCC Page commands. Review information collect
ed
from master.dbo.sysprocesses table. Review information collect
ed
from master.dbo.sys
lock
info table. Review output of sp_who, sp_who2, sp_
lock
. Analyze Profiler log for query usage pattern. Review output of trace flags to help troubleshoot dead
lock
s. Recommend
ed
Reading Q244455 - INF: Definition of Sysprocesses Waittype and Lastwaittype Fields Q244456 - INF: Description of DBCC PSS Command for SQL Server 7.0 Q271509 - INF: How to Monitor SQL Server 2000 B
lock
ing Q251004 - How to Monitor SQL Server 7.0 B
lock
ing Q224453 - Understanding and Resolving SQL Server 7.0 B
lock
ing Problem Q282749 – BUG: Dead
lock
information report
ed
with SQL Server 2000 Profiler
Lock
ing and B
lock
ing Try This: Examine B
lock
ed
Processes 1. Open a Query Window and connect to the pubs database. Execute the following statements: BEGIN TRAN -- connection 1 UPDATE titles SET price = price + 1 2. Open another connection and execute the following statement: SELECT * FROM titles-- connection 2 3. Open a third connection and execute sp_who; note the process id (spid) of the b
lock
ed
process. (Connection 3) 4. In the same connection, execute the following: SELECT spid, cmd, waittype FROM master..sysprocesses WHERE waittype 0 -- connection 3 5. Do not close any of the connections! What was the wait type of the b
lock
ed
process? Try This: Look at
lock
s held Assumes all your connections are still open from the previous exercise. • Execute sp_
lock
-- Connection 3 What
lock
s is the process from the previous example holding? Make sure you run ROLLBACK TRAN in Connection 1 to clean up your transaction. Collecting Information See Module 2 for more about how to gather this information using various tools. Recognizing B
lock
ing Problems How to Recognize B
lock
ing Problems
User
s complain about poor performance at a certain time of day, or after a certain number of
user
s connect. SELECT * FROM sysprocesses or sp_who2 shows non-zero values in the b
lock
ed
or BlkBy column. More severe b
lock
ing incidents will have long b
lock
ing chains or large sysprocesses.waittime values for b
lock
ed
spids. Possibl
FlexGraphics_V_1.79_D4-XE10.2_Downloadly.ir
Version 1.7 ----------- - ADD: Delphi/CBuilder 10.2 Tokyo now support
ed
. - ADD: Delphi/CBuilder 10.1 Berlin now support
ed
. - ADD: Delphi/CBuilder 10 Seattle now support
ed
. - ADD: Delphi/CBuilder XE8 now support
ed
. - ADD: Delphi/CBuilder XE7 now support
ed
. - ADD: Delphi/CBuilder XE6 now support
ed
. - ADD: Delphi/CBuilder XE5 now support
ed
. - ADD: Delphi/CBuilder XE4 now support
ed
. - ADD: Delphi/CBuilder XE3 now support
ed
. - ADD: Delphi/CBuilder XE2 now support
ed
. - ADD: Delphi/CBuilder XE now support
ed
. - ADD: Delphi/CBuilder 2010 now support
ed
. - ADD: Delphi/CBuilder 2009 now support
ed
. - ADD: New demo project FlexCADImport. - FIX: The height of the TFlexRegularPolygon object incorrectly changes with its rotation. - FIX: Add
ed
division by zero protect in method TFlexControl.MovePathSegment. - FIX: The background beyond docuemnt wasn't fill
ed
when TFlexPanel.DocClipping=True. - FIX: In "Windows ClearType" font rendering mode (OS Windows mode) the "garbage" pixels can appear from the right and from the bottom sides of the paint
ed
rectangle of the TFlexText object. - FIX: The result rectangle incorrectly calculat
ed
in the TFlexText.GetRefreshRect method. - FIX: Add
ed
FPaintCache.rcPaint cleanup in the TFlexPanel.WMPaint method. Now it is possible to define is the drawing take place via WMPaint or via the PaintTo direct call (if rcPaint contain non-empty rectangle then WMPaint in progress). - FIX: The TFlexPanel.FPaintCache field mov
ed
in the protect
ed
class section. Add
ed
rcPaint field in FPaintCache that represents drawing rectangle. - ADD: In the text prcise mode (TFlexText.Precise=True) takes into account the rotation angle (TFlexText.Angle). - FIX: Remov
ed
FG_NEWTEXTROTATE directive (the TFlexText Precise mode should be us
ed
instead). - FIX: The TFlexRegularPolygon object clones incorrectly draw
ed
in case when TFlexRegularPolygon have alternative brush (gradient, texture). - ADD: Add TFlexPanel.InvalidateControl virtual method which calls from TFle
基础和管理
17,377
社区成员
95,127
社区内容
发帖
与我相关
我的任务
基础和管理
Oracle 基础和管理
复制链接
扫一扫
分享
社区描述
Oracle 基础和管理
社区管理员
加入社区
获取链接或二维码
近7日
近30日
至今
加载中
查看更多榜单
社区公告
暂无公告
试试用AI创作助手写篇文章吧
+ 用AI写文章