查询数据库中表被锁的情况

SQL Server:

1.      查看当前被锁对象

select  dtl.request_session_id spid,

             object_name(dtl.resource_associated_entity_id) table_name,

            db.name db_name,

            dtl.request_mode,

            dtl.request_type,

            dtl.request_status,

            dtl.request_owner_type

from sys.dm_tran_locks dtl,sys.databases db

where dtl.resource_database_id=db.database_id

and dtl.resource_type='OBJECT'

and dtl.request_mode='IX';

或者执行 sp_who  查看mode=’IX’.

2  查看锁的语句，需要创建存储过程，并执行SP_WHO_LOCK（推荐）

create procedure [dbo].[sp_who_lock]  
AS  
 
BEGIN  
 
declare @spid int,  
  @bl int,  
  @intTransactionCountOnEntry int,  
  @intRowcount int,  
  @intCountProperties int,  
  @intCounter int  
 
create table #tmp_lock_who   
( id int identity(1,1),  
  spid smallint,  
  bl SMALLINT )  
 
IF @@ERROR<>0 RETURN @@ERROR  
 
insert into #tmp_lock_who(spid,bl)   
select 0, blocked  
from (select * from master.dbo.sysprocesses where  blocked>0 ) a  
where not EXISTS (select * from (select * from master.dbo.sysprocesses where  blocked>0) b where spid=a.blocked)  
union   
select spid,blocked from master.dbo.sysprocesses where  blocked>0  
 
IF @@ERROR<>0 RETURN @@ERROR  
 
select @intCountProperties = Count(*), @intCounter = 1  
from #tmp_lock_who  
 
IF @@ERROR<>0 RETURN @@ERROR  
 
if @intCountProperties = 0  
select 'No Locking！' as MESSAGE  
 
while @intCounter <= @intCountProperties  
BEGIN  
 select @spid = spid, @bl = bl  
 from #tmp_lock_who   
 where Id = @intCounter  
 begin  
  if @spid = 0  
   select 'The locking session is: '+ CAST(@bl AS VARCHAR(10)) + ', the sql is below.'  
  else  
   select 'Session SPID: '+ CAST(@spid AS VARCHAR(10))+ ' is locked by Session SPID: '+ CAST(@bl AS VARCHAR(10)) +',the sql is below.'  
    
  DBCC INPUTBUFFER (@bl )  
 end  
 set @intCounter = @intCounter + 1  
END  
 
drop table #tmp_lock_who  
return 0  
   
END  
 
 
  Oracle：

1. 当前被锁对象

select o.object_type,o.owner||'.'||o.object_nameobject,s.sid,s.serial#,p.spid,s.username,s.osuser,s.program,l.locked_mode

from v$locked_object l,dba_objects o,v$session s,v$process p

where l.object_id = o.object_id

and s.sid=l.session_id

and s.paddr=p.addr

2. 持有锁的会话与被等待锁的SQL

        SELECT bs.username "Blocking User", bs.username "DB User",

                  ws.username "Waiting User", bs.sid "SID", ws.sid"WSID",

                  bs.serial# "Serial#", bs.sql_address "address",

                  bs.sql_hash_value "Sql hash", bs.program "Blocking App",

                  ws.program "Waiting App", bs.machine "Blocking Machine",

                  ws.machine "Waiting Machine", bs.osuser "Blocking OS User",

                  ws.osuser "Waiting OS User", bs.serial# "Serial#",

                  ws.serial# "WSerial#",

                  DECODE (wk.TYPE,

                           'MR', 'Media Recovery',

                           'RT', 'Redo Thread',

                           'UN', 'USER Name',

                           'TX', 'Transaction',

                           'TM', 'DML',

                           'UL', 'PL/SQL USER LOCK',

                           'DX', 'Distributed Xaction',

                           'CF', 'Control FILE',

                           'IS', 'Instance State',

                           'FS', 'FILE SET',

                           'IR', 'Instance Recovery',

                           'ST', 'Disk SPACE Transaction',

                           'TS', 'Temp Segment',

                           'IV', 'Library Cache Invalidation',

                           'LS', 'LOG START OR Switch',

                           'RW', 'ROW Wait',

                           'SQ', 'Sequence Number',

                           'TE', 'Extend TABLE',

                           'TT', 'Temp TABLE',

                           wk.TYPE

                           ) lock_type,

                  DECODE (hk.lmode,

                           0, 'None',

                           1, 'NULL',

                           2, 'ROW-S (SS)',

                           3, 'ROW-X (SX)',

                           4, 'SHARE',

                           5, 'S/ROW-X (SSX)',

                           6, 'EXCLUSIVE',

                           TO_CHAR (hk.lmode)

                          ) mode_held,

                  DECODE (wk.request,

                           0, 'None',

                           1, 'NULL',

                           2, 'ROW-S (SS)',

                           3, 'ROW-X (SX)',

                           4, 'SHARE',

                           5, 'S/ROW-X (SSX)',

                           6, 'EXCLUSIVE',

                           TO_CHAR (wk.request)

                           ) mode_requested,

                  TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,

                  DECODE (hk.BLOCK,

                           0, 'NOT Blocking', /* Not blocking any other processes */

                           1, 'Blocking', /* This lock blocks other processes */

                           2, 'Global', /* This lock is global, so we can't tell */

                           TO_CHAR (hk.BLOCK)

                           ) blocking_others

        FROM v$lock hk, v$session bs, v$lock wk, v$session ws

        WHERE hk.BLOCK = 1

        AND hk.lmode != 0

        AND hk.lmode != 1

        AND wk.request != 0

        AND wk.TYPE(+) = hk.TYPE

        AND wk.id1(+) = hk.id1

        AND wk.id2(+) = hk.id2

        AND hk.sid = bs.sid(+)

        AND wk.sid = ws.sid(+)

        AND (bs.username IS NOT NULL)

        AND (bs.username <> 'SYSTEM')

        AND (bs.username <> 'SYS')

        ORDER BY 1;