SQL Server和Oracel中的锁和死锁(1)(3)

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  

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'   

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