| 两种锁类型：SQ、JQ | DBA之路：OoNiceDream |

两种锁类型：SQ、JQ

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2010/03/sq_jq_lock.html

最近在一个客户的系统上有时可以观察到不少的“enq: SQ - contention”等待，以前一般都按hash_value、event来group by，再按hash_value来找到相关语句，结果相关的SQL语句中没有看到sequence的影子。查看AWR报告，也找不到sequence。最终简单地查了dba_sequences视图，把cache size为0的都调大了。

后来与老大们聊起，才发现enq: SQ - contention的P2字段就是sequence的object id。以前查enqueue光注意P1了：

SQL> select name, parameter1, parameter2, parameter3
  2    from v$event_name
  3   where name = 'enq: SQ - contention';
 
NAME                           PARAMETER1      PARAMETER2      PARAMETER3
------------------------------ --------------- --------------- ---------------
enq: SQ - contention           name|mode       object #        0

另外查v$lock，type为SQ，效果是一样的，ID1就是sequence的object id：

SQL> select * from v$lock where type='SQ';
 
ADDR             KADDR             SID TY    ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---- -- ------ --- ----- ------- ----- -----
C000000A09AAD688 C000000A09AAD6A8  972 SQ 371898   0     0       6     2     0
C000000A09AAD7C8 C000000A09AAD7E8 1445 SQ 371898   0     0       6     0     0
C000000A09AAE768 C000000A09AAE788 2021 SQ 371898   0     0       6     0     0
C000000A09AADFE8 C000000A09AAE008 2236 SQ 371898   0     0       6     1     0
C000000A09AAE3A8 C000000A09AAE3C8 2734 SQ 371898   0     0       6     0     0
C000000A09AAE588 C000000A09AAE5A8 3681 SQ 371898   0     0       6     0     0
C000000A09AAEF88 C000000A09AAEFA8 3748 SQ 371898   0     0       6     0     0
C000000A09AAE6C8 C000000A09AAE6E8 3785 SQ 371898   0     0       6     0     0
C000000A09AB0248 C000000A09AB0268 3867 SQ 371898   0     6       0     2     1
 
SQL> select object_id,owner,object_name,object_type from dba_objects
  2  where object_id=371898;
 
 OBJECT_ID OWNER   OBJECT_NAME       OBJECT_TYPE
---------- ------- ----------------- ------------
    371898 U_NAME  U_NAME_SEQUENCE_1 SEQUENCE
 
1 row selected.

JQ锁，是一次在查看dba_jobs_running非常慢时，查看了下视图创建语句发现的：

create or replace view dba_jobs_running as
select v.SID, v.id2 JOB, j.FAILURES,
    LAST_DATE, substr(to_char(last_date,'HH24:MI:SS'),1,8) LAST_SEC,
    THIS_DATE, substr(to_char(this_date,'HH24:MI:SS'),1,8) THIS_SEC,
    j.field1 INSTANCE
  from sys.job$ j, v$lock v
  where v.type = 'JQ' and j.job (+)= v.id2;