update_or_insert first selects the record (if it exists) and then does the create/update. I suppose when two requests come in very close to each other, the second request is checking for the record before the first request has had a chance to commit the insert. I'm not sure I would call that a bug. The alternative would be to lock the database table before the read, but that would stop all other reads on the table and would introduce complexities with web2py's transaction-per-request workflow (e.g., in Postgres, to release a lock, the transaction must end, but if we explicitly commit a transaction in update_or_insert, that would prematurely close the transaction that is intended to remain open for the duration of the HTTP request). I suppose we could add an option to lock the table.
Anthony On Wednesday, October 4, 2017 at 10:25:44 AM UTC-4, Brad Miller wrote: > > Hi All, > > I have the following update or insert statement in a controller. > > db.grades.update_or_insert( > ((db.grades.auth_user == student.id) & > (db.grades.assignment == assignment.id)), > auth_user = student.id, > assignment = assignment.id, > score=score) > > The function that this is part of is called as the result of a user > pressing a button on the web page. It could get pressed multiple times in > quick succession. (Yes, I'll fix that too). ButI was surprised that I end > up with duplicate values every now and then. This is on top of Postgresql > with psycopg2 driver. I added a constraint to the table definition to > ensure that user,assignment pairs are unique and now at least I can catch > the IntegrityErrors when they happen. > > Since this is running in an multi-process environment I guess I could see > how this might happen, but it seems like a bug. Or am I doing something > stupid? > > Brad > > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
