Re: LOCK TABLE oddness in PLpgSQL function called via

At 02:22 PM 10/2/2001, Tom Lane wrote:
>Dave Harkness <daveh@MEconomy.com> writes:
> > The problem I'm seeing is that two database transactions,
> > initiated via JDBC, are able to obtain simultaneous exclusive table locks
> > on the same table.
>
>Sounds to me like JDBC is feeding all your commands through a single
>database connection, which means that what you think are independent
>transactions are really not.  Better take a closer look at what you're
>doing.

My test code creates multiple test threads and then starts them each. Each
test thread (IDFactoryThread) creates its own Connection and IDFactory
(which gets the connection). The test thread itself simply calls
IDFactory.nextID() in a loop. I'm not using any connection pooling
whatsoever. I'm using the built-in PostgreSQL JDBC driver alone.

Here's the code:

     public static void test ( int numThreads , String nameKey )
     {
       Thread[]      threads = new Thread[numThreads];

       for ( int i = 0 ; i < numThreads ; i++ )
       {
         threads[i] = new IDFactoryThread(i, nameKey);
       }

       for ( int i = 0 ; i < numThreads ; i++ )
       {
         threads[i].start();
       }
     }

     class IDFactoryThread extends Thread
     {
       Connection      conn = null;
       IDFactorySQL    factory = null;

       public IDFactoryThread ( int index , String nameKey )
       {
         super(Integer.toString(index));
         init(nameKey);
       }

       public void init ( String nameKey )
       {
         try
         {
           conn = DriverManager.getConnection(IDFactorySQLTest.DB_URL);
         }
         catch ( SQLException e )
         {
           System.out.println("Could not connect to the database");
           e.printStackTrace();
           System.exit(1);
         }

         factory = new IDFactorySQL(conn, nameKey,
IDFactorySQLTest.BLOCK_SIZE);
       }

       public void run ( )
       {
         try
         {
           for ( int i = 0 ; i < IDFactorySQLTest.LOOP_COUNT ; i++ )
           {
             System.out.println(getName() + " - " + factory.next());
           }
         }
         catch ( IllegalStateException e )
         {
           e.printStackTrace();
           System.exit(1);
         }

         factory.close();
       }
     }

Thanks again!

Peace,
Dave