afsheenarab
asked on
Unlocking the record of a table from asp.net 2.0
Hi,
I am developing an enterprise system backend using ASP.NET 2.0 and C#. This is my first enterprise system. In one of the tables of the Database (which is called Booking) I have a column called (IsLockedFromDashboard which is a boolean). I am using Pessimistic and Optimistic concurrencies (a mix of both). The 'IsLockedFromDashboard' will be true when the administrator of the system is trying to modify(update) the values of a specific booking. I want to lock this booking so the users of the front end won't have access to it untill the administrator is done updating the booking.
The first parts (Locking the booking which is being updated and preventing the users to access the booking) are fairly easy and is done. The problem is unlocking the Booking record. As you found out I am not using DB triggers to change the value of the 'IsLockedFromDashboard', I am locking the record from the asp.net (I have designed the Data Access Layer, Business Logic Layer, and Presentation Layer). Let me explain it more clearly, the administrator selects a booking to update it, a function will be called to change the value of the 'IsLockedFromDashboard' of that booking to "true", then in the front end the hyperlink which takes the user to the booking information will be "Enabled=false" so the user cannot retrieve the info from the DB for that particular booking.
Now, my problem is changing that 'IsLockedFromDashboard' to "false" when the admin is done with updating that specific booking. I can add the code to the "Commit Button" I have so when the admin is done updating and he clicks on the commit button i will change the value. BUT, what happens if the admin do not click on the commit button. For example, he clicks on "Logout" or any other link or button. Still I can add calling the method to change the lock value for every single one of the controls (which i believe is not a good design), but what happens if he closes the browser or something happens and the system goes down suddenly. Then that booking will be locked forever. In case of 1,000-100,000 bookings it is hard to find out which one was locked and why they could not be unlocked. Is there any way I can unlock (i have the function just let me know where to call the function) the booking.
* Assume the signature of the function is Bookings_ChangeLockStatus(
I am developing an enterprise system backend using ASP.NET 2.0 and C#. This is my first enterprise system. In one of the tables of the Database (which is called Booking) I have a column called (IsLockedFromDashboard which is a boolean). I am using Pessimistic and Optimistic concurrencies (a mix of both). The 'IsLockedFromDashboard' will be true when the administrator of the system is trying to modify(update) the values of a specific booking. I want to lock this booking so the users of the front end won't have access to it untill the administrator is done updating the booking.
The first parts (Locking the booking which is being updated and preventing the users to access the booking) are fairly easy and is done. The problem is unlocking the Booking record. As you found out I am not using DB triggers to change the value of the 'IsLockedFromDashboard', I am locking the record from the asp.net (I have designed the Data Access Layer, Business Logic Layer, and Presentation Layer). Let me explain it more clearly, the administrator selects a booking to update it, a function will be called to change the value of the 'IsLockedFromDashboard' of that booking to "true", then in the front end the hyperlink which takes the user to the booking information will be "Enabled=false" so the user cannot retrieve the info from the DB for that particular booking.
Now, my problem is changing that 'IsLockedFromDashboard' to "false" when the admin is done with updating that specific booking. I can add the code to the "Commit Button" I have so when the admin is done updating and he clicks on the commit button i will change the value. BUT, what happens if the admin do not click on the commit button. For example, he clicks on "Logout" or any other link or button. Still I can add calling the method to change the lock value for every single one of the controls (which i believe is not a good design), but what happens if he closes the browser or something happens and the system goes down suddenly. Then that booking will be locked forever. In case of 1,000-100,000 bookings it is hard to find out which one was locked and why they could not be unlocked. Is there any way I can unlock (i have the function just let me know where to call the function) the booking.
* Assume the signature of the function is Bookings_ChangeLockStatus(
If you are handling your Session State In Process (mode = InProc) the default state, then you can use the Session_OnEnd Event to check if the session ending was one started by the adiministrator and locked the DB, then simply call your Bookings_ChangeLockStatus function to unlock it.
ASKER
I have s seperate state server for this web application, and I don't think that writing the code in Session_OnEnd will solve the problem in case the the admin just select another booking to update or just click on any other links of the page. I really do not want to call my method in all the link buttons and buttons click event as well as Session_OnEnd. Then testing and debugging it will be a headache I guess.
As you pointed out, after the 'Commit' actiion, you unlock the DB. Also during the Logoff action from the administrator, you should unlock the DB, since he has indicated he is done with the system.
The problem that you are trying to solve, is for those cases that the administrator locks the database, but 'forgets' to unlock the DB. without comitting a transaction. How do you really know that the administrator has 'forgotten'. The best way to handle a 'forget' problem is to release resources based on a 'norma'l period of inactivity. This is how Session State is handled, with a session timeout. Almost all databases also have a lock timeout setting, specifically to handle this type of problem that you could use in this case.
Here are some links that may help:
http://www.sql-server-performance.com/tips/blocking_p1.aspx
http://www.sql-server-performance.com/tips/reducing_locks_p1.aspx
The problem that you are trying to solve, is for those cases that the administrator locks the database, but 'forgets' to unlock the DB. without comitting a transaction. How do you really know that the administrator has 'forgotten'. The best way to handle a 'forget' problem is to release resources based on a 'norma'l period of inactivity. This is how Session State is handled, with a session timeout. Almost all databases also have a lock timeout setting, specifically to handle this type of problem that you could use in this case.
Here are some links that may help:
http://www.sql-server-performance.com/tips/blocking_p1.aspx
http://www.sql-server-performance.com/tips/reducing_locks_p1.aspx
ASKER
lorelogic, thanks a lot for your reply. Everytime I read somebody's post I learn something new. The links you have posted are great but the problem is they are related to MS SQL Server only, and as you know each DBMS has its own way of locking and some of them even do not support it. Probably this is my mistake that did not explain the system clearly.
I am not using DBMS to lock my database, because the system that I am working on has a generic data access layer which can support all different DBMSs. I run a query to lock the table from the asp.net and call that method again to unlock it. I completely agree on what you have said, locking it after 'btnCommit OnClick Event' and also on LogOff, but please note that each Booking (Each record) has a column called (IsLockedFromDashboard, not a nice name i know). So, If I call my query in the logoff I have to retrieve all the bookings which has the IsLockedFromDashboard = true and then how can i find out which one was the last booking that the admin in our example was working on? Maybe there are 50 more bookings which have been locked by different administrators working on the system. Can i unlock all of them in the Session End Event? The answer is no, because probably there are other admins working on some bookings. Also, I prefer not to have a query to retrieve all the bookings with the Lock=True. If I am not still clear enough let me know so I can explain it more.
There can be more than 1 Administrator working on the system simultanously. Believe me I thought a lot about the best place to call my query function, the only reliable way I have found out untill now is to have two viewstates called BookingID and IsLocked and on every link or buttons click handlers of the page check if the booking has been locked get the bookingID from the viewstate and unlock it. Also I have to call the method in Session End as you all said.
I am not using DBMS to lock my database, because the system that I am working on has a generic data access layer which can support all different DBMSs. I run a query to lock the table from the asp.net and call that method again to unlock it. I completely agree on what you have said, locking it after 'btnCommit OnClick Event' and also on LogOff, but please note that each Booking (Each record) has a column called (IsLockedFromDashboard, not a nice name i know). So, If I call my query in the logoff I have to retrieve all the bookings which has the IsLockedFromDashboard = true and then how can i find out which one was the last booking that the admin in our example was working on? Maybe there are 50 more bookings which have been locked by different administrators working on the system. Can i unlock all of them in the Session End Event? The answer is no, because probably there are other admins working on some bookings. Also, I prefer not to have a query to retrieve all the bookings with the Lock=True. If I am not still clear enough let me know so I can explain it more.
There can be more than 1 Administrator working on the system simultanously. Believe me I thought a lot about the best place to call my query function, the only reliable way I have found out untill now is to have two viewstates called BookingID and IsLocked and on every link or buttons click handlers of the page check if the booking has been locked get the bookingID from the viewstate and unlock it. Also I have to call the method in Session End as you all said.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.