DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁，TX锁只有排他锁X。

数据库编程，特别注意多进程，或者不同程序对同一表的锁。如采用select * from for update (nowait)等方式，可能由于另一个程序处理事务太长不commmit，导致申请锁失败。

如：otl error! sql:select xx from xx for update nowait ,var:, errmsg:ORA-00054:资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效

SQL语句 表锁定模 允许的表锁定模式
Select * from …… RS RS、RX、S、SRX、X
Insert into …… RX RS、RX
Update …… RX RS、RX
Delete from …… RX RS、RX
Select * from for update RS RS、RX、S、SRX
lock table in row share mode RS RS、RX、S、SRX
lock table in row exclusive mode RX RS、RX
lock table in share mode S RS、S
lock table in share row exclusive mode SRX RS
lock table in exclusive mode X RS

常用的锁操作：

1.查询数据库中的锁 select * from v$lock; select * from v$lock where block=1;

2.查询被锁的对象 select * from v$locked_object;

3.查询阻塞 查被阻塞的会话 select * from v$lock where lmode=0 and type in (‘TM’,’TX’); 查阻塞别的会话锁 select * from v$lock where lmode>0 and type in (‘TM’,’TX’);

4.查询数据库正在等待锁的进程 select * from v$session where lockwait is not null;

5.查询会话之间锁等待的关系 select a.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from v$lock a,v$lock b where a.id1=b.id1 and a.id2=b.id2 and a.block=1 and b.block=0;

6.查询锁等待事件 select * from v$session_wait where event=’enqueue’;

7.查找锁住的表和解锁 select b.owner TABLEOWNER, b.object_name TABLENAME, c.OSUSER LOCKBY, c.USERNAME LOGINID, c.sid SID, c.SERIAL# SERIAL from v$locked_object a,dba_objects b, v$session c where b.object_id = a.object_id AND a.SESSION_ID =c.sid;

–通过SID, SERIAL解锁 –alter system kill session ‘SID, SERIAL’;