SQLServer和Oracel中的锁和死锁.docx
- 文档编号:3975684
- 上传时间:2022-11-26
- 格式:DOCX
- 页数:9
- 大小:20.16KB
SQLServer和Oracel中的锁和死锁.docx
《SQLServer和Oracel中的锁和死锁.docx》由会员分享,可在线阅读,更多相关《SQLServer和Oracel中的锁和死锁.docx(9页珍藏版)》请在冰豆网上搜索。
SQLServer和Oracel中的锁和死锁
1、锁的作用
ORACLE:
锁是一种机制,当某个数据库对象正被其他进程或用户修改时,这种机制可以保护它不被修改。
为了防止对象被两个不同的用户同时修改,数据库系统创建了锁这种复杂的数据结构,来实现锁定机制,这种机制使用在oracle中称为排队的队列结构,以串行的方式执行锁。
锁的作用如下:
1) 他们坚持一致性和完整性,在事务处理期间,数据和对象保证它们的一致性和完整性。
2) 但对象不是立即可用时,它们提供一种队列结构,这种结构允许所有会话都加入到等候对象的队列中,
3) 自动处理锁机制
4) 锁的持续时间等于被提交事务的长度或处理时间。
SQLSERVER:
锁的作用是提供事务的隔离性,保证各个事务不会互相干扰,一个事务不会读取或修改另一个事务正在使用的数据,此外,锁提供的隔离性还保证事务的一致性。
2、锁的模式
ORACLE中的锁模式和描述如下:
2.1锁模式描述和缩写词
缩写词 模式 说明
缩写词
模式
说明
RS(ss)
ROWSHARE
为了更新,每个事务在表中锁定了行,但允许其他事务锁定表中的其他行。
RX
ROWEXCLUSIVE
为了更新,每个事务在表中锁定了行,但不允许其他事务锁定表中的其他行。
S
SHARE
某个事务以一个模式锁定了表,这个模式允许其他事务以SHARE模式锁定这个表,但不允许在这个表中进行任何更新
SRX(ssx)
SHAREROWEXCLUSIVE
在SHAREMODE中,不允许其他事务锁定这个表,且不允许DML语句。
TM
N/A
表级别的锁
TX
N/A
行级别的锁
UUL
N/A
用户定义的锁
XX
EXCLUSIVE
这个表被锁定了,并且不允许其他会话锁定这个表,或者把DML语句提交到这个表
2.2锁模式和DML语句
SQLSERVER中的锁模式和描述如下:
缩写词
模式
说明
S
SHARE共享锁
取得资源共享锁。
SQLSERVER用共享锁执行所以读取操作,共享锁阻止取得独占锁,任何事务要修改共享锁所在页或行的数据时,受到阻止,直到释放所以共享锁为止。
U
UPDATE更新锁
取得资源更新锁。
更新锁锁定用户进程要修改的页。
SQLSERVER找到正确位置和准备插入记录时,将更新锁升级为独占锁。
X
EXCLUSIVE独占锁
独占锁在准备修改数据时向事务提供。
资源的独占锁保证其他事务不会干扰持有独占锁的事务锁定的数据。
在事务结束时释放独占锁,独占锁与其他任何类型的锁都不兼容。
如果对资源持有独占锁,任何其他进程对同一资源的读取或修改请求都要等到独占锁释放,同理,如果当前另一进程持有资源的读取锁定,这独占锁请求要等到资源可用为止。
IS
共享意向锁
表示进程当前持有或想持有低层资源(页或行)的共享锁。
IU
更新意向锁
表示进程当前持有或想持有低层资源(页或行)的更新锁。
IX
独占意向锁
表示进程当前持有或想持有低层资源(页或行)的独占锁。
BU
批量锁
批量复杂使用的批量更新锁。
Range
键范围锁
SQLSERVER中的键范围锁隔离事务之间对数据的修改,使事务每次返回相同的结果集。
即防止“幻影读“。
3、死锁
死锁就是两个进程都在等待对方持有的资源锁,要等对方释放持有的资源锁之后才能继续工作,它们互不相让,坚持到底,实际上,双方都要等到对方完成之后才能继续工作,而双方都完成不了。
Oracle死锁样本:
步骤一:
登陆ORACLESQL*plus之一窗口,执行:
UPDATE HR.JOBS
SETJOB_title ='S.FinanceManager'
WHEREjob_id='FI_MGR'
步骤二:
登陆ORACLESQL*plus之二窗口,执行:
UPDATE HR.JOBS
SETJOB_title ='S.President'
WHEREjob_id='AD_PRES';
步骤三:
重新ORACLESQL*plus之一窗口,执行
UPDATE HR.JOBS
SETJOB_title ='S.President'
WHEREjob_id='AD_PRES';
发现已经无法完成,因为在等待资源释放。
步骤四:
登陆ORACLESQL*plus之二窗口,执行:
UPDATE HR.JOBS
SETJOB_title ='S.FinanceManager'
WHEREjob_id='FI_MGR'
此时出现ORA-00060错误,如下图所示:
发现报出错误,系统检测到死锁,此时打开C:
\oracle\admin\ORADB\udump\
的oradb_ora_5528文件会发现已经记录了死锁deadlock日志,文字如下:
***2008-07-0516:
46:
43.000
***SESSIONID:
(17.16)2008-07-0516:
46:
43.000
DEADLOCKDETECTED
CurrentSQLstatementforthissession:
UPDATE HR.JOBS
SETJOB_title ='S.President'
WHEREjob_id='AD_PRES'
ThefollowingdeadlockisnotanORACLEerror.Itisa
deadlockduetousererrorinthedesignofanapplication
orfromissuingincorrectad-hocSQL.Thefollowing
informationmayaidindeterminingthedeadlock:
Deadlockgraph:
---------Blocker(s)-------- ---------Waiter(s)---------
ResourceName processsessionholdswaits processsessionholdswaits
TX-000a0002-00001904 16 17 X 17 18 X
TX-00010010-00001917 17 18 X 16 17 X
session17:
DID0001-0010-00000003 session18:
DID0001-0011-00000003
session18:
DID0001-0011-00000003 session17:
DID0001-0010-00000003
Rowswaitedon:
Session18:
obj-rowid=00007339-AAAHM5AAFAAAABGAAD
(dictionaryobjn-29497,file-5,block-70,slot-3)
Session17:
obj-rowid=00007339-AAAHM5AAFAAAABGAAA
(dictionaryobjn-29497,file-5,block-70,slot-0)
InformationontheOTHERwaitingsessions:
Session18:
pid=17serial=20audsid=0user:
0/SYS
O/Sinfo:
user:
WANGTM\wangtm,term:
WANGTM,ospid:
5200:
4876,machine:
WORKGROUP\WANGTM
program:
sqlplusw.exe
CurrentSQLStatement:
UPDATE HR.JOBS
SETJOB_title ='S.FinanceManager'
WHEREjob_id='FI_MGR'
EndofinformationonOTHERwaitingsessions.
SQLServer死锁样本:
死锁使事务中止时,SQLServer向客户机返回错误号1205,由于死锁不是逻辑错误,而只是资源争夺问题,因此客户机可以更新提交整个事务,要在应用程序中处理死锁,要在错误处理器中捕获消息1205。
遇到消息1205时,应用程序可以自动重新提交事务,最好不要然用户看到SQLServer返回的死锁错误消息。
我们知道可以通过SP_lock和SP_who监视进程之间的锁争用,但是,一旦出现死锁,一个事务回退,一个事务继续。
此时使用sp_lock已经看不到真正死锁的资源信息(或许能够看许多X类型的锁信息),因为所涉及资源的锁已经释放。
SQLSERVER提供了几个跟踪标志,可以监视出现的死锁。
可以用DBCCTRACEON命令打开跟踪标志,用DBCCTRACEOFF关闭跟踪标志,要然SQLSERVER把死锁跟踪标志的输出写入到错误日志中。
首先要设置DBCCTRACEON(3605),比如:
DBCCTRACEON(3605)
DBCCTRACEON(1204)
这样,一旦出现死锁,将能在错误日志中监视到相关明细信息。
4、锁争用的监测和解决
ORACLE:
ORACLE提供了有用的锁的动态性能视图V$LOCK和V$locked_OBJECT。
动态性能视图V$LOCK包含所有当前由系统和所以连接的会话保持的锁的信息。
如SELECT*fromV$lock返回下面样本:
ADDR KADDR SIDTY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------------------------------------------------------------------
682BE878682BE888 2MR 201 0 4 0 12383 0
682BE82C682BE83C 2MR 11 0 4 0 12383 0
682BE7E0682BE7F0 2MR 10 0 4 0 12383 0
V$locked_OBJECT提供了当前被锁定的对象。
可以查出该对象由什么锁模式锁定的。
我们可以如下查询语句查找死锁的进程:
查询1:
SELECT
ss.username,lo.OBJECT_ID,lo.SESSION_ID,ss.SERIAL#,
lo.ORACLE_USERNAME,lo.OS_USER_NAME,lo.PROCESS
FROMV$LOCKED_OBJECTlo,V$SESSIONss
WHERElo.SESSION_ID=ss.SID;
查询2:
SELECTDISTINCT
'BLOCKER('||LB.SID||':
'||sb.username||')-SQL:
'||qb.SQL_textBLOCKERS,
'WAITER('||lw.SID||':
'||sw.username||')-SQL:
'||qw.SQL_textWAITERS
FROMV$lockLB,V$sessionSB,V$lockLW,v$sessionSW,v$sqlQB,V$sqlQW
WHERELB.SID=SB.SID
ANDLW.SID=SW.SID
ANDSB.PREV_sql_addr=QB.ADDRESS
ANDSW.SQL_ADDRESS=QW.ADDRESS
ANDLB.ID1=LW.ID2
ANDSW.LOCKWAITISNOTNULL
查询3:
SELECTDISTINCT
'BLOCKER('||BW.HOLDING_SESSION||':
'||SB.username||')-SQL:
'||BQ.SQL_textBLOCKERS,
'WAITER('||BW.WAITING_SESSION||':
'||sw.username||')-SQL:
'||SQ.SQL_textWAITERS
FROMDBA_waitersBW,V$SESSIONSB,v$sessionSW,v$sqlareaBQ,V$sqlareaSQ
WHEREBW.HOLDING_SESSION=SB.SID
ANDBW.WAITING_SESSION=SW.SID
ANDSB.PREV_SQL_addr=BQ.ADDRESS
ANDSW.SQL_ADDRESS=SQ.ADDRESS
查询3执行后,返回如下所示监视信息。
BLOCKERS
--------------------------------------------------------------------------------
WAITERS
--------------------------------------------------------------------------------
BLOCKER(12:
SYS)-SQL:
SELECTDISTINCT 'BLOCKER('||BW.HOLDING_SESSION||':
'||
SB.username||')-SQL:
'||BQ.SQL_textBLOCKERS, 'WAITER('||BW.WAITING_SES
SION||':
'||sw.username||')-SQL:
'||SQ.SQL_textWAITERS FROMDBA_waitersB
W,V$SESSIONSB,v$sessionSW,v$sqlareaBQ,V$sqlareaSQ WHEREBW.HOLDING_SESS
ION=SB.SID ANDBW.WAITING_SESSION=SW.SID ANDSB.PREV_SQL_addr=BQ.AD
DRESS ANDSW.SQL_ADDRESS=SQ.ADDRESS
WAITER(13:
SYS)-SQL:
updatehr.jobssetjob_title=job_title||'abc'wherej
ob_id='ST_MAN'
当我们查询到死锁的进程的信息可以使用KILL命令终止这个产生死锁的ORACLE会话进程:
ALTERSYSTEMKILLSESSION'查出的SID,查出的SERIAL#';
其中SID即V$LOCKED_OBJECT的SESSION_ID,serial#为V$session的serial#。
再杀操作系统进程:
KILL SPID或ORAKILL刚才查出的SID刚才查出的SID
SQLSERVER:
要监视SQLSERVER的锁活动,有如下几种常用的方法:
1) 使用SP_LOCK和SP_WHO存储过程。
2) 直接查询syslockinfo表。
3) 使用SQLSERVERManagementstudio的活动监视器浏览锁活动。
4)使用SQLProfiler浏览锁活动。
让我们看看SQLSERVERManagementstudio的活动监视器浏览锁的页面,如下图:
点击查看大图
这个列表返回的信息和使用SP_lock返回的信息是一致的,比如
EXECSP_lock
GO
这个命令的输出样本如下:
spid dbid ObjId IndId TypeResource Mode Status
-----------------------------------------------------------------
52 5 0 0 DB S GRANT
52 5 148195578 1 PAG 1:
489 IX GRANT
52 5 148195578 0 TAB IX GRANT
52 5 148195578 1 KEY (07005a186c43) X GRANT
54 1 1115151018 0 TAB IS GRANT
54 5 0 0 DB S GRANT
这个样本提供了下列的信息:
Spid是事务的进程ID。
dbid 是持有锁的的数据库ID。
ObjID是持有锁的资源ID。
IndId 是持有锁的表索引ID。
Type是持有资源的锁类型。
Resource 是持有锁的资源内部名称,这个信息来自syslockinfo系统表。
Mode是事务请求的锁模式,锁类型请参考本文的锁模式一节
STATUS是请求的当前状态。
取值为GRANT/WAIT/GNVRT .
我们发现上面的样本信息中出现了很多X类型的锁,如果该X锁引起了阻塞或死锁等现象,我们可以使用KILL命令解决解决锁争用,Sqlserver通过使用KILL命令终止锁进程。
比如:
KILL 52
5、避免死锁
ORACLE:
1) 避免应用不运行长事务。
2) 经常提交以避免长时间锁定行。
3) 避免使用LOCK命令锁定表。
4) 在非高峰期间执行DDL操作。
5) 在非高峰期间执行长时间运行的查询或事务。
6) 确保开发人员使用限制最少的锁模式比不过仔细设计防止锁争用的事务,
7) 监控阻塞其他锁的锁并调查为什么这些锁正被保持。
8) 确定为什么阻塞的锁的被保持了很长时间并尽量阻止它们。
9) 监视死锁发生的频率并解决它们。
10) 当死锁发生通过回滚事务rollback或者终止会话来解决它。
SQLSERVER:
SQLSERVER能够自动探测和处理死锁,但应用程序应尽可能的避免,遵循如下原则:
1) 从表中访问数据的顺序要一致,避免循环死锁。
2) 减少使用holdlock或使用可重复读与可序列化锁隔离级的查询,从而避免转换死锁。
3) 恰当选择事务隔离级别。
选择低事务隔离级可以减少死锁。
来源:
网络王铁民编辑:
联动北方技术论坛
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLServer Oracel 中的 死锁
![提示](https://static.bdocx.com/images/bang_tan.gif)