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)


Posted by Derick Leony on
db_transaction should work, just make sure you use it like
db_transaction {
//step 1
//step 2
//step 3

and that you are using the same dbn that your db_0or1row and db_dml are using.



Posted by Claudio Pasolini on
In the past I noticed that doing any db_dml within a db_foreach doesn't work as a transaction, because they use a different handle.

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

Posted by Andrew Piskorski on
Derick's and Claudio's comments are exactly right, you should never use more than one database handle within a single db_transaction block. Breaking that rule is a problem that periodically pops up here in the forums. A good fix would be to implement this: db_transaction should throw error for multiple database handles.

It's not clear to me that this actually had anything directly to do with Robert's problem, but it's good advice regardless.

Claudio, would you mind if you add this comment to the db_dml or db_foreach procedure definition / api documentation? This is a valuable piece of information that should get in there.
Posted by Nis Jørgensen on
The problem is that a transaction (even if done right) doesn't do what you want - you WANT the 2nd process to see the row created by the 1st, even if the 1st haven't completed (or indeed, inserted the row yet).

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.

Posted by robert parker on
I think the processes (threads?) p1 and p2 are interleaving like this ...

p1:step1 -> read 0 rows
p2:step1 -> read 0 rows
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:step3 -> insert a row because 0 rows were read
p2:step1 -> resumes, reads 1 row
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.

"SELECT FOR UPDATE" will lock the rows selected - in this case none.
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.