October 6, 2005 at 5:12 pm
Hi,
Can any one tell me how to perform row-level locking. I have a orders webpage for admin users where I display a list of orders in table form. An admin user can click on any of the order to edit the order information. So whenever an admin users clicks on an order for editing, I want to lock this row.
When other users access this page, I want to display all the orders as before. Plus to let them know row X is currently being locked and edited by some one else, I want to display some visual indicator. They should be able to edit all the other records. Any help.
Thanks.
October 6, 2005 at 7:54 pm
In a stateless application (Using IE etc) You cannot implictly lock rows, it will hold rows locked until you release them, which is all good except what if the admin closes the browser, the rows will continue to be locked.
I have implemented row locking a couple different ways and it wasn't easy. It included a column Bit called locked. and a lockeddate datetime field when the application "edits" the row, update the row to locked, and the time it locked. user continues to edit the row, on save, update row, set locked back to 0.
In the instance where the user closes browser, admin can edit the row when say locked is older than 10 minutes old.
There is now way to do this just thru t-sql
October 7, 2005 at 10:22 am
Thanks Ray. That's what I was thinking of doing. But wanted to know if there is any straight-forward way to do it, I guess not.
October 7, 2005 at 1:09 pm
Just to clarify the term a bit: This is called "Pesimistic" locking!
There are other ways to implement this when scalability is a concern. The proposed method above is the so called "flag" method. There is another based on Applocks Table which is more scalable but it has its own complications also.
Just my $0.02
* Noel
October 7, 2005 at 3:48 pm
Can you please explain how applocks table method works. Or any pointer to online article or tutorial will be helpful.
Thanks.
October 7, 2005 at 4:42 pm
In addition to the locked bit field and lockeddate datetime field, wouldn't you also need a session id field so you know which web user locked the row?
I might try SQL locking tricks if this was a client app with fixed connections. You can't do it with web apps because the locks are owned by the connection that created them while the web pages are sharing a pooled set of connections. If it is even possible, it would be an extremely bad idea for a web page to create a lock that had a lifetime longer than a single call to the database. I don't know how you would ever get the web page to reconnect using the same SPID to release the lock. Meanwhile the locked object would be modifiable by any other web user who hit the same connection.
October 7, 2005 at 5:47 pm
Besides pessimistic (lock upfront) and optimistic (based on either a timestamp data-type or an integer column) concurrency models, in a scenario like the one that you have described where you need to provide visual indication (I have seen such requirement in healthcare software), you can use a logical lock model.
One connection's transaction can update a column in a table with a “in-use” value & update a datetime column with the time when the update was made and then when another connection tries to make updates to that record, it checks that column for that special “in-use” value and checks for how long that record has been in use (by using system date – the datetime column value) and based on a threshold (typically time-out setting) of the time that lock has existed, it either overrides that lock or just gives back a lock timeout message after a specified wait interval. For just reads, after reading that this record is "in-use" by another session, it can display the visual indication that you want.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply