Обсуждение: 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