關於Oracle鎖定

導致鎖定的幾種情況 :
1. users who do not regularly commit changes
2. developers who code unnecessarily long transactions
3. developers use unnecessarily high locking levels.
 
鎖死的解決:
1.Oracle 自動檢測deadlock 並通過回滾檢測出的deadlock 的語句來解決鎖死.
2.查出DeadLock ,执行以下语句将该进程进行清除:

alter system kill session 'sid, serial#';

 

 

几個SQL語句:
1.跳過被鎖的那筆資料:

select * from table t  for update NOWAIT SKIP LOCKED;

2.查看哪個Table的資料在被鎖定中:

select t2.sid, t2.serial#, t4.spid, t2.username, t3.object_name
  from v$locked_object t1, v$session t2, all_objects t3, v$process t4
 where t1.session_id = t2.sid
   and t1.object_id = t3.object_id
   and t2.paddr = t4.addr
 order by t2.logon_time;

 3.查看引起鎖定的sql語句(insert,update,select for update 等等).

select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */
 a.username,
 a.machine,
 a.sid,
 a.serial#,
 a.last_call_et "Seconds",
 b.id1,
 c.sql_text "SQL" 
  from v$session a, v$lock b, v$sqltext c
 where a.username is not null
   and a.lockwait = b.kaddr
   and c.hash_value = a.sql_hash_value;