[java] thread dump 분석 example

아래의 정보를 확인해 보시면, com/aintop/db/PoolMan에서 lock 상태이며, lock 발생하는 Thread인 webtob1-hth0(localhost:9900)-w023이하로 26개의 Thread들이 wait lock 상태로 대기중입니다. webtob1-hth0(localhost:9900)-w023 Thread는 /jsp/setting/UserDetailUpdate.jsp 수행이후로 at jeus_jspwork/_jsp/_setting/_500_UserDetailUpdate_5fjsp._jspService(_500_UserDetailUpdate_5fjsp.java:141) socketRead상태로 DB로부터 응답을 기다리고 있는 상태입니다. /jsp/setting/UserDetailUpdate.jsp 에 대한 SQL 확인이 필요합니다. 감사합니다. 세부내용은 다음과 같습니다. 1. lock 발생하는 Thread인 webtob1-hth0(localhost:9900)-w023이하로 26개의 Thread들이 wait lock 상태로 대기중 2LKMONINUSE sys_mon_t:0x00000001276B7E50 infl_mon_t: 0x00000001276B7EA0: 3LKMONOBJECT com/aintop/db/PoolMan@070000001764EBB0/070000001764EBC8: owner "webtob1-hth0(localhost:9900)-w023 [container1-45]" (0x0000000123E3AE00), entry count 1 3LKWAITERQ Waiting to enter: 3LKWAITER "webtob1-hth0(localhost:9900)-w000 [container1-22]" (0x0000000124302700) 3LKWAITER "webtob1-hth0(localhost:9900)-w001 [container1-23]" (0x0000000124350800) 3LKWAITER "webtob1-hth0(localhost:9900)-w004 [container1-26]" (0x000000012443CC00) 3LKWAITER "webtob1-hth0(localhost:9900)-w005 [container1-27]" (0x000000012448B100) 3LKWAITER "webtob1-hth0(localhost:9900)-w006 [container1-28]" (0x00000001244DA700) 3LKWAITER "webtob1-hth0(localhost:9900)-w017 [container1-39]" (0x0000000123E4AE00) 3LKWAITER "webtob1-hth0(localhost:9900)-w019 [container1-41]" (0x0000000123F47500) 3LKWAITER "webtob1-hth0(localhost:9900)-w021 [container1-43]" (0x00000001249CC000) 3LKWAITER "webtob1-hth0(localhost:9900)-w022 [container1-44]" (0x0000000123E31F00) 3LKWAITER "webtob1-hth0(localhost:9900)-w025 [container1-47]" (0x0000000124737A00) 3LKWAITER "webtob1-hth0(localhost:9900)-w028 [container1-50]" (0x000000012496E100) 3LKWAITER "webtob1-hth0(localhost:9900)-w036 [container1-58]" (0x0000000124E1E100) 3LKWAITER "webtob1-hth0(localhost:9900)-w037 [container1-60]" (0x0000000124E6C800) 3LKWAITER "webtob1-hth0(localhost:9900)-w041 [container1-63]" (0x0000000124FA8200) 3LKWAITER "webtob1-hth0(localhost:9900)-w042 [container1-67]" (0x0000000124FF7900) 3LKWAITER "webtob1-hth0(localhost:9900)-w054 [container1-75]" (0x00000001253FB900) 3LKWAITER "webtob1-hth0(localhost:9900)-w059 [container1-79]" (0x000000012522F700) 3LKWAITER "webtob1-hth0(localhost:9900)-w062 [container1-82]" (0x00000001256F2500) 3LKWAITER "webtob1-hth0(localhost:9900)-w068 [container1-90]" (0x000000012593B900) 3LKWAITER "webtob1-hth0(localhost:9900)-w077 [container1-99]" (0x0000000125C72600) 3LKWAITER "webtob1-hth0(localhost:9900)-w079 [container1-101]" (0x0000000125D11E00) 3LKWAITER "webtob1-hth0(localhost:9900)-w080 [container1-102]" (0x0000000125D60000) 3LKWAITER "webtob1-hth0(localhost:9900)-w087 [container1-109]" (0x0000000125FD8A00) 3LKWAITER "webtob1-hth0(localhost:9900)-w088 [container1-110]" (0x0000000126047100) 3LKWAITER "webtob1-hth0(localhost:9900)-w090 [container1-112]" (0x00000001260E4600) 3LKWAITER "webtob1-hth0(localhost:9900)-w093 [container1-115]" (0x00000001261D4600) 2LKMONINUSE sys_mon_t:0x0000000127F4AAA0 infl_mon_t: 0x0000000127F4AAF0: : 2LKREGMON Thread public flags mutex lock (0x0000000123DA6540): 3LKNOTIFYQ Waiting to be notified: 3LKWAITNOTIFY "webtob1-hth0(localhost:9900)-w023 [container1-45]" (0x0000000123E3AE00) 1. lock 발생하는 Thread at jeus_jspwork/_jsp/_setting/_500_UserDetailUpdate_5fjsp._jspService(_500_UserDetailUpdate_5fjsp.java:141) 호출이후에 socketRead상태로 DB로부터 응답을 기다리고 있는 상태입니다. /jsp/setting/UserDetailUpdate.jsp 에 대해서 SQL 확인이 필요합니다. Thread Name webtob1-hth0(localhost:9900)-w023 [container1-45] State Waiting on condition Monitor Owns Monitor Lock on com/aintop/db/PoolMan@070000001764EBB0/070000001764EBC8 Java Stack at java/net/SocketInputStream.socketRead0(Native Method) at java/net/SocketInputStream.read(SocketInputStream.java:155(Compiled Code)) at oracle/net/ns/Packet.receive(Packet.java:308(Compiled Code)) at oracle/net/ns/DataPacket.receive(DataPacket.java:106(Compiled Code)) at oracle/net/ns/NetInputStream.getNextPacket(NetInputStream.java:324(Compiled Code)) at oracle/net/ns/NetInputStream.read(NetInputStream.java:268(Compiled Code)) at oracle/net/ns/NetInputStream.read(NetInputStream.java(Compiled Code)) at oracle/net/ns/NetInputStream.read(NetInputStream.java:107(Compiled Code)) at oracle/jdbc/driver/T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:124(Compiled Code)) at oracle/jdbc/driver/T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:80(Compiled Code)) at oracle/jdbc/driver/T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1137(Compiled Code)) at oracle/jdbc/driver/T4CTTIfun.receive(T4CTTIfun.java:350(Compiled Code)) at oracle/jdbc/driver/T4CTTIfun.doRPC(T4CTTIfun.java:227(Compiled Code)) at oracle/jdbc/driver/T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:383(Compiled Code)) at oracle/jdbc/driver/T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:776(Compiled Code)) at oracle/jdbc/driver/T4CConnection.logon(T4CConnection.java:432(Compiled Code)) at oracle/jdbc/driver/PhysicalConnection.(PhysicalConnection.java:553(Compiled Code)) at oracle/jdbc/driver/T4CConnection.(T4CConnection.java:254(Compiled Code)) at oracle/jdbc/driver/T4CDriverExtension.getConnection(T4CDriverExtension.java:32(Compiled Code)) at oracle/jdbc/driver/OracleDriver.connect(OracleDriver.java:528(Compiled Code)) at java/sql/DriverManager.getConnection(DriverManager.java:572(Compiled Code)) at java/sql/DriverManager.getConnection(DriverManager.java:165) at com/aintop/db/PoolMan.createConnection(PoolMan.java:354) at com/aintop/db/PoolMan.getConnection(PoolMan.java:200(Compiled Code)) at com/aintop/db/DBHandler.dbOpen(DBHandler.java:329(Compiled Code)) at com/aintop/db/DBHandler.executeSelect(DBHandler.java:389(Compiled Code)) at jeus_jspwork/_jsp/_setting/_500_UserDetailUpdate_5fjsp._jspService(_500_UserDetailUpdate_5fjsp.java:141) at com/hs/hip/servlet/HIJspPage.service(HIJspPage.java:154(Compiled Code)) at javax/servlet/http/HttpServlet.service(HttpServlet.java:856(Compiled Code)) at jeus/servlet/jsp/JspServletWrapper.executeServlet(JspServletWrapper.java:147(Compiled Code)) at jeus/servlet/filter2/FilterChainImpl.internalDoFilter(FilterChainImpl.java:139(Compiled Code)) at jeus/servlet/filter2/FilterChainImpl.doFilter(FilterChainImpl.java:91(Compiled Code)) at com/hs/common/filter/CharsetFilter.doFilter(CharsetFilter.java:84(Compiled Code)) at jeus/servlet/filter2/FilterChainImpl.internalDoFilter(FilterChainImpl.java:122(Compiled Code)) at jeus/servlet/filter2/FilterChainImpl.doFilter(FilterChainImpl.java:91(Compiled Code)) at jeus/servlet/engine/ServletWrapper.execute(ServletWrapper.java:195(Compiled Code)) at jeus/servlet/jsp/JspServletWrapper.execute(JspServletWrapper.java:193(Compiled Code)) at jeus/servlet/engine/WebtobRequestProcessor.run(WebtobRequestProcessor.java:183) Native Stack No Native stack trace available 2. Waiting lock 중 일부 Thread 정보 1) com/aintop/db/DBHandler.dbOpen 시점에 Waiting lock 발생 Thread Name webtob1-hth0(localhost:9900)-w001 [container1-23] State Blocked Monitor Waiting for Monitor Lock on com/aintop/db/PoolMan@070000001764EBB0/070000001764EBC8 Java Stack at com/aintop/db/DBHandler.dbOpen(DBHandler.java:329(Compiled Code)) at com/aintop/db/DBHandler.executeSelect(DBHandler.java:389(Compiled Code)) at jeus_jspwork/_eip/_portal/_body/_theme002/_gadget/_500_GdgtCop_5fjsp._jspService(_500_GdgtCop_5fjsp.java:412(Compiled Code)) at jeus/servlet/jsp/HttpJspBase.service(HttpJspBase.java:54(Compiled Code)) at javax/servlet/http/HttpServlet.service(HttpServlet.java:856(Compiled Code)) at jeus/servlet/jsp/JspServletWrapper.executeServlet(JspServletWrapper.java:147(Compiled Code)) at jeus/servlet/engine/RequestDispatcherImpl.processCommonRequestDispatcher(RequestDispatcherImpl.java:505(Compiled Code)) at jeus/servlet/engine/RequestDispatcherImpl.doInclude(RequestDispatcherImpl..java:646(Compiled Code)) at jeus/servlet/engine/RequestDispatcherImpl.include(RequestDispatcherImpl.java:598(Compiled Code)) at jeus/servlet/jsp2/runtime/JspRuntimeLibrary.include(JspRuntimeLibrary.java:967(Compiled Code)) at jeus/servlet/jsp2/runtime/PageContextImpl.doInclude(PageContextImpl.java:641(Compiled Code)) at jeus/servlet/jsp2/runtime/PageContextImpl.include(PageContextImpl.java:620(Compiled Code)) at jeus_jspwork/_eip/_portal/_body/_theme002/_500_MainBody_5fjsp._jspService(_500_MainBody_5fjsp.java:873(Compiled Code)) at jeus/servlet/jsp/HttpJspBase.service(HttpJspBase.java:54(Compiled Code)) at javax/servlet/http/HttpServlet.service(HttpServlet.java:856(Compiled Code)) at jeus/servlet/jsp/JspServletWrapper.executeServlet(JspServletWrapper.java:147(Compiled Code)) at jeus/servlet/filter2/FilterChainImpl.internalDoFilter(FilterChainImpl.java:139(Compiled Code)) at jeus/servlet/filter2/FilterChainImpl.doFilter(FilterChainImpl.java:91(Compiled Code)) at com/hs/common/filter/CharsetFilter.doFilter(CharsetFilter.java:84(Compiled Code)) at jeus/servlet/filter2/FilterChainImpl.internalDoFilter(FilterChainImpl.java:122(Compiled Code)) at jeus/servlet/filter2/FilterChainImpl.doFilter(FilterChainImpl.java:91(Compiled Code)) at jeus/servlet/engine/ServletWrapper.execute(ServletWrapper.java:195(Compiled Code)) at jeus/servlet/jsp/JspServletWrapper.execute(JspServletWrapper.java:193(Compiled Code)) at jeus/servlet/engine/WebtobRequestProcessor.run(WebtobRequestProcessor.java:183) Native Stack No Native stack trace available 2) com/aintop/db/DBHandler.dbClose 시점에 Waiting lock 발생 Thread Name webtob1-hth0(localhost:9900)-w019 [container1-41] State Blocked Monitor Waiting for Monitor Lock on com/aintop/db/PoolMan@070000001764EBB0/070000001764EBC8 Java Stack at com/aintop/db/DBHandler.dbClose(DBHandler.java:347(Compiled Code)) at com/aintop/db/DBHandler.executeSelect(DBHandler.java:409(Compiled Code)) at jeus_jspwork/_aintop/_f_5fcharts/_data/_500_MSBar2D_5fjsp._jspService(_500_MSBar2D_5fjsp.java:116(Compiled Code)) at jeus/servlet/jsp/HttpJspBase.service(HttpJspBase.java:54(Compiled Code)) at javax/servlet/http/HttpServlet.service(HttpServlet.java:856(Compiled Code)) at jeus/servlet/jsp/JspServletWrapper.executeServlet(JspServletWrapper.java:147(Compiled Code)) at jeus/servlet/filter2/FilterChainImpl.internalDoFilter(FilterChainImpl.java:139(Compiled Code)) at jeus/servlet/filter2/FilterChainImpl.doFilter(FilterChainImpl.java:91(Compiled Code)) at com/hs/common/filter/CharsetFilter.doFilter(CharsetFilter.java:84(Compiled Code)) at jeus/servlet/filter2/FilterChainImpl.internalDoFilter(FilterChainImpl.java:122(Compiled Code)) at jeus/servlet/filter2/FilterChainImpl.doFilter(FilterChainImpl.java:91(Compiled Code)) at jeus/servlet/engine/ServletWrapper.execute(ServletWrapper.java:195(Compiled Code)) at jeus/servlet/jsp/JspServletWrapper.execute(JspServletWrapper.java:193(Compiled Code)) at jeus/servlet/engine/WebtobRequestProcessor.run(WebtobRequestProcessor.java:183) Native Stack No Native stack trace available

[Database] 오라클(oracle) 락(lock) 확인 및 제거(kill)

-- 락걸린 테이블 확인 SELECT do.object_name, do.owner, do.object_type, do.owner, vo.xidusn, vo.session_id, vo.locked_mode FROM v$locked_object vo , dba_objects do WHERE vo.object_id = do.object_id ; --해당테이블이 락에 걸렸는지.. SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C WHERE A.SID=B.SID AND B.ID1=C.OBJECT_ID AND B.TYPE='TM' AND C.OBJECT_NAME IN ('테이블명'); /* 락발생 사용자와 sql, object 조회 */ SELECT distinct x.session_id, a.serial#, d.object_name, a.machine, a.terminal, a.program, b.address, b.piece, b.sql_text FROM v$locked_object x, v$session a, v$sqltext b, dba_objects d WHERE x.session_id = a.sid and x.object_id = d.object_id and a.sql_address = b.address order by b.address,b.piece; /* 락 발생 사용자확인 */ SELECT distinct x.session_id, a.serial#, d.object_name, a.machine, a.terminal, a.program, a.logon_time , 'alter system kill session ''' || a.sid || ', ' || a.serial# || ''';' FROM gv$locked_object x, gv$session a, dba_objects d WHERE x.session_id = a.sid and x.object_id = d.object_id order by logon_time; /* 접속 사용자 제거 */ --alter system kill session 'session_id,serial#'; alter system kill session '26,6044'; /* 현재 접속자의 sql 분석 */ SELECT distinct a.sid, a.serial#, a.machine, a.terminal, a.program, b.address, b.piece, b.sql_text FROM v$session a, v$sqltext b WHERE a.sql_address = b.address order by a.sid, a.serial#,b.address,b.piece;