java - ORA-12519, TNS:no appropriate service handler found while inserting into Oracle Database with X threads -

java - ORA-12519, TNS:no appropriate service handler found while inserting into Oracle Database with X threads -

i trying insert oracle database has 2 columns-

id primary key varchar2 (4000) business relationship varchar2 (4000)

i wrote multithreaded programme that. , each thread using unique id every time insert id column id primary key.

the problem facing @ point is- below code, throws next exception after running few seconds.

1) null pointer exception 2) java.sql.sqlexception: listener refused connection next error:ora-12519, tns:no appropriate service handler found

i not able find root cause of problem in code looking me. closing each , every connection properly. how npe getting thrown , other exception well?

executorservice service = executors.newfixedthreadpool(10); seek { // queue tasks (int = 0; < 100 * 10; i++) { service.submit(new threadtask()); } service.shutdown(); service.awaittermination(long.max_value, timeunit.seconds); while (!service.isterminated()) { } } grab (interruptedexception e) { log.warn("threw interrupted exception in" + xmploadtest.class.getsimplename() + ".xmploadtest: boss told me stop...not fault!!"); }

below threadtask class-

class threadtask implements runnable { private static final string driver = "oracle.jdbc.driver.oracledriver"; private static final string connection = "jdbc:oracle:thin:@localhost:1521:orcl"; private static final string user = "scott"; private static final string password = "tiger"; private static connection dbconnection = null; private static preparedstatement preparedstatement = null; private static final atomicinteger id = new atomicinteger(1); private final static logger log = logger.getlogger(threadtask.class.getname()); public threadtask() { } @override public void run() { seek { dbconnection = getdbconnection(); preparedstatement = dbconnection.preparestatement(constants.insert_oracle_sql); preparedstatement.setstring(1, string.valueof(id.getandincrement())); preparedstatement.setstring(2, constants.a_account); preparedstatement.executeupdate(); } grab (exception e) { // npe getting thrown here/and sec exception log.error("threw sqlexception in " + getclass().getsimplename(), e); } { if (preparedstatement != null) { seek { preparedstatement.close(); preparedstatement = null; } grab (sqlexception e) { //oouch... log.error("threw sqlexception in block of prepared statement " + getclass().getsimplename(), e); } } if (dbconnection != null) { seek { dbconnection.close(); dbconnection = null; } grab (sqlexception e) { //better go , sql. log.error("threw sqlexception in block of dbconnection " + getclass().getsimplename(), e); } } } } /** * attempts found connection given database url * * @return db connection */ private connection getdbconnection() { connection dbconnection = null; seek { class.forname(xmp_driver); dbconnection = drivermanager.getconnection(connection, user, password); } grab (classnotfoundexception e) { log.error("threw classnotfoundexception in " + getclass().getsimplename(), e); } grab (sqlexception e) { //damn! i'm not.... log.error("threw sqlexception in " + getclass().getsimplename(), e); } grab (exception e) { log.error("threw exception in " + getclass().getsimplename(), e); } homecoming dbconnection; } }

is there potential problem here code? more worried npe.

stacktrace:

19:14:28,372 error threadtask:187 - threw sqlexception in threadtask java.sql.sqlexception: listener refused connection next error: ora-12519, tns:no appropriate service handler found @ oracle.jdbc.driver.t4cconnection.logon(t4cconnection.java:458) @ oracle.jdbc.driver.physicalconnection.<init>(physicalconnection.java:546) @ oracle.jdbc.driver.t4cconnection.<init>(t4cconnection.java:236) @ oracle.jdbc.driver.t4cdriverextension.getconnection(t4cdriverextension.java:32) @ oracle.jdbc.driver.oracledriver.connect(oracledriver.java:521) @ java.sql.drivermanager.getconnection(drivermanager.java:322) @ java.sql.drivermanager.getconnection(drivermanager.java:358) @ com.ebay.xmp.lnp.threadtask.getdbconnection(xmploadtest.java:179) @ com.ebay.xmp.lnp.threadtask.run(xmploadtest.java:137) @ java.util.concurrent.executors$runnableadapter.call(executors.java:452) @ java.util.concurrent.futuretask$sync.innerrun(futuretask.java:314) @ java.util.concurrent.futuretask.run(futuretask.java:149) @ java.util.concurrent.threadpoolexecutor$worker.runtask(threadpoolexecutor.java:897) @ java.util.concurrent.threadpoolexecutor$worker.run(threadpoolexecutor.java:919) @ java.lang.thread.run(thread.java:736) caused by: oracle.net.ns.netexception: listener refused connection next error: ora-12519, tns:no appropriate service handler found @ oracle.net.ns.nsprotocol.connect(nsprotocol.java:395) @ oracle.jdbc.driver.t4cconnection.connect(t4cconnection.java:1102) @ oracle.jdbc.driver.t4cconnection.logon(t4cconnection.java:320) ... 14 more 19:14:28,376 error threadtask:139 - threw sqlexception in threadtask java.lang.nullpointerexception @ com.ebay.xmp.lnp.threadtask.run(xmploadtest.java:137) @ java.util.concurrent.executors$runnableadapter.call(executors.java:452) @ java.util.concurrent.futuretask$sync.innerrun(futuretask.java:314) @ java.util.concurrent.futuretask.run(futuretask.java:149) @ java.util.concurrent.threadpoolexecutor$worker.runtask(threadpoolexecutor.java:897) @ java.util.concurrent.threadpoolexecutor$worker.run(threadpoolexecutor.java:919) @ java.lang.thread.run(thread.java:736)

the race status simplest explanation because in current code there guarantee dbconnection exists non-null value when multiple threads running.

to put, every threadtask object has access static connection dbconnection class field. if evaluation take there 3 threads started during applications initiation, , next happens:

main+----------------------------------------------------------------------(t) |+thread1 --dbconnection=getdbconnection()-------------dbconnection=null| | | |----------+thread2 --dbconnection=getdbconnection()--------------------|dbconnection=null | |-------------+thread3--dbconnection=getdbconnection()------------------|preparedstatement.executeupdate()

at time (t), since static variable set null thread1, thread3 throw exception.

* update *

you need create utilize of connection pooling, check out c3p0

* end update *

java multithreading oracle executorservice ora-12519