数据库锁表查找方法

最新推荐文章于 2024-09-26 18:23:37 发布

oOo右右

最新推荐文章于 2024-09-26 18:23:37 发布

阅读量1.6k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/JOJOY_tester/article/details/53310078

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

什么是锁：

锁是数据库里面串行机制的实现，是为了保证数据的一致性，而作的一个操作。例如去取钱时，数据库会规定一次只能操作一次，就是加了锁，一次超过两笔的话就不能操作。为了保证数据一致性就会上个锁，不允许这个步骤操作时另外一个步骤也操作。数据库里面还有其他锁。。。。

1、查出 oracle 数据库中的锁等待

select a.username, a.sid, a.serial#, b.id1
  from v$session a, v$lock b
 where a.lockwait = b.kaddr;

2.找出等待事件的原因

select event,p1,p2,sid from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';

3.找出锁住的进程

select object_id,session_id,locked_mode from v$locked_object;

4.找出锁住的进程的操作系统进程

select spid from v$process where addr=(select paddr from v$session where sid=19);（这个19表示被锁定的SID）

5.在操作系统上删除进程

orakill eoffice 2768（第一个参数据是数据库的SID  第二个参数是进程ID，这个数字就是上面取到的进程ID，

下面是例子：

SQL> select username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object);

找出了5个等待锁：
1.USERNAME

------------------------------------------------------------
LOCKWAIT                         STATUS
-------------------------------- ----------------
MACHINE
--------------------------------------------------------------------------------
PROGRAM
--------------------------------------------------------------------------------
CLPM_FS
                                 INACTIVE
W112PC02VM02
JDBC Thin Client

2.USERNAME

------------------------------------------------------------
LOCKWAIT                         STATUS
-------------------------------- ----------------
MACHINE
--------------------------------------------------------------------------------
PROGRAM
--------------------------------------------------------------------------------
CLPM_FS
                                 INACTIVE
W112PC01VM02
JDBC Thin Client

3.USERNAME

------------------------------------------------------------
LOCKWAIT                         STATUS
-------------------------------- ----------------
MACHINE
--------------------------------------------------------------------------------
PROGRAM
--------------------------------------------------------------------------------
CLPM_FS
C00000017811B2C8                 ACTIVE
W112PC03VM02
JDBC Thin Client

4.USERNAME

------------------------------------------------------------
LOCKWAIT                         STATUS
-------------------------------- ----------------
MACHINE
--------------------------------------------------------------------------------
PROGRAM
--------------------------------------------------------------------------------
CLPM_FS
                                 INACTIVE
W112PC02VM02
JDBC Thin Client

5.USERNAME

------------------------------------------------------------
LOCKWAIT                         STATUS
-------------------------------- ----------------
MACHINE
--------------------------------------------------------------------------------
PROGRAM
--------------------------------------------------------------------------------
CLPM_FS
C00000017810F0F8                 ACTIVE
W112PC03VM02
JDBC Thin Client

6.USERNAME

------------------------------------------------------------
LOCKWAIT                         STATUS
-------------------------------- ----------------
MACHINE
--------------------------------------------------------------------------------
PROGRAM
--------------------------------------------------------------------------------
CLPM_FS
C000000178107858                 ACTIVE
W112PC03VM02
JDBC Thin Client

7.USERNAME

------------------------------------------------------------
LOCKWAIT                         STATUS
-------------------------------- ----------------
MACHINE
--------------------------------------------------------------------------------
PROGRAM
--------------------------------------------------------------------------------
CLPM_FS
                                 INACTIVE
W112PC03VM02
JDBC Thin Client

8.USERNAME

------------------------------------------------------------
LOCKWAIT                         STATUS
-------------------------------- ----------------
MACHINE
--------------------------------------------------------------------------------
PROGRAM
--------------------------------------------------------------------------------
CLPM_FS
                                 INACTIVE
W112PC01VM02
JDBC Thin Client

9.USERNAME

------------------------------------------------------------
LOCKWAIT                         STATUS
-------------------------------- ----------------
MACHINE
--------------------------------------------------------------------------------
PROGRAM
--------------------------------------------------------------------------------
CLPM_FS
C00000017811ABC8                 ACTIVE
W112PC03VM02
JDBC Thin Client

10.USERNAME

------------------------------------------------------------
LOCKWAIT                         STATUS
-------------------------------- ----------------
MACHINE
--------------------------------------------------------------------------------
PROGRAM
--------------------------------------------------------------------------------
CLPM_FS
C000000178116710                 ACTIVE
W112PC03VM02
JDBC Thin Client

11 rows selected.

SQL> select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object));

SQL_TEXT
--------------------------------------------------------------------------------
   update Fctrn_Fnds_AccInf    set Grp_Innr_Dealg_InsID = :1   where Grp_Innr_De
alg_InsID =  :2  and(        Ctr_ID = :3   )

update openframework_sequence set value=15001 where seq_key='20150919123228' and
 roolingkey='NA' and value=10001


select value from openframework_sequence where seq_key='20150919123228' and rool
ingkey='NA'