Hello,
I'm trying to use the 8 threads from my new processor to handle transactions on the PostgreSQL database. It have to process geographic data in PostGIS, what I already do with just 1 processor core (one thread). I'm using Java (JDBC4) to create one Connection for each thread. Each connection receives the job to process groups of geometric entities, where one SELECT and one UPDATE statements are used for each entity. Each entity is processed by unique ID and no relation functions are used, so there is no dependencies between the transactions.
The application can be started to run with a variable number of threads. When I run it, all except one of the threads hang. Even if I try to run with just two threads, one hangs. With the "Server status" tool from pgAdmin3 I can see that all the hanging threads are "IDLE in transaction", some in "ExclusiveLock" mode, some in "RowExclusiveLock" mode and some in "AccessShareLock" mode.
I've adjusted my postgresql.conf as described in http://jayant7k.blogspot.com/2010/06/postgresql-tuning-quick-tips.html
I've tried to put the threads to sleep for a while right after the UPDATE statement with no success.
Why are the locks been created? Is there a way to avoid these locks, once that are no reasons to a query depend on other?
Thanks for any help