数据库锁介绍： http://www.tuohang.net/article/243490.html
一、查询oracle锁定的表：
1、锁相关表
SELECT * FROM v$lock;列：ADDR:锁定状态对象地址;KADDR:锁地址;SID :会话id;ID1:锁标识符#1;ID2:锁标识符#2;LMODE:会话持有的锁模式（0~6）;REQUEST:进程请求的锁模式（0~6）;
CTIME:当前模式的时间;BLOCK:为1代表阻碍者，表示正在阻碍其它会话; SELECT * FROM v$locked_object; SELECT * FROM v$session; SELECT * FROM v$session_wait; SELECT * FROM v$sqlarea; SELECT * FROM v$process ; SELECT * FROM all_objects
2、常用语句
1、查出锁定object的session的信息以及被锁定的object名
|
1 2 3 4 5 |
SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, l.os_user_name, s.machine, s.terminal, o.object_name, s.logon_time FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid ORDER BY sid, s.serial#; |
2、查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句，比上面那段多出sql_text和action
|
1 2 3 4 5 |
SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s. user #, l.os_user_name, s.machine, s.terminal, a.sql_text, a. action FROM v$sqlarea a, v$session s, v$locked_object l WHERE l.session_id = s.sid AND s.prev_sql_addr = a.address ORDER BY sid, s.serial#; |
3、查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
|
1 2 3 4 5 |
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine, s.terminal, s.logon_time, l.TYPE FROM v$session s, v$lock l WHERE s.sid = l.sid AND s.username IS NOT NULL ORDER BY sid; |
4、这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。
|
1 2 3 4 5 6 |
SELECT /*+ rule */ s.username, DECODE(l.TYPE, 'TM' , 'TABLE LOCK' , 'TX' , 'ROW LOCK' , NULL ) LOCK_LEVEL, o.owner, o.object_name, o.object_type, s.sid, s.serial#, s.terminal, s.machine, s.program, s.osuser FROM v$session s, v$lock l, dba_objects o WHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username IS NOT NULL ; |
5、如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待,以下的语句可以查询到谁锁了表，而谁在等待。
以下查询结果是一个树状结构，如果有子节点，则表示有等待发生。如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN
|
1 2 3 4 5 |
SELECT LPAD( ' ' , DECODE(l.xidusn, 0, 3, 0)) || l.oracle_username user_name, o.owner, o.object_name, o.object_type, s.sid, s.serial# FROM v$locked_object l, dba_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid ORDER BY o.object_id, xidusn DESC |
3、kill session语句：
alter system kill session 'sid,serial#';
alter system kill session'494,7355';
二、一个有用查找脚本：
1、找到某表的锁 所属的sid， alter system kill session 'sid,serial#' ;即可
|
1 2 3 4 5 |
select v$lock.sid, decode(v$lock.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 Invalida-tion' , 'LS' , 'Log Start or Switch' , 'RW' , 'Row Wait' , 'SQ' , 'Sequence Number' , 'TE' , 'Extend Table' , 'TT' , 'Temp Table' , 'Unknown' ) LockType, rtrim(owner) || '.' || object_name object_name, decode(lmode, 0, 'None' , 1, 'Null' , 2, 'Row-S' , 3, 'Row-X' , 4, 'Share' , 5, 'S/Row-X' , 6, 'Exclusive' , 'Unknown' ) LockMode, decode(request, 0, 'None' , 1, 'Null' , 2, 'Row-S' , 3, 'Row-X' , 4, 'Share' , 5, 'S/Row-X' , 6, 'Exclusive' , 'Unknown' ) RequestMode, ctime, block b from v$lock, all_objects where sid > 6 and v$lock.id1 = all_objects.object_id; |
2、查出被lock 的对象，然后 alter system kill session 'sid,serial#' ;
|
1 2 3 |
select object_id, session_id, serial#, oracle_username, os_user_name, s.process from v$locked_object a, v$session s where a.session_id = s.sid; |
三、LOCK TABLE
1、语法：
|
1 2 |
LOCK TABLE table_1 [,table_2, ..., table_n] IN lock_mode MODE NOWAIT |
2、变量：
table_1,...,table_n: 一系列你想通过使用LOCK TABLE语句锁住的数据库表。
lock_mode: 对于某一数据库表你要设定的锁定模式。你可以从如下的锁定模式中任选一个。
EXCLUSIVE SHARE ROW EXCLUSIVE SHARE SHARE UPDATE ROW SHARE ROW EXCLUSIVENOWAIT: Oracle will not wait to lock the given Table(s), if the Table(s) is(are) not available
3、例子：
|
1 2 3 4 5 6 7 |
LOCK TABLE loan IN SHARE MODE ; LOCK TABLE region IN EXCLUSIVE MODE NOWAIT; LOCK TABLE acct IN SHARE UPDATE MODE; LOCK TABLE bank IN ROW EXCLUSIVE MODE NOWAIT; LOCK TABLE user IN SHARE ROW EXCLUSIVE MODE; LOCK TABLE branch IN ROW SHARE MODE NOWAIT; commit |
到此这篇关于Oracle锁（lock）的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：https://www.cnblogs.com/springsnow/p/11492221.html
查看更多关于Oracle中锁（lock）的用法的详细内容...