for update什么时候释放锁_经典案例 | Oracle锁引起OOM

什么是OOM？  OOM，全称“Out Of Memory”，来源于java.lang.OutOfMemoryError。 看下关于的官方说明： Thrown when the Java Virtual Machine cannot allocate an object because it is out of memory, and no more memory could be made available by the garbage collector. 意思就是说，当JVM因为没有足够的内存来为对象分配空间并且垃圾回收器也已经没有空间可回收时，就会抛出这个error。 案例分析 本月在处理一个宕机问题时，发现数据库锁同样也会造成内存缓慢漏泄，直到服务器OOM，这里做个记录分享。 环境： Linux操作系统/Oracle数据库 现象： 内存使用率逐渐升高，直到最后CPU因为FullGC使用率也非常高。整个系统虽然没有彻底宕掉，但反应非常慢客观造成系统无法正常使用。 分析： 根据这个现象分析，应该是内存缓慢漏泄。 说明： A区：新建对象放入A区，A区满后进行A区垃圾回收，几次都回收不掉的移到B区，橙色代表A区回收不掉的对象。 B区：存储生命周期比较久的对象，B区满后进行B区垃圾回收，红色代表B区回收不掉的对象 曲线图：垃圾回收前各区容量的变化。 OOM的是指向oracle驱动jar包，进一步分析稳定包中的日志，发现有一个线程长驻在内存中，从重启一直到宕机。 查找堆栈位置， 这个是指向的代码是一句简单的update SQL。 到这里问题就比较奇怪了，一句简单的更新SQL不可能一直执行不完呀，迟迟执行不完怀疑遇到了锁等待。 最终问题确认，客户更新数据使用了for update而没有提交造成整个表被表级共享锁锁定，造成其它的更新语句都处理等待。   总结： 这里又给OOM提供了一种新的可能性。现象就是

• 内在缓慢泄漏

• 漏泄点儿指向oracle的驱动jar包

• 线程中有更新表的SQL一直在执行

一些关于oracle锁及处理方案 查找当前锁：

select s.SID,       s.SERIAL#,       s.MACHINE,       s.TYPE,       l.TYPE,       l.CTIME,       l.BLOCK,       l.REQUEST,       l.LMODE,       decode(l.lmode,              0,              'None',              1,              'Null',              2,              'Row-S (SS)',              3,              'Row-X (SX)',              4,              'Share',              5,              'S/Row-X (SSX)',              6,              'Exclusive',              substr(to_char(l.lmode), 1, 13)) as "Locked Mode",       DECODE(L.TYPE,              'MR',              'File_ID:' || L.ID1,              'TM',              t.NAME,              'TX',              'USN:' || to_char(TRUNC(L.ID1 / 65536)) || 'RWO:' ||              nvl(r.NAME, 'None'),              L.ID1) as LOCK_ID1,       'alter system killsession ''' || s.SID || ',' || s.SERIAL# || ''';' as "Kill"  from v$process p inner join v$session s    on s.PADDR = p.ADDR inner join v$lock l    on l.SID = s.SID  left join sys.obj$ t    on l.ID1 = t.obj#  left join sys.obj$ r    on s.ROW_WAIT_OBJ# = r.obj# where 1 = 1   and l.TYPE != 'MR' order by s.SID

执行结果如下： 这是非常好用的一个sql，可以获取你想要的任何关于锁的信息，执行这句SQL最后一列是对应的kill语句，可以直接copy执行。   一、锁类型： 在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。   当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。 这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。 TM锁包括了SS、SX、S、X等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。   1、TX：行级锁，事务锁

• 在改变数据时必须是排它模式(mode 6)。

• 每一个活动事务都拥有一个锁。它将在事务结束(commit/rollback)时释放。

• 如果一个块包括的列被改变而没有ITL(interestedtransaction list)槽位(entries)，那么session将锁置于共享模式(mode 4)。当session获得块的ITL槽位时释放。

• 当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。

• 指出回滚段和事务表项

按下列项以避免竞争：

• 避免TX－6类型竞争，需要根据应用而定。

• 避免TX－4类型竞争，可以考虑增加对象INITRANS参数值。

2、TM：表级锁

•  数据库执行任何DDL语句时必须是排它模式；例如alter table,drop table。

• 执行像insert,update,delete这类DML语句时处于共享模式。它防止其它session对同一个对象同时执行ddl语句。

• 任何对象拥有正被改变的数据，TM锁都将必须存在。

• 锁指向对象。

在TM队列避免竞争，可以考虑屏蔽对象表级锁，屏蔽表级锁防止对象执行任何ddl语句。   3、ST：空间事务锁

• 每个数据库(非实例)拥有一个ST锁。

• 除了本地管理表空间，在space管理操作(新建或删除extents)时必须是排它模式。

• 对象creation, dropping, extension, 以及truncation都处于这种锁

• 多数公共原因的争夺，是在磁盘排序(并非使用真正的临时表空间)或回滚段扩展或收缩。

按如下项以避免竞争：

• 使用真正的临时表空间(true temporarytablespaces)，利用临时文件。临时段在磁盘排序之后并不创建或删除。

• 使用本地管理表空间。

• 指定回滚段避免动态扩展和收缩，或使用自动undo management。

• 避免应用执行创建或删除数据库对象。

UL：用户定义锁用户可以自定义锁。内容较多并与此节关系不大，略过。   二、锁模式： 1：空 2：行共享锁【是一个共享表锁】(RS：ROW SHARE) 3：行专用锁【用于行的修改】(RX：ROW EXCLUSIVE) 4：共享锁【阻止其他DML操作】(S：SHARE) 5：共享行专用锁【阻止其他事务操作】(SRX：SHARE ROW EXCLUSIVE) 6：专用锁【独立访问使用】(EXCLUSIVE)   锁模式分析： Oracle的TM锁类型

锁模式	锁描述	解释	SQL操作
0	none
1	NULL		Select
2	SS(Row-S) 行级共享锁	其他对象只能查询这些数据行，Lock row share	Select for update、Lock for update
3	SX(Row-X) 行级排它锁	在提交前不允许做DML操作	Insert、Update、Delete
4	S(Share) 共享锁	Lock share	Create index
5	SSX(S/Row-X) 共享行级排它锁	Lock share row exclusive
6	X(Exclusive)排它锁	Lock exclusive	Altertable、Drop able、Drop index、Truncate table

  数字越大锁级别越高, 影响的操作越多。 一般的查询语句如select ... from ... ;是小于2的锁, 有时会在v$locked_object出现。select ... from ... for update; 是2的锁。   当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select...for update操作。 insert / update / delete ... ; 是3的锁。 没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。   创建索引的时候也会产生3,4级别的锁。locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。 有主外键约束时 update / delete ... ; 可能会产生4,5的锁。DDL语句时是6的锁。   如果出现了锁的问题, 某个DML操作可能等待很久没有反应。 当你采用的是直接连接数据库的方式，也不要用OS系统命令 $kill process_num 或者 $kill -9 process_num来终止用户连接，因为一个用户进程可能产生一个以上的锁, 杀OS进程并不能彻底清除锁的问题。 记得在数据库级别用alter system kill session 'sid,serial#';杀掉不正常的锁。