Hi there,
I got stuck with how or whether we can deal with the concurrent db
update, without the "overwrite" problem. This should be fundamental
but my brain is out of ideas. Please guide me.
Well, one specific example as a background. Think about a booking
system.
db.define_table('resource',
Field('name'),
Field('occupied', 'boolean', default=False),
Field('used_by'),
)
# Find any vacancy, then assign it to a user. FCFS.
def book_any_vacancy():
vacancy = db(db.resource.occupied==False).select().first()
vacancy.update_record(occupied=True,
used_by=request.vars.username)
return dict(foo='You just book resource #%d'%vacancy.id)
# But if we use db other than sqlite, those two db operations
# are not atomic. Then, will this sequence happen?
# 1. Alice visits book_any_vacancy(), triggers
db(...).select()
# 2. At the next 0.02 seconds, Bob visits and triggers
db(...).select()
# 3. Alice triggers update(...), got response 'You just book
resource #1'
# 4. Bob triggers update(...), got response 'You just book
resource #1'. Oops?!
#
Question:
1. Need to somehow combine the select()-then-update() procedures into
one atomic operation? And how?
2. What is the good practice to avoid above race condition?
3. Does different db backend (sqlite or postgrel) affect the answer to
question 1 and 2?
PS:
* There is even a web2py plugin designed for locking (http://
web2py.com/plugins/default/locking). But it still seems vulnerable,
what if two users obtain lock at the same time? At that moment, both
users thought they obtain the lock.
* I understand that a whole booking system can be complex. Here is an
old discussion but it did not clearly cover the "overwrite" issue.
http://comments.gmane.org/gmane.comp.python.web2py/20843
* It mentioned a prototype but it is not longer available? I wanna see
how it solves this problem.
http://web2py.com/examples/static/web2py.app.booking.w2p
* Similar discussion here.
critical problem - race condition in "check is in db" / "insert in
db" (IS_NOT_IN_DB)
https://groups.google.com/group/web2py/browse_frm/thread/679f353e4dd10982
Regards,
Iceberg
