On Wednesday 22 February 2006 14:51, Oleg Broytmann wrote: > On Wed, Feb 22, 2006 at 02:48:42PM +0100, David Faure wrote: > > > Why do you want to lock the row with cacheValues=False? > > > > Because another process might be accessing the same table row at the same > > time. > > I do understand what locking is. I do not understand locking with > cacheValues=False. I am sure you do not need cacheValues=False with > SELECT FOR UPDATE.
OK, here's a full testcase then. See definition of the Aggregates and MyValues tables (sqlobjects) in the attached sqlrace.py. Each transaction is "adding a new entry to the MyValues table and updating the total in the Aggregates table (which has a single row)". Run this script once first so that it creates the tables, then run it twice at the same time from two different shells. If the (only) row in the Aggregates table isn't locked, then you get a race where both scripts read the same old value and, in their transaction, write out the same new value. But they both managed to insert a row into MyValues, so the total got out of sync. To check the results after running two concurrent sqlrace.py scripts, use this SQL: select total from test.aggregates select sum(value) from test.my_values The values should match, but they don't when lockRows is not set to True. SELECT FOR UPDATE is needed in order to prevent two transactions (in two different processes) from reading the same value at the same time and stepping onto each other's toes when updating it. And given that the Aggregates table will obviously always be used this way (updating a total), being able to set lockRows=true once and for all in the definition of that table makes sense to me; more than having to remember to pass "lock the rows" in each and every operation on that table - which would be impossible anyway for "agg.total = agg.total + newVal". -- David Faure -- [EMAIL PROTECTED], [EMAIL PROTECTED] KDE/KOffice developer, Qt consultancy projects Klarälvdalens Datakonsult AB, Platform-independent software solutions
sqlrace.py
Description: application/python
