Forum OpenACS Q&A: how do you get locking transactions to work?

The logic of my page is as follows:
1) test for uniqueness by using db_0or1row on table X
2) some more stuff
3) if zero rows returned from 1) then insert into table X

This code is executed multiple times in quick succession and the query at step 1 always indicates that there are no matching rows and so step 3 inserts as many rows as page accesses. I'm expecting only one row to be inserted no matter how many times or how quickly the page is accessed.

I have tried ...
* using "select for update" in step 1
* putting db_transaction around all three steps (with or without the "select for update"
* putting "db_dml b {begin}" and "db_dml c {commit}" around all three steps (testing in psql, I can get the "select for update" to lock the table if I put begin;/commit; around the select)

... but I always get more than one row inserted at step 3

How do I make steps 1 to 3 into a "transaction" that locks out other page accesses ?

(this is OACS 5.2 on Postgres)

thanks,
Robert

If this is the case simply get your rows with a db_list_of_lists and then loop over them with a foreach.

I believe you need to set some kind of "globally accessible" flag right after or during the 1st query. The "obvious" solution would be to insert the row immediately - with dummy values if the real ones are not available.

Alternatively, you can use LOCK TABLE - but this would exclude ALL other processes, which might be undesirable.