lock基础

about lock:

2 RS: row share                  
3 RX: row exclusive              
4 S: share                       
5 SRX: share row exclusive       
6 X: exclusive                   

2级锁有：Select for update，Lock For Update，Lock Row Share select for update当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select for update操作。
3级锁有：Insert，Update，Delete，Lock Row Exclusive 没有commit之前插入同样的一条记录会没有反应， 因为后一个3级的锁会一直等待上一个3级的锁，我们必须释放掉上一个才能继续工作。
4级锁有：Create Index， Lock Share locked_mode为2、3、4级锁，不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。00054, 00000, "resource busy and acquire with NOWAIT specified" // *Cause: Resource interested is busy. //*Action: Retry if necessary.
5级锁有：Lock Share Row Exclusive 具体来讲有主外键约束时update / delete ... ； 可能会产生4，5的锁。
6级锁有：Alter table, Drop table, Drop Index,Truncate table, Lock Exclusive

lock table lock_test in row share mode; 2 RS

lock table lock_test in row exclusive mode; 3 RX
lock table lock_test in share mode; 4 S
lock table lock_test in share row exclusive mode; 5 SRX
lock table lock_test in exclusive mode; 6 X

v$locked_object
v$lock
v$session where event like'%TX%';
v$session_wait

alter system kill session '513,12093';踢掉等待session会话
select spid from v$process p,v$session s where s.paddr = p.addr and s.sid=507;根据sid找出相应的操作系统进程
kill -9 12068

select * from dba_objects a,v$locked_object b  where a.object_id = b.OBJECT_ID;

select s.username,o.object_name,s.machine,s.sid,s.serial#,k.type
    from v$locked_object l,dba_objects o ,v$session s,v$lock k
    where l.object_id  =  o.object_id and l.session_id=s.sid and k.sid=s.sid

select * from v$sqltext a,v$session b where b.sql_id=a.sql_id and b.sid = 507