关于SQL Server和Oracel中的锁和死锁的讲解

不经意间我们又来到了oracle系统文章的学习，在众多学习中，我们的文章也许不起眼，但是想必大家都有很多问题吧，所以重要的下面我们就来讲解一下，大家一定要认真看奥！！
 
【51CTO.com独家特稿】锁是数据库处理并发事务的一种基本机制，而死锁现象是数据库应用中经常要遇到的现象，本文就SQL SERVE和Oracle常出现的锁和死锁的检测和解决方法进行了全面比较。
1、锁的作用

ORACLE ：
锁是一种机制，当某个数据库对象正被其他进程或用户修改时，这种机制可以保护它不被修改。
为了防止对象被两个不同的用户同时修改，数据库系统创建了锁这种复杂的数据结构，来实现锁定机制，这种机制使用在oracle中称为排队的队列结构，以串行的方式执行锁。
锁的作用如下：
1） 他们坚持一致性和完整性，在事务处理期间，数据和对象保证它们的一致性和完整性。
2） 但对象不是立即可用时，它们提供一种队列结构，这种结构允许所有会话都加入到等候对象的队列中，
3） 自动处理锁机制
4） 锁的持续时间等于被提交事务的长度或处理时间。
SQL Server ：
锁的作用是提供事务的隔离性，保证各个事务不会互相干扰，一个事务不会读取或修改另一个事务正在使用的数据，此外，锁提供的隔离性还保证事务的一致性。
2 、锁的模式
ORACLE中的锁模式和描述如下：
2.1 锁模式描述和缩写词
缩写词 模式 说明

2.2 锁模式和DML语句
SQL SERVER 中的锁模式和描述如下：

3、死锁

死锁就是两个进程都在等待对方持有的资源锁，要等对方释放持有的资源锁之后才能继续工作，它们互不相让，坚持到底，实际上，双方都要等到对方完成之后才能继续工作，而双方都完成不了。
Oracle死锁样本：
步骤一：
登陆ORACLE SQL *plus 之一窗口，执行：
UPDATE  HR.JOBS
SET JOB_title  = 'S.Finance Manager'
WHERE job_id = 'FI_MGR'
步骤二：
登陆ORACLE SQL *plus 之二窗口，执行：
UPDATE  HR.JOBS
SET JOB_title  = 'S.President'
WHERE job_id = 'AD_PRES';
步骤三：
重新ORACLE SQL *plus 之一窗口，执行
UPDATE  HR.JOBS
SET JOB_title  = 'S.President'
WHERE job_id = 'AD_PRES';
发现已经无法完成，因为在等待资源释放。
步骤四：
登陆ORACLE SQL *plus 之二窗口，执行：
UPDATE  HR.JOBS
SET JOB_title  = 'S.Finance Manager'
WHERE job_id = 'FI_MGR'
此时出现ORA-00060错误,如下图所示：

  发现报出错误，系统检测到死锁，此时打开C:\\oracle\\admin\\ORADB\\udump\\oracle教学视频
的oradb_ora_5528文件会发现已经记录了死锁deadlock日志，文字如下：

*** 2008-07-05 16:46:43.000
*** SESSION ID:(17.16) 2008-07-05 16:46:43.000
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE  HR.JOBS
SET JOB_title  = 'S.President'
WHERE job_id = 'AD_PRES'
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-000a0002-00001904        16      17     X             17      18           X
TX-00010010-00001917        17      18     X             16      17           X
session 17: DID 0001-0010-00000003 session 18: DID 0001-0011-00000003
session 18: DID 0001-0011-00000003 session 17: DID 0001-0010-00000003
Rows waited on:
Session 18: obj - rowid = 00007339 - AAAHM5AAFAAAABGAAD
  (dictionary objn - 29497, file - 5, block - 70, slot - 3)
Session 17: obj - rowid = 00007339 - AAAHM5AAFAAAABGAAA
  (dictionary objn - 29497, file - 5, block - 70, slot - 0)
Information on the OTHER waiting sessions:
Session 18:
  pid=17 seRIAl=20 audsid=0 user: 0/SYS
  O/S info: user: WANGTM\\wangtm, term: WANGTM, ospid: 5200:4876, machine: WORKGROUP\\WANGTM
            program: sqlplusw.exe
  Current SQL Statement:
  UPDATE  HR.JOBS
SET JOB_title  = 'S.Finance Manager'
WHERE job_id = 'FI_MGR' 
End of information on OTHER waiting sessions.   

甲骨文公司产品主要有以下几类：^[2]1．服务器及工具（主要竞争对手：IBM、微软）数据服务器：2013年最新版本Oracle 12C。应用服务器：Oracle Application Server。开发工具：OracleJDeveloper，Oracle Designer，Oracle Developer，等等。企业应用软件（主要竞争对手：德国SAP公司）企业资源计划(ERP)软件。已有10年以上的历史。2005年，并购了开发企业软件的仁科软件公司（PeopleSoft）以增强在这方面的竞争力。客户关系管理(CRM)软件。自1998年开始研发这种软件。2005年，并购了开发客户关系管理软件的希柏软件公司（Siebel）。^[2帮助中国软件企业在快速增长的经济大潮中取得成功，促进中国软件业的发展，同时也为中国的广大用户提供性价比高、可靠、安全的企业软件，为他们的业务增长作出贡献。


SQL Server死锁样本：
死锁使事务中止时，SQL Server向客户机返回错误号1205，由于死锁不是逻辑错误，而只是资源争夺问题，因此客户机可以更新提交整个事务，要在应用程序中处理死锁，要在错误处理器中捕获消息1205。遇到消息1205时，应用程序可以自动重新提交事务，最好不要然用户看到SQL Server返回的死锁错误消息。
我们知道可以通过SP_lock和SP_who监视进程之间的锁争用，但是，一旦出现死锁，一个事务回退，一个事务继续。此时使用sp_lock已经看不到真正死锁的资源信息(或许能够看许多X类型的锁信息)，因为所涉及资源的锁已经释放。
SQL SERVER 提供了几个跟踪标志，可以监视出现的死锁。可以用DBCC TRACEON命令打开跟踪标志，用DBCC TRACEOFF关闭跟踪标志，要然SQL SERVER把死锁跟踪标志的输出写入到错误日志中。首先要设置DBCC TRACEON(3605)，比如：
 DBCC TRACEON(3605)
 DBCC TRACEON(1204)
这样，一旦出现死锁，将能在错误日志中监视到相关明细信息。
4、锁争用的监测和解决
ORACLE:
ORACLE提供了有用的锁的动态性能视图V$LOCK和V$locked_OBJECT。
动态性能视图V$LOCK包含所有当前由系统和所以连接的会话保持的锁的信息。
如SELECT * from V$lock 返回下面样本：
ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST    CTIME      BLOCK
---------- ----------------- ---------- ---------- -------- -------- ----
682BE878 682BE888   2 MR        201       0      4     0           12383          0
682BE82C 682BE83C  2 MR         11       0       4    0            12383          0
682BE7E0 682BE7F0   2 MR         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 
FROM V$LOCKED_OBJECT lo,V$SESSION ss
WHERE lo.SESSION_ID=ss.SID; 

查询2：

SELECT DISTINCT 
    'BLOCKER('||LB.SID||':'||sb.username||') - SQL :' || qb.SQL_text BLOCKERS,
    'WAITER ('||lw.SID||':'||sw.username||') - SQL :' || qw.SQL_text WAITERS
   FROM V$lock LB,V$session SB ,V$lock LW,v$session SW,v$sql QB,V$sql QW
   WHERE LB.SID = SB.SID
     AND LW.SID = SW.SID
  AND SB.PREV_sql_addr = QB.ADDRESS
  AND SW.SQL_ADDRESS = QW.ADDRESS
  AND LB.ID1 = LW.ID2
  AND SW.LOCKWAIT IS NOT NULL  

 
查询3：

SELECT DISTINCT 
    'BLOCKER('||BW.HOLDING_SESSION||':'||SB.username||') - SQL :' || BQ.SQL_text BLOCKERS,
    'WAITER ('||BW.WAITING_SESSION||':'||sw.username||') - SQL :' || SQ.SQL_text WAITERS
   FROM DBA_waiters BW,V$SESSION SB , v$session SW,v$sqlarea BQ,V$sqlarea SQ
   WHERE BW.HOLDING_SESSION = SB.SID
     AND BW.WAITING_SESSION = SW.SID
  AND SB.PREV_SQL_addr = BQ.ADDRESS
  AND SW.SQL_ADDRESS = SQ.ADDRESS  

查询3执行后，返回如下所示监视信息。

BLOCKERS
--------------------------------------------------------------------------------
WAITERS
--------------------------------------------------------------------------------
BLOCKER(12:SYS) - SQL :SELECT DISTINCT     'BLOCKER('||BW.HOLDING_SESSION||':'||
SB.username||') - SQL :' || BQ.SQL_text BLOCKERS,     'WAITER ('||BW.WAITING_SES
SION||':'||sw.username||') - SQL :' || SQ.SQL_text WAITERS    FROM DBA_waiters B
W,V$SESSION SB , v$session SW,v$sqlarea BQ,V$sqlarea SQ    WHERE BW.HOLDING_SESS
ION = SB.SID      AND BW.WAITING_SESSION = SW.SID   AND SB.PREV_SQL_addr = BQ.AD
DRESS   AND SW.SQL_ADDRESS = SQ.ADDRESS
WAITER (13:SYS) - SQL :update hr.jobs set job_title = job_title || 'abc' where j
ob_id = 'ST_MAN'   

当我们查询到死锁的进程的信息可以使用KILL命令终止这个产生死锁的ORACLE会话进程：oracle数据库
ALTER SYSTEM KILL SESSION '查出的SID,查出的SERIAL#';
 其中SID即V$LOCKED_OBJECT 的SESSION_ID, serial#为V$session的serial#。
 
再杀操作系统进程:
KILL  SPID或ORAKILL 刚才查出的SID 刚才查出的SID
SQL SERVER ：
要监视SQL SERVER 的锁活动，有如下几种常用的方法：
1） 使用SP_LOCK和SP_WHO存储过程。
2） 直接查询syslockinfo表。
3） 使用SQL SERVER Management studio的活动监视器浏览锁活动。
4）使用SQL Profiler浏览锁活动。

让我们看看SQL SERVER Management studio的活动监视器浏览锁的页面，如下图：
 

点击查看大图

这个列表返回的信息和使用SP_lock返回的信息是一致的，比如
EXEC SP_lock
GO
这个命令的输出样本如下：
spid   dbid   ObjId       IndId  Type Resource                         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命令解决解决锁争用，Sql server通过使用KILL 命令终止锁进程。
比如：
KILL  52 
5 、避免死锁
ORACLE ：
1） 避免应用不运行长事务。
2） 经常提交以避免长时间锁定行。
3） 避免使用LOCK命令锁定表。
4） 在非高峰期间执行DDL操作。
5） 在非高峰期间执行长时间运行的查询或事务。
6） 确保开发人员使用限制最少的锁模式比不过仔细设计防止锁争用的事务，
7） 监控阻塞其他锁的锁并调查为什么这些锁正被保持。
8） 确定为什么阻塞的锁的被保持了很长时间并尽量阻止它们。
9） 监视死锁发生的频率并解决它们。
10) 当死锁发生通过回滚事务rollback或者终止会话来解决它。
SQL SERVER ：
SQL SERVER能够自动探测和处理死锁，但应用程序应尽可能的避免，遵循如下原则：
1） 从表中访问数据的顺序要一致，避免循环死锁。
2） 减少使用holdlock或使用可重复读与可序列化锁隔离级的查询，从而避免转换死锁。
3） 恰当选择事务隔离级别。选择低事务隔离级可以减少死锁。
【51CTO.com独家特稿，转载请注明出处和作者】

【相关文章】

• 解决Oracle ERP死锁的方法

• 如何减少SQL Server死锁发生的情况

• 轻松掌握MySQL数据库锁机制的相关原理

【责任编辑：雪花 TEL：（010）68476606】

小主们看完我们的文章，想必一定对我们文章很感兴趣了吧，若大家想了解更多视频课程文章的课程，亲们可以到课课家官网查看。非常真诚地欢迎大家，偶在等你们哟，快来吧！！！