JDBC Resource Registration on WebSphere Console

decorumgroveInternet και Εφαρμογές Web

7 Αυγ 2012 (πριν από 5 χρόνια και 2 μήνες)

356 εμφανίσεις

1

JDBC Resource Registration on WebSphere Console

javax.naming.InitialContext ctx = new javax.naming.InitialContext();

javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("
jdbc/DB2UDB
");

java.sql.Connection conn = ds.getConnection();

NO !!!

Default:

TRANSACTION_NONE

Sharable Connection

Information logging every called

Servlet/JSP
코드에서

명시적으로

conn.close()
하였더라도

Servlet/JSP


끝날

때까지

connection


pool


release
되지

않음



불필요한

과도한

JDBC
연결자원사용됨
/
경우에

따라

성능장애

2

J2EE: DataSource Resource Reference when EAR packaging

jdbc/DB2UDB

User

Application

Database B

WebSphere Application Server

Transaction

manager

jdbc/MyDB

Resource Manager

EAR

Resource Reference

Mapping during deploy

javax.naming.InitialContext ctx = new javax.naming.InitialContext();

javax.sql.DataSource ds =


(javax.sql.DataSource)ctx.lookup(“
java:comp/env/jdbc/MyDB
");

java.sql.Connection conn = ds.getConnection();

register

end

request start to end

with a thread lifetime

Global JNDI name

Local JNDI name

3

Using AAT, Datasource Resource Reference

javax.naming.InitialContext ctx = new javax.naming.InitialContext();

javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("
java:comp/env/jdbc/UnsharedDS
");

java.sql.Connection conn = ds.getConnection();

공유범위
:

Shareable/Unshareable

AAT


EAR

웹모듈


자원참조

유형
:
DataSource

4

Using AAT, Transaction Isolation Level

동시성

제어
(
Concurrency Control)
이슈

Transaction Isolation Level

(1) TRANSACTION_NONE (default)

(2) TRANSACTION_READ_UNCOMMITTED

(3) TRANSACTION_READ_COMMITTED

(4) TRANSACTION_REPEATABLE_READ

(5) TRANSACTION_SERIALIZABLE

902
웹스피어

5.0
데이타베이스

연결

설정법


http://www.javaservice.net/~java/bbs/read.cgi?m=appserver&b=was&c=r_p&n=1061739578

5

public void method() throws Exception {


String name =
getEmployeeName
("7904");


String dept =
getDeptName
("1234");

}

public String
getEmployeeName
(String id) throws Exception {


Connection conn = null; Statement stmt = null; String name = null;


try{


javax.sql.DataSource ds =


(javax.sql.DataSource)ctx.lookup("
java:comp/env/jdbc/A
");


conn = ds.getConnection();


stmt = conn.createStatement();


Result rs = stmt.executeQuery("select ename from emp where empno = " + id);


name = rs.getString("ename");


rs.close();


}


finally{


if(stmt != null) try{stmt.close();}catch(Exception e){}


if(conn != null) try{conn.close();}catch(Exception e){}


}


return name;

}

public String
getDeptName
(String id) throws Exception {


Connection conn = null; Statement stmt = null; String name = null;


try{


javax.sql.DataSource ds =


(javax.sql.DataSource)ctx.lookup("
java:comp/env/jdbc/A

");


conn = ds.getConnection();


stmt = conn.createStatement();


Result rs = stmt.executeQuery("select deptname from dept where deptno = " + id);


name = rs.getString("deptname");


rs.close();


}


finally{


if(stmt != null) try{stmt.close();}catch(Exception e){}


if(conn != null) try{conn.close();}catch(Exception e){}


}


return name;

}

Database B

Resource

Manager

JDBC

connection pool

request end within a thread boundary

Shareable Connection

allocate

release

6

public void method() throws Exception {


String name =
getEmployeeName
("7904");


String dept =
getDeptName
("1234");

}

public String
getEmployeeName
(String id) throws Exception {


Connection conn = null; Statement stmt = null; String name = null;


try{


javax.sql.DataSource ds =


(javax.sql.DataSource)ctx.lookup("
java:comp/env/jdbc/A
");


conn = ds.getConnection();


stmt = conn.createStatement();


Result rs = stmt.executeQuery("select ename from emp where empno = " + id);


name = rs.getString("ename");


rs.close();


}


finally{


if(stmt != null) try{stmt.close();}catch(Exception e){}


if(conn != null) try{conn.close();}catch(Exception e){}


}


return name;

}

public String
getDeptName
(String id) throws Exception {


Connection conn = null; Statement stmt = null; String name = null;


try{


javax.sql.DataSource ds =


(javax.sql.DataSource)ctx.lookup("
java:comp/env/jdbc/A

");


conn = ds.getConnection();


stmt = conn.createStatement();


Result rs = stmt.executeQuery("select deptname from dept where deptno = " + id);


name = rs.getString("deptname");


rs.close();


}


finally{


if(stmt != null) try{stmt.close();}catch(Exception e){}


if(conn != null) try{conn.close();}catch(Exception e){}


}


return name;

}

Database B

Resource

Manager

JDBC

connection pool

Unshareable Connection

allocate

release

release

allocate

7

XA and non
-
XA JDBC Driver Provider

Non
-
XA JDBC Driver Provider

javax.naming.InitialContext ctx = new javax.naming.InitialContext();

javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup(“
java:comp/env/jdbc/MyDB
");

java.sql.Connection conn = ds.getConnection();

boolean mode = conn.getAutoCommit();


true

XA JDBC Driver Provider

javax.naming.InitialContext ctx = new javax.naming.InitialContext();

javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("
java:comp/env/jdbc/MyDB

");

java.sql.Connection conn = ds.getConnection();

boolean mode = conn.getAutoCommit();


false

Non
-
XA JDBC Driver Provider


: 1
-
phase commit resource


XA JDBC Driver Provider


: 2
-
phase commit resource

8

Non
-
XA JDBC

Resource

EJB

Servlet/JSP

call()

Database A

WebSphere Application Server

ctx.lookup("
java:comp/env/jdbc/A
");

ctx.lookup("
java:comp/env/jdbc/A
");

Question : If

-

Unshareable Connection

-

Non
-
XA JDBC Provider

Shareable needed

Global Transaction error with 1pc resource !!

9

XA JDBC Provider : 2 phase
-
commit

XA Resource

manager

XA Resource

manager

Transaction

manager

User

Application

tx.begin()

tx.commit()

Database A

Database B

1 phase: prepare

2 phase: commit/rollback

1 phase: in
-
doubt

2 phase: commit/rollback

tranlog

WebSphere Application Server

10

XA JDBC Provider : 2 phase
-
commit

XA Resource

manager

XA Resource

manager

EJB1

Database A

Database B

EJB2

Transaction propagation

11


java.sql.Connection conn1 = null;


java.sql.Statement stmt1 = null;


java.sql.Connection conn2 = null;


java.sql.Statement stmt2 = null;




javax.transaction.UserTransaction tx = null;


try {


javax.naming.InitialContext ctx = new javax.naming.InitialContext();


tx = (javax.transaction.UserTransaction) ctx.lookup("java:comp/UserTransaction");


tx.begin();



//
-------------------------------------------------------------------------


javax.sql.DataSource ds1 = (javax.sql.DataSource)ctx.lookup("
java:comp/env/jdbc/A
");


conn1 = ds1.getConnection();


stmt1 = conn1.createStatement();


stmt1.executeUpdate("update emp set ename = 'LWY" + count + "' where empno = 7934");


//
-------------------------------------------------------------------------


javax.sql.DataSource ds2 = (javax.sql.DataSource)ctx.lookup("
java:comp/env/jdbc/B
");


conn2 = ds2.getConnection();


stmt2 = conn2.createStatement();


stmt2.executeUpdate("update emp set ename = 'LWY" + count + "' where empno = 7934");



//
-------------------------------------------------------------------------


tx.commit();


}


catch(Exception e){


if ( tx != null ) try{tx.rollback();}catch(Exception ee){}


}


finally {


if ( stmt1 != null ) try { stmt1.close();}catch(Exception e){}


if ( conn1 != null ) try { conn1.close();}catch(Exception e){}



if ( stmt2 != null ) try { stmt2.close();}catch(Exception e){}


if ( conn2 != null ) try { conn2.close();}catch(Exception e){}


}


2 phase commit sample (XA JDBC Datasource)

Question :

How about in EJB?

CMT/BMT

Database A

Database B