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

I think the processes (threads?) p1 and p2 are interleaving like this ...

p1:step1 -> read 0 rows
p2:step1 -> read 0 rows
p1:step2
p2:step2
p1:step3 -> insert a row because 0 rows were read
p2:step3 -> insert a row because 0 rows were read

I want p2:step1 to be blocked until the end of p1:step3 and so it would look like this ...

p1:step1 -> read 0 rows
p2:step1 -> blocked
p1:step2
p1:step3 -> insert a row because 0 rows were read
p2:step1 -> resumes, reads 1 row
p2:step2
p2:step3 -> dont insert a row because 1 row was read

I thought if I use the postgres "select for update" in step1 and surrounded all three steps with a db_transaction then this would block p2 until p1 completes. But this doesnt seem to work (I'm not using any db_foreach).

I will check on the use of the db handles as suggested in earlier replies.

You're right !
As a result I've had to re-write the page logic, so no real need for db_transaction.

Thanks for everyone's help.