[web2py] Re: How do we handle concurrent db update?
I don't know very well web2py internals (in the book at chapter 04 seems to behave like all operations inside a function are executed inside a transaction, if an exception raises all opened transaction are rollbacked). If it is like explained in web2py book this is the right way to do normal transactions in a stateless world (like the web one). In this default behaviour, if Alice hasn't finished the function and Bob starts the same function, sure there will happen race conditions (Bob istantiate a transaction while Alice's one is not committed yet) chapter/06#commit-and-rollback gives you a hint... If you need to avoid the behaviour you explained, you need a different table design, but for the sake of discussion.: before showing you booked resource X, wouldn't db.commit() and re-selecting the record watching again the occupied value solve the problem ? On 1 Giu, 07:40, Mathew Grabau grabau@gmail.com wrote: Yes - that is exactly the problem that transactions are intended to take care of. What would writing raw SQL do? Raw SQL statements (unless explicitly contained in a transaction) would be executed outside of the transaction and therefore still vulnerable. On May 31, 6:57 pm, David J da...@styleflare.com wrote: I am confused. Isn't this exactly what transactions are for? On May 31, 2011 7:54 PM, pbreit pbreitenb...@gmail.com wrote: We aren't talking about a bank account (and even if we were, that's why banks rely on reconciliation procedures), we are talking about a booking system. It's like security, you're never 100%, just as close to 100% as you need to be.
[web2py] Re: How do we handle concurrent db update?
On May 31, 10:29 pm, Vasile Ermicioi elff...@gmail.com wrote: you can use record versioning to solve that somehow http://web2py.com/book/default/chapter/07#Record-Versioning Thanks for the feedback, but the info you provided is for archive, not for dealing with parallel update. Or do I miss something? Regards, Iceberg
[web2py] Re: How do we handle concurrent db update?
I wouldn't worry too much about it. As long as you check for its availability immediately before assigning it to a traveler, you'll be fine. Then you'll need to run a periodic job to release pending reservations that do not complete.
[web2py] Re: How do we handle concurrent db update?
If you have a record version number field in the table then you can read it for the record of interest. When you go to update the record you specify the expected version number in the update equivalent to the SQL WHERE along with the field id so if the version number has been incremented by another user update, your update will fail because the WHERE version=x part of the update will not match. I think this depends on the database you use as well. I thought most modern databases apply a read lock while you are looking at a record and if you try to update the lock is upgraded to a write lock and if someone else beat you to the update an error is thrown. You would need to check your database documentation looks for pessimistic vs optimistic locking or just locking.
[web2py] Re: How do we handle concurrent db update?
On May 31, 11:10 pm, pbreit pbreitenb...@gmail.com wrote: I wouldn't worry too much about it. As long as you check for its availability immediately before assigning it to a traveler, you'll be fine. Then you'll need to run a periodic job to release pending reservations that do not complete. But I am not that confident about it. Allow me to quote some word from [1]. I was able to exploit this race condition and create two identical user records in SQLDB (In test environment, I had to add a small time.sleep(), but in real world such problems DO happen - for example when server load is higher). And, in case of [1], there is a last ditch, duplicated record can be denied if our db has a unique=True constraint. But in my booking system case, no constraint can help. User Bob can overwrite user Alice's booking, and we don't even have an easy way to detect and repair that. :-/ [1] http://groups.google.com/group/web2py/browse_frm/thread/679f353e4dd10982
[web2py] Re: How do we handle concurrent db update?
It's a theoretical problem, not an actual one. If you check room availability before reserving the room, you'll be fine. If your service ever gets enough traffic for it to become a problem (highly unlikely), you will have the resources to address it. It's better to spend your energy trying to get to that point.
[web2py] Re: How do we handle concurrent db update?
This is not a theoretical but a very actual and serious problem. Think about a bank account, you might be in a deadly embrace. Web2py is not a only user that access database. So the problem is not solved in web2py. If i were you, i would write a raw sql statement. On 6月1日, 午前1:25, pbreit pbreitenb...@gmail.com wrote: It's a theoretical problem, not an actual one. If you check room availability before reserving the room, you'll be fine. If your service ever gets enough traffic for it to become a problem (highly unlikely), you will have the resources to address it. It's better to spend your energy trying to get to that point.
[web2py] Re: How do we handle concurrent db update?
We aren't talking about a bank account (and even if we were, that's why banks rely on reconciliation procedures), we are talking about a booking system. It's like security, you're never 100%, just as close to 100% as you need to be.
Re: [web2py] Re: How do we handle concurrent db update?
I am confused. Isn't this exactly what transactions are for? On May 31, 2011 7:54 PM, pbreit pbreitenb...@gmail.com wrote: We aren't talking about a bank account (and even if we were, that's why banks rely on reconciliation procedures), we are talking about a booking system. It's like security, you're never 100%, just as close to 100% as you need to be.
[web2py] Re: How do we handle concurrent db update?
Yes - that is exactly the problem that transactions are intended to take care of. What would writing raw SQL do? Raw SQL statements (unless explicitly contained in a transaction) would be executed outside of the transaction and therefore still vulnerable. On May 31, 6:57 pm, David J da...@styleflare.com wrote: I am confused. Isn't this exactly what transactions are for? On May 31, 2011 7:54 PM, pbreit pbreitenb...@gmail.com wrote: We aren't talking about a bank account (and even if we were, that's why banks rely on reconciliation procedures), we are talking about a booking system. It's like security, you're never 100%, just as close to 100% as you need to be.
