- Posted by: Vincenzo Sorrentino
- Posted on: October 18 2004 10:03 EDT
How Can I revolve concurrent access on data in Web Application
I've two user connected in my application that work on same record.
If one of this wait just a minute on change a field in the record and the other one meanwhile modify that field I obtain a not consistent state of data
Thanks in advance
- Concurrent access on data in Web application by joseph yi on October 18 2004 19:06 EDT
- Concurrent access on data in Web application by Sanjaya Ganesh on October 19 2004 04:23 EDT
- Concurrent access on data in Web application by joseph yi on October 19 2004 16:03 EDT
- Concurrent access on data in Web application by Duncan Mills on October 19 2004 19:08 EDT
- put the lock in the db by Bruce Goldstein on October 21 2004 10:08 EDT
A simple thing to do would be to 'lock' records by using the ServletContext object to track which record is open for updating. Perhaps you can make an 'AccessLock' object storing the user ID, record ID, and time of lock. When the user is done updating, you can remove it from the ServletContext. You'll also probably want a thread to time out records that people open for updating but close the browser or don't update for whatever reason. Sorry if it's not a sophisticated answer; I've never done anything like this and this is the easiest thing I can think of. It's a good question though; I'm curious as to how other people deal with this issue.
This is a standard isolation issue with any DB application. Normally, there are two ways in which you can address this:
(1) Allow the users to manually handle this. Let any number of people update it, it is their responsibility to make sure not to step each other with clearly demarcated workflows / roles for those users
(2) Have an update timestamp for every DB record which is automatically updated via a DB Trigger. Each time you read, read that timestamp. When you go to update it, check to make sure that the timestamp has not changed. You can do this in your update statement itself with an additional WHERE clause for update_timestamp. If the update failed (0 records updated), that would mean the record has changed since it was read.
My suggestion is dont use explicit locks. Explicit row locks in web applications can cause chaos. Imagine, I opening the record and go home for the day leaving the browser open :-).
as i said before, i've never done this... but i still think locking records in the web application using ServletContext is a decent solution and can be used in many ways beyond just locking records (i.e. tracking/monitoring/unlocking records). in the case where a user leaves his browser open, i already mentioned having a thread process handle that (TimerTasks are not difficult to write).
the db trigger solution is not bad, but if i were a user, i'd be upset if i spent 30 minutes editing a record only to find that my update was not accepted. i feel the user should know beforehand that somebody is working on the record so that time wasn't wasted.
anyway, i agree that communication is key, and no matter what solution you decide on, you'll be writing more code ;p
My suggestion is dont use explicit locks. Explicit row locks in web applications can cause chaos. Imagine, I opening the record and go home for the day leaving the browser open :-).That's a pretty dangerous suggestion, I'd say just the opposite, let the database handle the locking. Rolling you own locking in the mid tier session might be OK, if and only if, this appliciation is the only way of accessing the data, but if more than one interface or more than one application needs to access that data it ain't gonna work.
If you use a decent object relational mapping layer then things like issuing the locks and cleaning up when the user walks away will be handled for you. The only real decision here will be the locking mode you choose - optimistic or pessimistic.
Optimistic - assume that most likely no-one else will actually be updating this row so you only issue the lock just before the transaction is committed.
Pessimistic - Lock the row as soon as you've made a change in the cache.
In both of these cases you'd also want to check either the timestamp (if the record has one) or a comparison of the actual data between the cache and the DB to make sure that someone else has not changed and subsequently committed underneath you so that your image of the row is now stale. Again this kind of functionality should come as standard with any decent O/R mapping framework.
ah, i didn't think about that.
i come from a background using different dbms's and mysql doesn't offer triggers so i'm always looking for non-db solutions first. the OR layer seems like the most general solution
luckily i hardly develop data requiring concurrent access, but if i ever do, i'll check out the popular OR frameworks
I'd say just the opposite, let the database handle the locking.
Unfotunately, this is NOT "Explicit Locking" by application. Row levels locks in a web application are to be handled automatically by the DB and not by the application - java code. Of course, you can have soft locks in the application level (not DB locks) to handle this concurrency - but locking DB explicitly from a web application (I reiterate) can never be a reliable solution.
I have not seen a web application which plays around with DB locks - That does not mean there is no application taht way :-). Just that I have not seen :-) in last 7 years of my java play.
I still would suggest the simplest way for "MOST" applications is to let the user know taht the record you are trying to update is already modified by another user. You will see the importance of this, when the time duration between READ and UPDATE is not small - but considerably large. I do not know of any O/R framework that will handle a 15 minute gap between READ and UPDATE and still let me know that the record has been updated by someone else. Because I feel it is a 'business requirement' and not something a DB / framework shoudl auto-handle (when you have larger gaps between reads and updates).
Then there is the question of whether the READ and UPDATE fall into the same TXN context - Again here too I feel having a TXN span across multiple user interventions is a bad design.
Interesting area - Dont know if I communicated / mis-communicated :-)
Frameworks do exist (Toplink for example) which will handle a long gap between read and update. It's not a tricky thing to do although it obviously does have a cost overhead as you (or rather the framework on your behalf) will be checking something in the table to make sure the record has not been changed.
Of course if you're using a database such as Oracle such checks are lower cost as they can be conbined with the lock statement itself. (select ... for update)
Anyway, if you have a large multi table transaction to carry out regardless of how you collect and then submit the transaction(s) you should be explicitly locking the effected rows before starting to apply the updates otherwise (on a high transaction OLTP system particulary) you'll not be able to guarentee the consistency of the interelated data.
I'd even apply this if you collected all the data in the midteir and posted the whole graph of changes as one logical transaction - which would obviously be the preferred design as you point out.
As you say - an interesting area, and of course somewhat dependent on what the loading on the Database is and what type it actually is.
For long transactions, i.e. user views data, user edits data, user hits save I like putting locking info in the db.
Lock Table: id, record_id, user_id, created, last_modified
When a user wants to edit data they throw a record in the lock table. This gives them x minutes of lock time, i.e. the lock is leased. Four cases:
1. user saves data and deletes lock
2. user takes more than x minutes and refreshes lock
3. user cancels operation and deletes lock
4. user walks away and lock times out in x minutes
I do my timeout checks in my locking framework although you could use a background thread. A background thread can also refresh locks (java like jini).
1. Long transactions do not require open database connections. It is a bad idea to hold db connections for minutes in a web app, i.e. do not use select ... for update.
1. More work but then its always more work for more complexity.
Hope this helps.