ORA-12519，TNS:在使用X线程插入Oracle数据库时没有找到合适的服务处理程序

Question

我正在尝试插入Oracle数据库，其中有两列-

ID         Primary Key     varchar2 (4000)
ACCOUNT                    varchar2 (4000)

我为此编写了一个多线程程序。每个线程每次都使用unique id插入到ID column中，因为ID是主键。

我在某个时候面临的唯一问题是-下面的代码，在运行几秒钟后抛出以下异常。

 1) Null Pointer Exception
 2) java.sql.SQLException: Listener refused the connection with the following error:ORA-12519, TNS:no appropriate service handler found

我无法在代码中找到这个问题的根本原因，因为我觉得一切都很好。因为我正在正确地关闭每一个连接。那么NPE是如何被抛出和其他异常的呢？

    ExecutorService service = Executors.newFixedThreadPool(10);

    try {
        // queue some tasks
        for (int i = 0; i < 100 * 10; i++) {
            service.submit(new ThreadTask());
        }
        service.shutdown();
        service.awaitTermination(Long.MAX_VALUE, TimeUnit.SECONDS);

        while (!service.isTerminated()) {

        }
    } catch (InterruptedException e) {
        LOG.warn("Threw a Interrupted Exception in" + XMPLoadTest.class.getSimpleName()
                + ".XMPLoadTest: boss told me to stop...Not my fault!!");
    }

下面是ThreadTask类-

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() {

        try {

            dbConnection = getDBConnection();
            preparedStatement = dbConnection.prepareStatement(Constants.INSERT_ORACLE_SQL);

            preparedStatement.setString(1, String.valueOf(id.getAndIncrement()));
            preparedStatement.setString(2, Constants.A_ACCOUNT);

            preparedStatement.executeUpdate();

        } catch (Exception e) {
            // NPE getting thrown here/And second exception as well
            LOG.error("Threw a SQLException in " + getClass().getSimpleName(), e);
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                    preparedStatement = null;
                } catch (SQLException e) {
                    //Oouch...
                    LOG.error("Threw a SQLException in finally block of prepared statement " + getClass().getSimpleName(), e);
                }
            }
            if (dbConnection != null) {
                try {
                    dbConnection.close();
                    dbConnection = null;
                } catch (SQLException e) {
                    //Better go and look for SQL.
                    LOG.error("Threw a SQLException in finally block of dbConnection " + getClass().getSimpleName(), e);
                }
            }
        }
    }

    /**
     * Attempts to establish a connection to the given database URL
     * 
     * @return the db connection
     */
    private Connection getDBConnection() {

        Connection dbConnection = null;

        try {
            Class.forName(XMP_DRIVER);
            dbConnection = DriverManager.getConnection(CONNECTION, USER, PASSWORD);
        } catch (ClassNotFoundException e) {
            LOG.error("Threw a ClassNotFoundException in " + getClass().getSimpleName(), e);
        } catch (SQLException e) {
            //DAMN! I'm not....
            LOG.error("Threw a SQLException in " + getClass().getSimpleName(), e);
        } catch (Exception e) {
            LOG.error("Threw a Exception in " + getClass().getSimpleName(), e);
        }

        return dbConnection;
    }
}

我的代码有什么潜在的问题吗？我更担心这个NPE。

StackTrace:

19:14:28,372 ERROR ThreadTask:187 - Threw a SQLException in ThreadTask
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
    at java.sql.DriverManager.getConnection(DriverManager.java:322)
    at java.sql.DriverManager.getConnection(DriverManager.java:358)
    at com.ebay.xmp.lnp.ThreadTask.getDBConnection(XMPLoadTest.java:179)
    at com.ebay.xmp.lnp.ThreadTask.run(XMPLoadTest.java:137)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:452)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:314)
    at java.util.concurrent.FutureTask.run(FutureTask.java:149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:897)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:919)
    at java.lang.Thread.run(Thread.java:736)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:395)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)
    ... 14 more
19:14:28,376 ERROR ThreadTask:139 - Threw a SQLException in ThreadTask
java.lang.NullPointerException
    at com.ebay.xmp.lnp.ThreadTask.run(XMPLoadTest.java:137)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:452)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:314)
    at java.util.concurrent.FutureTask.run(FutureTask.java:149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:897)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:919)
    at java.lang.Thread.run(Thread.java:736)

Answer 1

争用条件是最简单的解释，因为在当前代码中，当多个线程运行时，可以保证dbConnection作为非空值存在。

简单地说，每个ThreadTask对象都可以访问static Connection dbConnection类字段。如果为了进行评估，我们认为在应用程序启动过程中启动了3个线程，并且发生了以下情况：

Main+----------------------------------------------------------------------(T)
    |+Thread1 --dbConnection=getDBConnection()-------------dbConnection=null|
    |                                                                       |
    |----------+Thread2 --dbConnection=getDBConnection()--------------------|dbConnection=null
    |
    |-------------+Thread3--dbConnection=getDBConnection()------------------|preparedStatement.executeUpdate()

在time (T)中，由于静态变量由null设置为Thread1，您的Thread3将抛出异常。

*更新*

您需要使用连接池，请查看c3p0。

*结束更新*

Answer 2

我只是遇到了同样的情况。

我的前提场景是:我在Eclipse中使用一个简单的Hibernate程序，其中包含一个insert操作。

这个异常过去发生在一些输入时，而通过我的HIBERNATE程序进行插入&在其他一些输入中没有发生。是啊听起来又蠢又怪。

我很困惑，我确实查看了谷歌的答案，堆栈，流等。这是一个非常简单的观察，拯救了我的整个一天！

如果你在日食中工作，如果你遇到同样的情况.解决方案:在Eclipse中，我只是关闭了“Console”选项卡中所有打开的控制台，这些控制台是在我多次执行d程序时生成的。他们被堆起来了&就像预期的那样，空间用完了。关闭他们解决了我的问题。这听起来确实是一个非常模糊的解决方案。没关系，它是观察为什么会发生这种情况，而且例外情况不会再次发生。

Eclipse映像