Skip site navigation (1) Skip section navigation (2)

Re: LOCK TABLE oddness in PLpgSQL function called via JDBC

From: Barry Lind <barry(at)xythos(dot)com>
To: Dave Harkness <daveh(at)MEconomy(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: LOCK TABLE oddness in PLpgSQL function called via JDBC
Date: 2001-10-03 01:03:26
Message-ID: 3BBA63DE.3030004@xythos.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackerspgsql-jdbc
Dave,

I don't know why you are seeing these problems with the lock table.  But 
the select for update should work for you.  (In my product I have done 
exactly the same thing you are trying to do using select for update with 
success).

I would add one minor comment on your description of the behavior of 
using select for update:

The select for update will block other 'select for updates' or 
'updates'.  It does not block other simple selects.  But that is fine 
for the purposes here.

thanks,
--Barry



Dave Harkness wrote:

> At 01:45 PM 10/2/2001, Barry Lind wrote:
> 
>> Dave,
>>
>> First off, are you running with autocommit turned off in JDBC?  By 
>> default autocommit is on, and thus your lock is removed as soon as it 
>> is aquired.
> 
> 
> I've tried it with auto-commit ON and OFF. With it off, I've tried it 
> with READ_COMMITTED and SERIALIZABLE. All produce the same result.
> 
> However, my understanding is that each JDBC statement is executed within 
> a single transaction when auto-commit is ON. I'm executing only one 
> statement:
> 
>     select next_id_block(?, ?)
> 
> While the function does indeed execute multiple statements itself, 
> aren't they all done inside a single transaction? If not, I must rethink 
> our strategy as I had assumed that the PLpgSQL functions I wrote would 
> be transactional.
> 
>> 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)
> 
> is it safe to assume that the update in (2) will ALWAYS succeed since it 
> would be impossible for any other transaction to read or update the row 
> once it was selected for update?
> 
> Thanks for your help.
> 
> Peace,
> Dave
> 



In response to

pgsql-hackers by date

Next:From: Tatsuo IshiiDate: 2001-10-03 01:11:03
Subject: Re: Unicode combining characters
Previous:From: Tatsuo IshiiDate: 2001-10-03 01:01:22
Subject: Re: Unicode combining characters

pgsql-jdbc by date

Next:From: Dave CramerDate: 2001-10-03 01:11:49
Subject: Re: driver fails to handle strings in query statements properly
Previous:From: Joe ShevlandDate: 2001-10-03 00:05:35
Subject: Re: jdbc download jar is corrupted

pgsql-general by date

Next:From: Barry LindDate: 2001-10-03 01:13:14
Subject: Re: LOCK TABLE oddness in PLpgSQL function called via JDBC
Previous:From: Tatsuo IshiiDate: 2001-10-03 01:01:16
Subject: Re: [GENERAL] Problem with the accents

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group