Обсуждение: Problems ensuring uniqueness?

Hi there. I'm currently using Postgresql 7.0.3, accessing using the JDBC
drivers. I'm enforcing the most severe transaction isolation available
(serializable). I'm currently doing something like the following (this
is pseudo code, with the if being performed in Java):

begin
lock table foo in access exclusive mode
select count(*) from foo where key1 = bar, key2 = baz

if count > 1
    rollback
else
    insert into foo (key1, key2, other) values (bar, baz, stuff)
    commit

Now, table foo has a primary key made up of key1 & key2. Without
concurrent access, the code works great. However, provided I use enough
threads, I inevitably get back errors from the database indicating that
I've tried to insert a record which violates the uniquness of the
primary key.

Is there away to avoid this error by doing my SQL & locking properly, or
should I just catch the exception and deal with it?

--Chris

On 18 Jun 2001 17:42:15 -0400, Tom Lane wrote:
> Christopher Smith <x@xman.org> writes:
> Um ... surely that should be "if count > 0" ?  Or was that just a
> transcription error?
>
> This approach certainly ought to work as desired given the exclusive
> lock, so a silly typo seems like a plausible explanation...

Sorry, it is indeed a transcription error (sadly). The actual line in
question looks like this in Java:

boolean answer = resultSetCount.getInt(1) > 0;

I accidently transcribed the field offset instead of the comparison
constant.... probably should split that up into two lines of code to
avoid confusion like this...

--Chris

Christopher Smith <x@xman.org> writes:
> begin
> lock table foo in access exclusive mode
> select count(*) from foo where key1 = bar, key2 = baz

> if count > 1
>     rollback
> else
>     insert into foo (key1, key2, other) values (bar, baz, stuff)
>     commit

> Now, table foo has a primary key made up of key1 & key2. Without
> concurrent access, the code works great. However, provided I use enough
> threads, I inevitably get back errors from the database indicating that
> I've tried to insert a record which violates the uniquness of the
> primary key.

Um ... surely that should be "if count > 0" ?  Or was that just a
transcription error?

This approach certainly ought to work as desired given the exclusive
lock, so a silly typo seems like a plausible explanation...

            regards, tom lane

Christopher Smith <x@xman.org> writes:
>> Um ... surely that should be "if count > 0" ?  Or was that just a
>> transcription error?
>>
>> This approach certainly ought to work as desired given the exclusive
>> lock, so a silly typo seems like a plausible explanation...

> Sorry, it is indeed a transcription error (sadly).

Oh well.  The next thought, given that you mention threads, is that
you've got multiple threads issuing commands to the same backend
connection; in which case the interlocking you think you have doesn't
exist at all...

            regards, tom lane

On 18 Jun 2001 18:04:14 -0400, Tom Lane wrote:
> Christopher Smith <x@xman.org> writes:
> >> Um ... surely that should be "if count > 0" ?  Or was that just a
> >> transcription error?
> >>
> >> This approach certainly ought to work as desired given the exclusive
> >> lock, so a silly typo seems like a plausible explanation...
>
> > Sorry, it is indeed a transcription error (sadly).
>
> Oh well.  The next thought, given that you mention threads, is that
> you've got multiple threads issuing commands to the same backend
> connection; in which case the interlocking you think you have doesn't
> exist at all...

You got it bang on... I thought I had isolated access to the connections
properly, but shortly after posting that last e-mail, had a eureka moment.
I presume this will make the bug go away, so I'll encourage everyone to
ignore this thread (other than as a warning ;-) until I've confirmed I
still have the problem after making the correct adjustments.

--Chris