Oracle确定锁源头的步骤

mahan

Part1数据库环境
Oracle 11GR2 Rac
Part2问题模拟
生成测试用的scott用户
[oracle ]$ find /u01/app/oracle/product/11.2.0/db/ -name "utlsampl.sql"

SQL> @/u01/app/oracle/product/11.2.0/db/rdbms/admin/utlsampl.sql

conn scott/scott
时间点1、实例1、会话1执行语句未提交或者回滚
SQL> select * from v$mystat where rownum =1;

       SID STATISTIC#    VALUE
---------- ---------- ----------
      2984     0        0

SQL> update emp set sal=8000 where empno=7788;

1 row updated.
时间点2、实例2、会话3执行语句未提交或者回滚
SQL> select * from v$mystat where rownum =1;

       SID STATISTIC#    VALUE
---------- ---------- ----------
       998     0        0


SQL>  delete from emp where empno=7839;

1 row deleted.

SQL> update emp set job = 'MANAGER' where empno = 7788;
SQL> rollback；

卡住了卡住了
时间点3、实例2、会话4执行语句未提交或者回滚
SQL> select * from v$mystat where rownum =1;

       SID STATISTIC#    VALUE
---------- ---------- ----------
       671     0        0

SQL> update emp set sal =15000 where empno = 7839;
SQL> rollback；

卡住了卡住了
时间点4、实例1、会话2执行语句未提交或者回滚
SQL> select * from v$mystat where rownum =1;

       SID STATISTIC#    VALUE
---------- ---------- ----------
10     0        0

SQL> update emp set job='CEO' where empno = 7839;
SQL> rollback；

卡住了卡住了
Part3问题定位
第一个定位SQL
这个是DBA攻坚指南提供的SQL

select a.inst_id,
       a.process,
       a.sid,
       a.serial#,
       a.sql_id,
       a.event,
       a.status,
       a.program,
       a.machine,
       connect_by_isleaf as isleaf,
       sys_connect_by_path(a.SID || '@' || a.inst_id, ' <- ') tree,
       level as tree_level
  from gv$session a
start with a.blocking_session is not null
connect by (a.sid || '@' || a.inst_id) = prior
           (a.blocking_session || '@' || a.blocking_instance);      
null
可以看到的是的确是抓住最初没提交的SQL了

SELECT inst_id,sql_text,SQL_id,module FROM gv$sql WHERE sql_id = (SELECT NVL(prev_sql_id,'1') FROM  gv$session WHERE sid=2984 and inst_id=1);

INST_ID            SQL_TEXT                            SQL_ID        MODULE
   1    update emp set sal=8000 where empno=7788   9as2y6j3u24v3   SQL*Plus
干掉他，以后的就都释放了

alter system kill session '2984,37021,@1' immediate;
领导给的SQL
SELECT 'Instance '||s1.INST_ID||' '|| s1.username || '@' || s1.machine
|| ' ( SID= ' || s1.sid || ','|| s1.serial#||',@'||s1.INST_ID||' '||s1.status||  '  )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' ||s2.sql_id
FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
WHERE s1.sid=l1.sid AND
s1.inst_id=l1.inst_id AND
s2.sid=l2.sid AND
s2.inst_id=l2.inst_id AND
l1.BLOCK=1 AND
l2.request > 0 AND
l1.id1 = l2.id1 AND
l2.id2 = l2.id2 ;
查询结果

'INSTANCE'||S1.INST_ID||''||S1.USERNAME||'@'||S1.MACHINE||'(SID='||S1.SID||','||S1.SERIAL#||',@'||S1.INST_ID||''||S1.STATUS||')I

Instance 2 SCOTT@ojndev42 ( SID= 998,21071,@2 ACTIVE  )  is blocking SCOTT@ojndev42 ( SID=671 ) afphmw0pd08xq
Instance 2 SCOTT@ojndev42 ( SID= 998,21071,@2 ACTIVE  )  is blocking SCOTT@ojndev41 ( SID=10 ) 1swzb3n1zd9vp
可以看到的是执行的第二个SQL语句

SELECT inst_id,sql_text,SQL_id,module FROM gv$sql WHERE sql_id = (SELECT NVL(prev_sql_id,'1') FROM  gv$session WHERE sid=998 and inst_id=2);

INST_ID        SQL_TEXT                     SQL_ID      MODULE
   2    delete from emp where empno=7839 9y17vzq06y7sz SQL*Plus
查杀试试

alter system kill session '998,21071,@2' immediate;
释放了其中一条，但是源头还是卡住，而且再用这个去查，其实是查不到了
网上的SQL
SELECT distinct status,
                state,
                event,
                blocking_session BLS,
                ' alter system kill session ''' || b.sid || ',' ||
                b.serial# ||',@' || b.inst_id || ''' immediate;',
                c.cpu_time,
                c.elapsed_TIME,
                c.SQL_TEXT,
                c.SQL_ID,
                b.username,
                b.sid,
                b.serial#,
                logon_time,
                osuser OUSER,
                machine,
                LAST_ACTIVE_TIME,
                b.*
  FROM gv$session b, gv$sqlarea c
WHERE b.SQL_ID = c.SQL_ID
   and b.inst_id = c.inst_id
--and EVENT = 'enq: TX - row lock contention'
--and upper(sql_text )like '%SYS_FLOW_TASK_SH%'
--and  sid = '2545'
and username = 'CWBASE21_9999'
order by CPU_TIME desc, c.SQL_TEXT, b.logon_time;
null 通过ELAPSED_TIME判断锁定时间长度，然后根据锁定时间最长的那行，找到BLS，锁定会话源头，根据这个再进行查找锁源头。

oradebug
SQL> oradebug -g all hanganalyze 3

Hang Analysis in /u01/app/oracle/diag/rdbms/oj4/oj42/trace/ojndev42_diag_27399.trc
*** 2025-04-11 11:40:53.174
REQUEST:custom dump [6] with parameters [51][56][1415053314][2], requested by (instance=0, osid=30184), summary=[ges process stack dump (kjdglblkrdm1)].
Performing custom dump
Dump is written to /u01/app/oracle/diag/rdbms/ojndev4/ojndev42/trace/ojndev42_diag_27399_3.trc
End of custom dump

*** 2025-04-14 09:21:26.438
DIAG sends out oradebug command to nodes [0,1]
****************************************************************
PORADEBUG BEGIN ORIGINATING INST:2 SERIAL:0 PID:12507
PORADATA COMMAND:hanganalyze 3
session (kjzddbx) switches to a new action
PORADATA TRACEFILE /u01/app/oracle/diag/rdbms/ojndev4/ojndev42/trace/ojndev42_diag_27399.trc
PORADEBUG END ORIGINATING INST:2 SERIAL:0 PID:12507
****************************************************************

*** 2025-04-14 09:21:26.932
===============================================================================
HANG ANALYSIS:
  instances (db_name.oracle_sid): ojndev4.ojndev42, ojndev4.ojndev41
  oradebug_node_dump_level: 3
  analysis initiated by oradebug
  os thread scheduling delay history: (sampling every 1.000000 secs)
    0.000000 secs at [ 09:21:26 ]
      NOTE: scheduling delay has not been sampled for 0.412849 secs    0.000000 secs from [ 09:21:22 - 09:21:27 ], 5 sec avg
    0.000000 secs from [ 09:20:27 - 09:21:27 ], 1 min avg
    0.000000 secs from [ 09:16:27 - 09:21:27 ], 5 min avg
  vktm time drift history
===============================================================================

Chains most likely to have caused the hang:
[a] Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock contention'
     Chain 1 Signature Hash: 0x42598823
Chain 2 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock contention'
     Chain 2 Signature Hash: 0x42598823
[c] Chain 3 Signature: <not in a wait>
     Chain 3 Signature Hash: 0x673a0128

===============================================================================
Non-intersecting chains:

-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 2 (ojndev4.ojndev42)
                   os id: 24244
              process id: 50, oracle@ojndev42 (TNS V1-V3)
              session id: 671
        session serial #: 46759
    }
    is waiting for 'enq: TX - row lock contention' with wait info:
    {
                      p1: 'name|mode'=0x54580006
                      p2: 'usn<<16 | slot'=0x14000d
                      p3: 'sequence'=0x242f7d0
            time in wait: 36 min 54 sec
           timeout after: never
                 wait id: 30
                blocking: 0 sessions
             current sql: update emp set sal =15000 where empno = 7839
             short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+178<-ksliwat()+2066<-kslwaitctx()+163<-kjusuc()+3401<-ksipgetctxi()+1759<-ksqcmi()+20798<-ksqgtlctx()+3501<-ksqgelctx()+557<-ktuGetTxForXid()+131<-ktcwit1()+343<-kdddgb()+9090<-kdusru()+494<-kauupd()+412<-updrow()+2167<-qerupFetch()+860<-updaul()+1378<-updThreePhaseExe()+318<-updexe()+638<-opiexe()+10378<-kpoal8()+2392<-opiodr()+917<-ttcpip()+1255<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o(
            wait history:
              * time between current wait and wait #1: 0.001292 sec
              1.       event: 'SQL*Net message from client'
                 time waited: 1 min 24 sec
                     wait id: 29              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000003 sec
              2.       event: 'SQL*Net message to client'
                 time waited: 0.000001 sec
                     wait id: 28              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #2 and #3: 0.000040 sec
              3.       event: 'SQL*Net message from client'
                 time waited: 0.000224 sec
                     wait id: 27              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
    }
    and is blocked by
=> Oracle session identified by:
    {
                instance: 2 (ojndev4.ojndev42)
                   os id: 24255
              process id: 51, oracle@ojndev42 (TNS V1-V3)
              session id: 998
        session serial #: 21071
    }
    which is waiting for 'enq: TX - row lock contention' with wait info:
    {
                      p1: 'name|mode'=0x54580006
                      p2: 'usn<<16 | slot'=0x7001a
                      p3: 'sequence'=0x28da32
            time in wait: 37 min 15 sec
           timeout after: never
                 wait id: 38
                blocking: 2 sessions
             current sql: update emp set job = 'MANAGER' where empno = 7788
             short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+178<-ksliwat()+2066<-kslwaitctx()+163<-kjusuc()+3401<-ksipgetctxi()+1759<-ksqcmi()+20798<-ksqgtlctx()+3501<-ksqgelctx()+557<-ktuGetTxForXid()+131<-ktcwit1()+343<-kdddgb()+9090<-kdusru()+494<-kauupd()+412<-updrow()+2167<-qerupFetch()+860<-updaul()+1378<-updThreePhaseExe()+318<-updexe()+638<-opiexe()+10378<-kpoal8()+2392<-opiodr()+917<-ttcpip()+1255<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o(
            wait history:
              * time between current wait and wait #1: 0.000883 sec
              1.       event: 'SQL*Net message from client'
                 time waited: 30.531305 sec
                     wait id: 37              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000004 sec
              2.       event: 'SQL*Net message to client'
                 time waited: 0.000002 sec
                     wait id: 36              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #2 and #3: 0.000067 sec
              3.       event: 'gc current grant 2-way'
                 time waited: 0.000099 sec
                     wait id: 35              p1: ''=0x5
                                              p2: ''=0x29a3
                                              p3: ''=0x2010001
    }
    and is blocked by
=> Oracle session identified by:
    {
                instance: 1 (ojndev4.ojndev41)
                   os id: 7465
              process id: 89, oracle@ojndev41 (TNS V1-V3)
              session id: 2984
        session serial #: 37021
    }
    which is waiting for 'SQL*Net message from client' with wait info:
    {
                      p1: 'driver id'=0x62657100
                      p2: '#bytes'=0x1
            time in wait: 38 min 10 sec
           timeout after: never
                 wait id: 66
                blocking: 3 sessions
             current sql: <none>
             short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-read()+14<-ntpfprd()+117<-nsbasic_brc()+396<-nsbrecv()+69<-nioqrc()+495<-opikndf2()+978<-opitsk()+831<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+245
            wait history:
              * time between current wait and wait #1: 0.000005 sec
              1.       event: 'SQL*Net message to client'
                 time waited: 0.000001 sec
                     wait id: 65              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000246 sec
              2.       event: 'db file sequential read'
                 time waited: 0.000312 sec
                     wait id: 64              p1: 'file#'=0x5
                                              p2: 'block#'=0x299c
                                              p3: 'blocks'=0x1
              * time between wait #2 and #3: 0.000170 sec
              3.       event: 'gc cr block 2-way'
                 time waited: 0.000367 sec
                     wait id: 63              p1: ''=0x5
                                              p2: ''=0x29a3
                                              p3: ''=0x1
    }

Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0x42598823
-------------------------------------------------------------------------------

===============================================================================
Intersecting chains:

-------------------------------------------------------------------------------
Chain 2:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (ojndev4.ojndev41)
                   os id: 7449
              process id: 80, oracle@ojndev41 (TNS V1-V3)
              session id: 10
        session serial #: 55675
    }
    is waiting for 'enq: TX - row lock contention' with wait info:
    {
                      p1: 'name|mode'=0x54580006
                      p2: 'usn<<16 | slot'=0x14000d
                      p3: 'sequence'=0x242f7d0
            time in wait: 36 min 38 sec
           timeout after: never
                 wait id: 70
                blocking: 0 sessions
             current sql: update emp set job='CEO' where empno = 7839
             short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+178<-ksliwat()+2066<-kslwaitctx()+163<-kjusuc()+3401<-ksipgetctxi()+1759<-ksqcmi()+20798<-ksqgtlctx()+3501<-ksqgelctx()+557<-ktuGetTxForXid()+131<-ktcwit1()+343<-kdddgb()+9090<-kdusru()+494<-kauupd()+412<-updrow()+2167<-qerupFetch()+860<-updaul()+1378<-updThreePhaseExe()+318<-updexe()+638<-opiexe()+10378<-kpoal8()+2392<-opiodr()+917<-ttcpip()+1255<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o(
            wait history:
              * time between current wait and wait #1: 0.000200 sec
              1.       event: 'gc current block 2-way'
                 time waited: 0.000448 sec
                     wait id: 69              p1: ''=0x5
                                              p2: ''=0x299c
                                              p3: ''=0x2000001
              * time between wait #1 and #2: 0.000247 sec
              2.       event: 'gc cr block busy'
                 time waited: 0.001294 sec
                     wait id: 68              p1: ''=0x5
                                              p2: ''=0x29a3
                                              p3: ''=0x1
              * time between wait #2 and #3: 0.000095 sec
              3.       event: 'Disk file operations I/O'
                 time waited: 0.000006 sec
                     wait id: 67              p1: 'FileOperation'=0x2
                                              p2: 'fileno'=0x5
                                              p3: 'filetype'=0x2
    }
    and is blocked by 'instance: 2, os id: 24255, session id: 998',
    which is a member of 'Chain 1'.

Chain 2 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock contention'
Chain 2 Signature Hash: 0x42598823
-------------------------------------------------------------------------------

===============================================================================
Sessions in an involuntary wait or not in a wait:

-------------------------------------------------------------------------------
Chain 3:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (ojndev4.ojndev41)
                   os id: 14468
              process id: 64, oracle@ojndev41 (J000)
              session id: 6
        session serial #: 959
    }
    is not in a wait:
    {
               last wait: 5 min 16 sec ago
                blocking: 0 sessions
             current sql: SELECT /*+ push_pred(v) */ U.NAME, OT.NAME, OT.SUBNAME, V.SPART, V.NAME, V.GLOB_ST, V.USER_ST, V.DISTCNT, V.DENSITY, V.SAMPLE_DISTCNT, V.SSIZE, V.NULL_CNT, V.TIMESTAMP#, V.AVGCLN, V.LOWVAL, V.HIVAL, V.MINIMUM, V.MAXIMUM, V.DMIN, V.DMAX, V.CCNT, V.BUCKET, V.ENDPOINT, V.EPVALUE, V.EAVS, V.FLAGS FROM SYS.USER$ U, SYS.OBJ
             short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-lxoCpStr()+55<-lxdprl()+643<-LdiDateToString()+4641<-pfrcvfc_format_conversion()+572<-pevm_CNVMSC()+34<-pfrinstr_CNVMSC()+50<-pfrrun_no_tool()+63<-pfrrun()+627<-plsql_run()+649<-peicnt()+302<-kkxexe()+503<-opiexe()+18001<-opiodr()+917<-rpidrus()+211<-skgmstack()+148<-rpiswu2()+690<-rpidrv()+1327<-rpiexe()+74<-kkjex1e()+8679<-kkjsexe()+711<-kkjrdp()+694<-opirip()+958<-opidrv()+603<-sou2o()+103<-opimai_real()+250<-ssthrdmain()+265<-main()+20
            wait history:
              1.       event: 'latch: row cache objects'
                 time waited: 0.000010 sec
                     wait id: 19588317        p1: 'address'=0x8947cdfb8
                                              p2: 'number'=0x135
                                              p3: 'tries'=0x0
              * time between wait #1 and #2: 9 min 0 sec
              2.       event: 'latch: row cache objects'
                 time waited: 0.000004 sec
                     wait id: 19588316        p1: 'address'=0x8947cdfb8
                                              p2: 'number'=0x135
                                              p3: 'tries'=0x0
              * time between wait #2 and #3: 4 min 44 sec
              3.       event: 'latch: row cache objects'
                 time waited: 0.000004 sec
                     wait id: 19588315        p1: 'address'=0x8947cdfb8
                                              p2: 'number'=0x135
                                              p3: 'tries'=0x0
    }

Chain 3 Signature: <not in a wait>
Chain 3 Signature Hash: 0x673a0128
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
Chain 4:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (ojndev4.ojndev41)
                   os id: 20240
              process id: 99, oracle@ojndev41
              session id: 997
        session serial #: 21303
    }
    is waiting for 'gc current request' with wait info:
    {
                      p1: 'file#'=0xe
                      p2: 'block#'=0x63d15
                      p3: 'id#'=0x2010001
            time in wait: 0.002025 sec
           timeout after: never
                 wait id: 90569
                blocking: 0 sessions
             current sql:  DELETE FROM  REPORT_TEMP2
             short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-times()+12<-kcbchg1_main()+3755<-kcbchg1()+205<-ktuchg2()+1420<-ktbchg2()+281<-kddchg()+1080<-kdddrp()+2280<-kdddel()+853<-kaudel()+115<-delrow()+1496<-qerdlFetch()+1822<-delexe()+1001<-opiexe()+10315<-kpoal8()+2392<-opiodr()+917<-ttcpip()+1255<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+245
            wait history:
              * time between current wait and wait #1: 0.000222 sec
              1.       event: 'gc current grant 2-way'
                 time waited: 0.004638 sec
                     wait id: 90568           p1: ''=0x3
                                              p2: ''=0x5e4e5
                                              p3: ''=0x2000054
              * time between wait #1 and #2: 0.000361 sec
              2.       event: 'gc current grant 2-way'
                 time waited: 0.001437 sec
                     wait id: 90567           p1: ''=0x3
                                              p2: ''=0x5e4e4
                                              p3: ''=0x2000054
              * time between wait #2 and #3: 0.000319 sec
              3.       event: 'gc current grant 2-way'
                 time waited: 0.000108 sec
                     wait id: 90566           p1: ''=0x3
                                              p2: ''=0x5e4e3
                                              p3: ''=0x2000054
    }

Chain 4 Signature: 'gc current request'
Chain 4 Signature Hash: 0x7962fc88
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
Chain 5:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (ojndev4.ojndev41)
                   os id: 30719
              process id: 10, oracle@ojndev41 (DIA0)
              session id: 3301
        session serial #: 1
    }
    is not in a wait:
    {
               last wait: 0.006684 sec ago
                blocking: 0 sessions
             current sql: <none>
             short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-__poll()+16<-sskgxp_selectex()+392<-skgxpiwait()+4318<-skgxpwaiti()+1916<-skgxpwait()+162<-ksxpwait()+2501<-ksxprcv_int()+5363<-ksxprcvimd()+36<-kjzcwait()+302<-kjzcrcv()+409<-kjzfrcv()+479<-kjzgpoll()+753<-kjzdm_rac()+1003<-kjzdm()+1252<-ksbrdp()+1045<-opirip()+623<-opidrv()+603<-sou2o()+103<-opimai_real()+250<-ssthrdmain()+265<-main()+201<-__libc_start_main()+245
            wait history:
              1.       event: 'DIAG idle wait'
                 time waited: 0.010070 sec
                     wait id: 79886290        p1: 'component'=0x1
                                              p2: 'where'=0x1
                                              p3: 'wait time(millisec)'=0xa
              * time between wait #1 and #2: 0.000046 sec
              2.       event: 'DIAG idle wait'
                 time waited: 0.184270 sec
                     wait id: 79886289        p1: 'component'=0x1
                                              p2: 'where'=0x1
                                              p3: 'wait time(millisec)'=0xb4
              * time between wait #2 and #3: 0.000055 sec
              3.       event: 'DIAG idle wait'
                 time waited: 0.204301 sec
                     wait id: 79886288        p1: 'component'=0x1
                                              p2: 'where'=0x1
                                              p3: 'wait time(millisec)'=0xc8
    }

Chain 5 Signature: <not in a wait>
Chain 5 Signature Hash: 0x673a0128
-------------------------------------------------------------------------------
从上往下推导到源头，chain代表一个链条，看到chain最后的一条即可

instance: 1 (ojndev4.ojndev41)
os id: 7465
process id: 89, oracle@ojndev41 (TNS V1-V3)
session id: 2984
session serial #: 37021
blocking: 3 sessions
current sql: <none>
快速理解锁的流程，配合开发定位问题，但是没法查到导致锁的语句是哪个。

Part4知识扩展
在锁的源头再加上一条查询语句
SQL> conn scott/scott
Connected.
SQL> select * from v$mystat where rownum =1;

       SID STATISTIC#    VALUE
---------- ---------- ----------
      2984     0        0

SQL> update emp set sal=8000 where empno=7788;

1 row updated.

我再加上执行这个SQL
SQL> select * from v$mystat where rownum =1;

       SID STATISTIC#    VALUE
---------- ---------- ----------
      2984     0        0
再查SQL就变成查询语句了，这就是大家的疑惑，查询SQL也能锁表

SELECT inst_id,sql_text,SQL_id,module FROM gv$sql WHERE sql_id = (SELECT NVL(prev_sql_id,'1') FROM  gv$session WHERE sid=2984 and inst_id=1);

    INST_ID SQL_TEXT SQL_ID MODULE
1 1 select * from v$mystat where rownum =1 df8hd7yrp3avs SQL*Plus
2 2 select * from v$mystat where rownum =1 df8hd7yrp3avs SQL*Plus
如果是在事后追寻，而且流程里边已经已经执行了新的SQL那就代表没用了，只能抓到这个锁的会话，却没有办法抓到锁的SQL。

Part5思考思路
其中一个方便理解的SQL

set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column Seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column fblocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra

SELECT *
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)||
' from Instance '||blocker_instance BLOCKER_PROC,
'Number of waiters: '||num_waiters waiters,
'Final Blocking Process: '||decode(p.spid,null,'',
p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC,
'Program: '||p.program image,
'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,
'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
'',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
gv$session s,
gv$session bs,
gv$instance i,
gv$process p
WHERE wc.instance = i.instance_number (+)
AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)
and wc.sess_serial# = s.serial# (+))
AND (s.final_blocking_instance = bs.inst_id (+) and s.final_blocking_session = bs.sid (+))
AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))
AND ( num_waiters > 0
OR ( blocker_osid IS NOT NULL
AND in_wait_secs > 10 ) )
ORDER BY chain_id,
num_waiters DESC)
WHERE ROWNUM < 101;
Part6SQL使用排序
SELECT distinct status,
                state,
                event,
                blocking_session BLS,
                ' alter system kill session ''' || b.sid || ',' ||
                b.serial# ||',@' || b.inst_id || ''' immediate;',
                c.cpu_time,
                c.elapsed_TIME,
                c.SQL_TEXT,
                c.SQL_ID,
                b.username,
                b.sid,
                b.serial#,
                logon_time,
                osuser OUSER,
                machine,
                LAST_ACTIVE_TIME,
                b.*
  FROM gv$session b, gv$sqlarea c
WHERE b.SQL_ID = c.SQL_ID
   and b.inst_id = c.inst_id
--and EVENT = 'enq: TX - row lock contention'
--and upper(sql_text )like '%SYS_FLOW_TASK_SH%'
--and  sid = '2545'
and username = 'CWBASE21_9999'
order by CPU_TIME desc, c.SQL_TEXT, b.logon_time;
select a.inst_id,
       a.process,
       a.sid,
       a.serial#,
       a.sql_id,
       a.event,
       a.status,
       a.program,
       a.machine,
       connect_by_isleaf as isleaf,
       sys_connect_by_path(a.SID || '@' || a.inst_id, ' <- ') tree,
       level as tree_level
  from gv$session a
start with a.blocking_session is not null
connect by (a.sid || '@' || a.inst_id) = prior
           (a.blocking_session || '@' || a.blocking_instance);      
SELECT 'Instance '||s1.INST_ID||' '|| s1.username || '@' || s1.machine
|| ' ( SID= ' || s1.sid || ','|| s1.serial#||',@'||s1.INST_ID||' '||s1.status||  '  )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' ||s2.sql_id
FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
WHERE s1.sid=l1.sid AND
s1.inst_id=l1.inst_id AND
s2.sid=l2.sid AND
s2.inst_id=l2.inst_id AND
l1.BLOCK=1 AND
l2.request > 0 AND
l1.id1 = l2.id1 AND
l2.id2 = l2.id2 ;
这三条下来基本能干掉的都干掉了

Part7原理
当事务（Transaction）更改了一行，那么该事务的唯一标志（ITL）就会被记录在数据库块头（Data Block header）中。

在transaction开始前，系统从回滚段头的事务表（Undo Transaction Table List）中分配了一个ITL事务槽，同时将ITL记录到被事务影响的Data Block中。与此同时，被该事务影响更改的行将会使用Lock Bytes索引指向该ITL。

这样，当新的事务需要更改同一行时，将会发现有尚未提交的事务（Uncommited Tran-action）正在修改该行，那么该事务就会被阻塞等待（Transaction Blocj Wait）。

一旦事务提交或者回滚，事务锁就会被释放。即那个被堵塞等待的事务就锁定该行了。

Part7最后的疑问
到底有没有办法事后锁定锁源头的SQL