Oracle PLSQL中dbms_lock最大等待线程数限制及未入队线程去向查询

你当前使用的锁等待查询语句 #

SELECT
   l.*,
   substr(a.name,1,41) name,
   substr(s.program,1,45) program,
   p.spid SPID,
   s.osuser,
   l.SID SID,
   s.process PID,
   s.TERMINAL,
   S.STATUS
FROM
   sys.dbms_lock_allocated a,
   v$lock l,
   v$session s,
   v$process p
WHERE
   a.lockid = l.id1 and
   l.type = 'UL' and
   l.sid = s.sid and
   p.addr = s.paddr;

上述语句只能统计到已经进入数据库层面、处于活跃状态的UL锁持有者/等待者，无法统计应用层排队的线程，这是你观测到的等待数和实际调用数存在量级差的核心原因。

决定可等待锁最大线程数的核心因素 #

• 数据库层配置参数
  数据库的PROCESSES和SESSIONS初始化参数是总天花板，所有数据库会话（包括等待锁的会话）总数不能超过这两个参数的限制。除此之外如果配置了用户Profile的SESSIONS_PER_USER参数，单个用户能持有的会话数也会被限制，超过上限的连接会被直接拒绝。
• 应用层连接池配置
  数千个业务线程不会直接和数据库建连，通常会通过连接池复用连接。如果你的连接池最大连接数配置为200左右，那么同一时间最多只有200个线程能拿到数据库连接执行API逻辑，剩余的线程会在应用层的连接池队列中排队，根本不会进入数据库层面，自然不会出现在v$lock的查询结果中。
• DBMS_LOCK.request的超时参数
  你调用dbms_lock.request时如果指定了timeout参数非0，等待锁超过指定时间的会话会直接返回超时错误，退出锁等待队列，不会继续留在v$lock的结果里。
• 数据库资源管理器规则
  如果数据库针对你的业务用户/服务配置了并发会话数限制，超过限制的请求会被资源管理器直接拦截，无法执行到申请锁的代码段。

剩余调用线程的处理逻辑 #

• 应用层排队：未拿到连接池连接的线程会在应用端的等待队列中排队，如果连接池配置了获取连接的超时时间，排队超时的线程会直接抛出连接异常，不会执行API代码。
• 锁等待超时：已经拿到数据库连接、进入锁等待的线程如果达到dbms_lock.request设置的超时时间，会收到非0的返回值（返回1代表超时），如果代码没有特殊重试逻辑，会直接抛出异常终止执行，释放持有的数据库连接。
• 异常中断：如果业务线程在等待锁的过程中被上层逻辑中断、或者客户端和数据库的连接断开，对应的会话会被数据库清理，也会从锁等待队列中移除。

内容的提问来源于stack exchange，提问作者Deepak