Re: LOCK TABLE oddness in PLpgSQL function called via JDBC

From: Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp>
To: Dave Harkness <daveh(at)meconomy(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: LOCK TABLE oddness in PLpgSQL function called via JDBC
Date: 2001-10-03 01:36:05
Message-ID: 3BBA6B85.B4A00020@tpf.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers pgsql-jdbc

Dave Harkness wrote:
>
> At 01:45 PM 10/2/2001, Barry Lind wrote:
> >Dave,
> >
> >Secondly, you don't need a table lock, you just need to lock the row
> >between the select and the update. You should use 'select for update' to
> >do this. That way when you issue the select to get the current value, it
> >will lock the row, preventing other select for update requests from
> >completing until the lock is released. That way the select and the update
> >can be assured that no one else is changing the data.
>
> THANK YOU! That's what I thought, but the documentation was a bit light on
> the subject of SELECT ... FOR UPDATE. So to mirror it back to you, if I do
>
> next_id_block ( count )
> (1) read idfactory row FOR UPDATE
>
> (2) update idfactory row
> increment next_id by count
> increment change_num by 1
> where change_num is equal to that read in (1)
>
> (3) return next_id read in (1)

As far as I see, this is a stored function issue not a Java
issue.
I got the exact code of the function from Dave.

create function next_id_block (
varchar , integer
)
returns bigint
as '
DECLARE
-- Parameters
name_key alias for $1 ;
block_size alias for $2 ;

-- Locals
id_rec record ;
num_rows integer ;
BEGIN
-- To avoid a retry-loop, lock the whole table for the
transaction
lock table idfactory in exclusive mode ;

-- Read the current value of next_id
select into id_rec * from idfactory where name = name_key ;

-- Increment it by block_size
update idfactory
set next_id = next_id + block_size,
change_num = change_num + 1
where name = name_key and change_num = id_rec.change_num ;

-- If the update failed, raise an exception
get diagnostics num_rows = ROW_COUNT ;
if num_rows != 1 then
raise exception ''Update failed'' ;
return -1 ;
end if ;

return id_rec.next_id ;
END ;
' language 'plpgsql' ;

The cause is that the stored function uses a common
snapshot throughout the function execution. As I've
complained many times, the current implementaion is
far from intuition and this case seems to show that
it isn't proper at all either.

*lock table* certainly locks idfactory table but the
subsequenct *select* sees the table using the snapshot
taken before the function call. The *update* statement
find the row matching the where clause using the common
snapshot but will find the row was already updated and
the updated row doesn't satisfy the condition any longer.

[In case when we remove the *lock* statement and add a
*for update* clause to the subsequent *select* statement]

The *select .. for update* statement gets the latest
(may be updated) change_num value. Unfortunately
the subsequent *update* statement has a where clause
containing change_num. The *update* statemnet can't
find the row matching the where clause using the snapshot
taken before the function call.

regards,
Hiroshi Inoue

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Bitmead 2001-10-03 01:52:35 More info needed on TEXT data type
Previous Message Mark Higgins 2001-10-03 01:26:06 Column existence - how to check?

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Lockhart 2001-10-03 01:54:15 Re: CVS changes
Previous Message Barry Lind 2001-10-03 01:13:14 Re: LOCK TABLE oddness in PLpgSQL function called via JDBC

Browse pgsql-jdbc by date

  From Date Subject
Next Message Ray Tomlinson 2001-10-03 01:54:26 Re: jdbc download jar is corrupted
Previous Message Barry Lind 2001-10-03 01:13:14 Re: LOCK TABLE oddness in PLpgSQL function called via JDBC