Unlocking the record of a table from 2.0

Posted on 2007-07-29
Last Modified: 2012-05-05

      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 (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(Int64 ID,bool Status) which the status is the status of the "IsLockedFromDashboard" (true/false).

Question by:afsheenarab
    LVL 3

    Expert Comment

    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.

    Author Comment

    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.
    LVL 3

    Expert Comment

    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:

    Author Comment

    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 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.

    LVL 3

    Accepted Solution

    When an administrator locks a booking, save the IDs he has locked in his session object. When he logs out, check his session for the ID's that he has locked to call your Bookings_ChangeLockStatus(Int64 ID,bool Status) function to change the status to unlocked on logout and remove the locked ID from his session object .
    You can add a timestamp that is also set in the database for the status of lock. This would allow you to run a query to unlock only those bookings that have been locked for a period greater than 'normal' such as 30 or 60 minutes.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
    A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now