Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-03-10 : 07:28:39
|
| Giovanni writes "I want to keep a record locked in a multi-user web application until the user that holds the lock finishes to update/modify it. I use the pessimistic lock and keep the recordset open (by means of a session variable) until the user leaves the update page.The recordset is closed automatically when the user session times out. The problem is that if the user closes the browser while being in the update page or forgets to exit the page, the record keeps locked until the session times out (and I don't want to change its default value).Is it possible to set the time a record can be locked or force to release the lock, so that a sort of "edit timeout" can be implemented?" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-10 : 09:02:36
|
| In a web environment (especially) you are better off NOT locking a record that's being edited. It can only lead to problems, such as you're experiencing now. You're also putting recordsets into session variables, which is the fastest way to ruin your webserver's performance, AND also waiting for sessions to time out to close these variables. No offense, but you're doing everything that every ASP site specifically says NOT TO DO in a web app.Instead, grab your data from your recordset AND CLOSE IT IMMEDIATELY. DO NOT store a recordset in a session variable EVER. You can grab the data, close the connection, and then write it to the screen very quickly. The user can then take all day to make their changes. Add a submit button for the user to save the changes. Their values can then be passed to the ASP page and from there to a stored procedure via a new connection (or using a pooled connection) The stored procedure can then check the values to see if they've been changed since the record was sent to the browser, and if they have been changed, it can throw an error. You can even provide feedback to the user on which columns were altered. This technique can also be easily extended to handle multiple row updates.Locking a record/row is NEVER a good solution because it only prevents people from getting work done, and if someone times out their session their changes don't get saved anyway. It's also worth looking at whether people actually DO edit the same rows at the same time (they may not, don't assume) and if they do, whether the app should be altered so that they can't. It's usually a bad business process if it allows multiple people to work on the same thing at the same time, without any coordination between the users. |
 |
|
|
rjpaulsen
Starting Member
9 Posts |
Posted - 2003-03-10 : 19:00:38
|
| I agree. Locking is something you work around... not something you implement. :-) If you need to prevent another user from updating the record, try a 'check-out' system. Add a status field (like 'CheckOutUserID'). Set the field to the user id of whoever checked out the record. Reset it (to null??) after they finish updating it. If you have high volume, use a seperate table to store the CheckOut field so you don't trash your indexes. |
|
|
|
|
|
|
|
|
release record locking