Thread: RE: [INTERFACES] Transaction support in 6.5.3/JDBC

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, December 08, 1999 9:07 AM
To: Peter Mount
Cc: 'Assaf Arkin'; pgsql-interfaces@hub.org
Subject: Re: [INTERFACES] Transaction support in 6.5.3/JDBC 


Peter Mount <petermount@it.maidstone.gov.uk> writes:
> 1. When a dead-lock occurs trying to update the same row from multiple
> threads, there does not seem to be any timeout and both connections
hang
> forever. Is there any dead-lock detection in 6.5 and will there be on
in
> 6.6 or 7.0?

> PM: I'm not sure if there is/will be in the backend. I'm planning on
> making the OOB abort stuff working for 7.0, but I'm not certain on
> dead-lock's.

This doesn't seem right to me --- the backend should detect deadlocks
(not right away, but within a few seconds or a minute at most).  If
it doesn't, that's not JDBC's fault.  More details please?

PM: I didn't think it was.

> 2. In the event of such a dead-lock, the XA layer will attempt to
> terminate one of the connections. Right now the only recourse is to
> shutdown the connection forcefully, hoping that the transaction is
> rolledback and all locks are released. Am I safe to assume that?

> PM: Someone correct me if I'm wrong, but the transaction should roll
> back if the connection is closed whilst it's open, regardless of the
> interface.

Right.  Again, this'd be a backend bug if it didn't happen.  The backend
isn't supposed to rely on correct frontend behavior to ensure database
integrity.

PM: That's what I thought was the case.

> 3. I would rather cancel the pending update/insert, and according to
the
> interface specs there is a way to recieve a pid/key on startup and use
> it to cancel an operation in progress. However, at the end of the
> authentication process that happens at startup, no pid/key are send to
> the FE, nor does the BE acknowledge that a query can be made.

> PM: Do you mean the pid of the backend running a particular
connection?
> If so, I can see some security headaches if one connection can cancel
an
> operation running on another. As for the BE not acknowledging that a
> query can be made, thats just how the current protocol works.

This one maybe can be blamed on JDBC.  In the 2.0 protocol the BE
will send a cancel security key --- but I seem to recall that the
JDBC client still requests protocol 1.0?

PM: In theory 6.5.3 should be requesting the current protocol (I
remember the patch being submitted to me as part of another fix).
However, I haven't (yet) had chance to look at it yet - hence not
knowing about the security key. The bit I want to add is for JDBC2,
ResultSet would by default use a cursor, and if it's closed while a read
is in effect, it would send cancel to the backend.

Peter
        regards, tom lane

************

> Peter Mount <petermount@it.maidstone.gov.uk> writes:
> > 1. When a dead-lock occurs trying to update the same row from multiple
> > threads, there does not seem to be any timeout and both connections
> hang
> > forever. Is there any dead-lock detection in 6.5 and will there be on
> in
> > 6.6 or 7.0?
> 
> > PM: I'm not sure if there is/will be in the backend. I'm planning on
> > making the OOB abort stuff working for 7.0, but I'm not certain on
> > dead-lock's.
> 
> This doesn't seem right to me --- the backend should detect deadlocks
> (not right away, but within a few seconds or a minute at most).  If
> it doesn't, that's not JDBC's fault.  More details please?
> 
> PM: I didn't think it was.

In my opinion the back-end should detect deadlocks, or just connections
that hang too long trying to get something done, and have them properly
terminated.

I have a very simple test. I start two threads, both of them attempt to
do an update on the exact same row in the table, both connections keep
hanging.


> Right.  Again, this'd be a backend bug if it didn't happen.  The backend
> isn't supposed to rely on correct frontend behavior to ensure database
> integrity.
> 
> PM: That's what I thought was the case.

Good.


> This one maybe can be blamed on JDBC.  In the 2.0 protocol the BE
> will send a cancel security key --- but I seem to recall that the
> JDBC client still requests protocol 1.0?
> 
> PM: In theory 6.5.3 should be requesting the current protocol (I
> remember the patch being submitted to me as part of another fix).
> However, I haven't (yet) had chance to look at it yet - hence not
> knowing about the security key. The bit I want to add is for JDBC2,
> ResultSet would by default use a cursor, and if it's closed while a read
> is in effect, it would send cancel to the backend.

I'll try and see if I can get change the protocol and get the pid/key.

I'm only using the pid/key internally, so I don't see the possibility of
someone else interfering. It's not exposed to the code using the JDBC
driver. While the pid is known, the key is no known so one cannot
terminate an operation without creating the connection first. It's up to
the key to be reasonably unguessable.

arkin

> 
> Peter
> 
>                         regards, tom lane
> 
> ************
> 
> ************

-- 
____________________________________________________________
Assaf Arkin                               arkin@exoffice.com
CTO                                  http://www.exoffice.com
Exoffice, The ExoLab Company             tel: (650) 259-9796

Assaf Arkin <arkin@exoffice.com> writes:
> I have a very simple test. I start two threads, both of them attempt to
> do an update on the exact same row in the table, both connections keep
> hanging.

Hmm.  The backend doesn't have any trouble detecting this; I checked
both current sources and 6.5.3:

play=> select * from z1;
f1
--12
(2 rows)

play=> begin;
BEGIN
play=> update z1 set f1 = 3 where f1 = 1;
UPDATE 1

<now in a second psql do>

play=> begin;
BEGIN
play=> update z1 set f1 = 4 where f1 = 2;
UPDATE 1
play=> update z1 set f1 = 3 where f1 = 1;

<second psql is now waiting for first one to commit or abort>

<back in first psql do>

play=> update z1 set f1 = 4 where f1 = 2;

After a second or so, one psql will say
NOTICE:  Deadlock detected -- See the lock(l) manual page for a possible cause.
ERROR:  WaitOnLock: error on wakeup - Aborting this transaction
and the other will say
UPDATE 1

If you're not getting this behavior, then either your test code is wrong
or there's something broken in the JDBC driver...
        regards, tom lane