PostgreSQL JDBC - Deadlock problem

Author

Deadlock problem

Vit Timchishin

2005-11-18, 1:23 pm

Hello.

I have an multithreaded java application using postgresql. I am using UR mode (handling locking internally)
and wa shoping to have no problems with locks.
But now I have locking porlbme where there is only one thread calling posgresql and locking in wait.
Looking at pg_lock I can see a lot of locks each holding exclusive a transaction (other two fields are empty).
And for one transaction there is one PID holding Exclusive Lock and another waiting for shared lock for
same transaction.
I suppose that this means that I've first used connection object from one thread and this thread pid (linux
x86-64) took exclusive lock and now another thread tries to use same connection and is going into lock.
Am I correct? If so, why this exclusive locks are help while there no other queries executed? Or does this
mean I can't use same connection object from different threads?

ó Õ×ÁÖÅÎÉÅÍ,
÷ÉÔÁÌÉÊ ÷ÁÌÅÒÉÅ×ÉÞ ôÉÍÞÉÛÉÎ,
ôÅÈÎÉÞÅÓËÉÊ äÉÒÅËÔÏÒ
ïïï "çÏÌÄÅÎ ôÅÈÎÏÌÏÄÖÉÓ"
http://www.gtech-ua.com

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Kris Jurka

2005-11-18, 1:23 pm

On Fri, 18 Nov 2005, Vit Timchishin wrote:

> I have an multithreaded java application using postgresql. I am using UR
> mode (handling locking internally) and wa shoping to have no problems
> with locks. But now I have locking porlbme where there is only one
> thread calling posgresql and locking in wait. Looking at pg_lock I can
> see a lot of locks each holding exclusive a transaction (other two
> fields are empty). And for one transaction there is one PID holding
> Exclusive Lock and another waiting for shared lock for same transaction.
> I suppose that this means that I've first used connection object from
> one thread and this thread pid (linux x86-64) took exclusive lock and
> now another thread tries to use same connection and is going into lock.
> Am I correct? If so, why this exclusive locks are help while there no
> other queries executed? Or does this mean I can't use same connection
> object from different threads?

I'm a little bit unclear on how you are using connections. Does your
application have only one Connection object that it shares among various
threads? If this is the case the postgresql jdbc driver will block a
thread when another thread is executing a statement from the same
connection. The server does not support multiplexing queries so the
driver can only allow one to execute at any given time. If this is the
case you may want to open more than one Connection in your application.
This situation cannot cause a block on the server side because a
Connection cannot block itself. If you are blocked on the server side
(with only one Connection) then you must be waiting for another
connection's resources, perhaps from another application or maintenence
command.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Kris Jurka

2005-11-18, 8:24 pm

On Fri, 18 Nov 2005, Andrew Sullivan wrote:

> If this connection is being actively used by more than one thread
> concurrently, things are going to break in really surprising ways.
>

The JDBC driver is threadsafe so multiple threads may make calls
simultaneously, but internally only one is executed at a time. Clearly
running with autocommit = false requires some coordination of
transactions, but multiple threads are not in and of themselves dangerous.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Andrew Sullivan

2005-11-18, 8:24 pm

On Fri, Nov 18, 2005 at 02:49:53PM -0500, Kris Jurka wrote:
> simultaneously, but internally only one is executed at a time. Clearly
> running with autocommit = false requires some coordination of
> transactions, but multiple threads are not in and of themselves dangerous.

I'd _really_ like to see an example of an application that did this
correctly and without any bugs. I've seen people do it, yes; but I
haven't seen it working without fairly serious problems. (Besides,
isn't solving this sort of pain what a pool is for? Why reinvent the
wheel, and make it square to boot?)

A

--
Andrew Sullivan | ajs@crankycanuck.ca
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Andres Olarte

2005-11-19, 8:23 pm

There's also the option of using a queue that's accessed by various threads
and a single thread with a single connection that actually talks with the
DB. I've been sucessfull with this aproach, at least in a very particular
and well defined scenario. As for reiventing the wheel, it's probably very
pointless and prone to erros like Andrew mentions. The DB has transactions,
they work well, use them to your advantage.

Andres

Vit Timchishin

2005-11-21, 1:23 pm

On Fri, 18 Nov 2005 13:31:23 -0500 (EST), Kris Jurka wrote:

>
>
>On Fri, 18 Nov 2005, Vit Timchishin wrote:
>
>
>I'm a little bit unclear on how you are using connections. Does your
>application have only one Connection object that it shares among various
>threads? If this is the case the postgresql jdbc driver will block a
>thread when another thread is executing a statement from the same
>connection. The server does not support multiplexing queries so the
>driver can only allow one to execute at any given time. If this is the
>case you may want to open more than one Connection in your application.
>This situation cannot cause a block on the server side because a
>Connection cannot block itself. If you are blocked on the server side
>(with only one Connection) then you must be waiting for another
>connection's resources, perhaps from another application or maintenence
>command.

I am not using one connection and at the time of block there is only one active query (that is locked) at the
whole database (in my test case). But for one transaction it may be used by different java threads (e.g. main
thread and finalizer) and it seems that this is producing problems because exclusive lock is held after
statement have finished. It is possible that I am still having open resultsets (did not check), but they are all
forward only and not updateable.

ó Õ×ÁÖÅÎÉÅÍ,
÷ÉÔÁÌÉÊ ÷ÁÌÅÒÉÅ×ÉÞ ôÉÍÞÉÛÉÎ,
ôÅÈÎÉÞÅÓËÉÊ äÉÒÅËÔÏÒ
ïïï "çÏÌÄÅÎ ôÅÈÎÏÌÏÄÖÉÓ"
http://www.gtech-ua.com

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Kris Jurka

2005-11-21, 8:24 pm

On Mon, 21 Nov 2005, Vit Timchishin wrote:

> I am not using one connection and at the time of block there is only one
> active query (that is locked) at the whole database (in my test case).
> But for one transaction it may be used by different java threads (e.g.
> main thread and finalizer) and it seems that this is producing problems
> because exclusive lock is held after statement have finished. It is
> possible that I am still having open resultsets (did not check), but
> they are all forward only and not updateable.
>

Locks are always held until transaction commit, not the end of an
individual statement. So, while you may only have one statement executing
you have more than one transaction in progress and this is causing your
deadlocks. Consider a table with a primary key:

CREATE TABLE t(a int primary key);

Connection 1:
BEGIN;
INSERT INTO t VALUES (1);

Connection 2:
BEGIN;
INSERT INTO t VALUES (1);

Connection 2 must wait for connection 1 to either commit or rollback
before it knows whether it can legally insert its value. This is true
even if connection 1 performed its insert a week ago, the transaction is
still in doubt even if the statement has completed running.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Vit Timchishin

2005-11-22, 9:23 am

On Mon, 21 Nov 2005 18:06:37 -0500 (EST), Kris Jurka wrote:

>
>
>On Mon, 21 Nov 2005, Vit Timchishin wrote:
>
>
>Locks are always held until transaction commit, not the end of an
>individual statement. So, while you may only have one statement executing
>you have more than one transaction in progress and this is causing your
>deadlocks.

This is not the problem. I know good about locks, but this one:
relation | database | transaction | pid | mode | granted
----------+----------+-------------+-------+-----------------+---------
| | 1109601 | 32172 | ExclusiveLock | t

1) Not related to any relation. Relation and database fields are null
2) Intertransaction - transaction field of lock that is granted (example above) and that is trying to lock are
equal. The only difference is the pid column (and that the lock that is trying is SharedLock and granted = f).

ó Õ×ÁÖÅÎÉÅÍ,
÷ÉÔÁÌÉÊ ÷ÁÌÅÒÉÅ×ÉÞ ôÉÍÞÉÛÉÎ,
ôÅÈÎÉÞÅÓËÉÊ äÉÒÅËÔÏÒ
ïïï "çÏÌÄÅÎ ôÅÈÎÏÌÏÄÖÉÓ"
http://www.gtech-ua.com

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links