Lock the database table using asp.net/sqlserver
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.
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.
It's in the properties of your .net datacontrol - look at cursor types and locking.
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
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
ASKER
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 ?
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.