?
Solved

Lock the database table using asp.net/sqlserver

Posted on 2007-10-19
6
Medium Priority
?
648 Views
Last Modified: 2010-05-18
I am creating a application using asp.net and sqlserver-2000
the problem i am facing is to lock the rows or table when one user opens the update page
i mean
if one user opens the page that edit the table  "meter_instalation", the other users connot see the records in meter_instalations unless first user close the page.
i studied about sqlserver locks but they only lock when there is transection.
0
Comment
Question by:mahabat
6 Comments
 
LVL 11

Expert Comment

by:cmhunty
ID: 20108912
When you say "cannot see", do you mean "can't access the data through any method" or just "other justs can't access the web application"?

If it's the second one, that shouldn't be too hard. You can set a flag somewhere whether the database is accessible and when they leave the page, change the flag back. If the flag is inaccessible, the page would inform the user.
0
 
LVL 14

Expert Comment

by:twoboats
ID: 20109397
It's in the properties of your .net datacontrol - look at cursor types and locking.
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 20111281
locking information is a very tricky task and has to be done well .. .net does not provide inbuilt features to what you want in a web environment because a web application is stateless i.e. does not maintain the state .. so once a page is processed and if the page had connections to the database, it no longer maintains the connection and so when the page is posted back, there is no way .net knows which record it was looking at earlier .. so some of us use a field in the table to flag that a record is been edited .. this cannot be just a flag but a information as to who has locked this record (the login id or something like that which can uniquily identify your user) .. but again this has its own drawbacks in a web environment ..
consider the case that a user requests to edit a records .. so when you fetched it, you flagged the records as been "edited" .. the next user asks for the same record .. you check the flag and say the record is been edited .. good ..

but now the first user never finished editing the record becuase he got busy in a phone call etc etc and the session timeouts .. or he browses to another site or he closes his browser and goes home .. the record is still locked .. so who will unlock it .. then you will now need to write code in the session_end to unlock such records .. now if you have 100 tables, will you write code to unlock 100 tables because you do not know which all tables did the user locked (remember in a web environment, the user can also move within various links of your website) .. anway, the point is this is again not full proof. .

so what I would advice is to use what some of us call as "optimistic" locking i.e chances of the record that the user is editing been changed at the same time is very minimal .. so add a timestamp field to the record .. when a user opts to edit a record, get the timestamp value and store it in a hidden field on your page .. when the user edits and saves, update the new timestamp .. but before saving check if the old timestamp(in your hidden field) and the one that is available for that record in that table is the same .. if yes, no one edited the same record while this user wes editing .. if not, the record was changed by someone ..

now depending on how critical this  information is or depending on who is currenlty doing the edit (administrator user or something like that) .. basically depending on your application design, you can prompt the user that the information has changed and if needed show him the new set of values .. then he can review it, make his changes again and save ..

Rejo
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:mahabat
ID: 20122089
Great help Rejo and cmhunty
Actually the application is already working without these checks and i dont want to change database tables. i thought about 2 solutions
1-
the perfact solution for me is if i lock database table on the page_load event of EditPage and unlock it on Unload event or on Update button. in this solution i dont major change in all pages. Also when the table is locked no body can fetch or read that table.

2-
i create new database table with fields
user
page
lock (boolean )
and make lock TRUE on the load event of update page and unlock on unload
and check the Lock(boolean) on the page load of display event

what u think ?
0
 
LVL 26

Accepted Solution

by:
Rejojohny earned 1000 total points
ID: 20122969
it will still have the issues I have mentioned above like how to handle session timeout, browser crashing etc ..
0
 

Author Comment

by:mahabat
ID: 20130025
so this solution is good ?
2-
i create new database table with fields
user
page
lock (boolean )
and make lock TRUE on the load event of update page and unlock on unload
and check the Lock(boolean) on the page load of display event
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question