關於Oracle鎖定

wuhuizhong

導致鎖定的幾種情況 :
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;