locked
How to do record locking in an order entry system.. RRS feed

  • Question

  • I only want 1 user changing an order at a given time, aside from setting a field in the orders header record, is there a good way to do this? What about using mssql locking?
    Tuesday, June 7, 2011 5:40 PM

Answers

  • Only problem I see is if there is a network connection issue when the user has the order in "locked" mode.. I would have to manually go in and "unlock" the record..


    Hi Waldo,

    You can use BEGIN TRAN block to mark the starting point of an explicit transaction. If the connection error occurs and the transaction is not completed, then the statements executed for the duration of it will not effect on the data. Also, use COMMIT TRAN block to complete the transaction or ROLLBACK block to roll back the transaction while any error occurs.
    BEGIN TRANSACTION 
    
    BEGIN TRY
    	INSERT INTO test.t1 SELECT 1,1
    		SELECT 1/0 --an error occurs, then it will go to CATCH block
    		COMMIT TRANSACTION 
    END TRY
    
    BEGIN CATCH
    	ROLLBACK		
    END CATCH
    
    


    Best Regards,
    Stephanie Lv


    Thursday, June 9, 2011 5:32 AM
  • >if there is a network connection issue when the user has the order in "locked" mode.. I would have to manually go in and "unlock" the record..

    That depends on the setup.

    A quick example (not necessarily a good one though, quickest thought on top of my head) would be to tag the record with a timestamp, and require a refresh of the timestamp every minute. If it is more than a minute old, the lock is stale and can be ignored.

    Report As AbuseBeside, if there is a network issue, at what point do you want to unlock the record? Come up with the criteria, and there will probably be an implementable solution.
    Friday, June 10, 2011 2:41 AM
    Answerer
  • Personally, i would set a record in the header. It is a very effective way to tag the record without causing locking issues. Is there a reason you do not want to use this method?
    Tuesday, June 7, 2011 8:32 PM
    Answerer

All replies

  • Personally, i would set a record in the header. It is a very effective way to tag the record without causing locking issues. Is there a reason you do not want to use this method?
    Tuesday, June 7, 2011 8:32 PM
    Answerer
  • Not really.. I had thought of this but didnt know if there was a better way to do locking using sql servers built in locking.. Only problem I see is if there is a network connection issue when the user has the order in "locked" mode.. I would have to manually go in and "unlock" the record..

    Wednesday, June 8, 2011 3:14 PM
  • Only problem I see is if there is a network connection issue when the user has the order in "locked" mode.. I would have to manually go in and "unlock" the record..


    Hi Waldo,

    You can use BEGIN TRAN block to mark the starting point of an explicit transaction. If the connection error occurs and the transaction is not completed, then the statements executed for the duration of it will not effect on the data. Also, use COMMIT TRAN block to complete the transaction or ROLLBACK block to roll back the transaction while any error occurs.
    BEGIN TRANSACTION 
    
    BEGIN TRY
    	INSERT INTO test.t1 SELECT 1,1
    		SELECT 1/0 --an error occurs, then it will go to CATCH block
    		COMMIT TRANSACTION 
    END TRY
    
    BEGIN CATCH
    	ROLLBACK		
    END CATCH
    
    


    Best Regards,
    Stephanie Lv


    Thursday, June 9, 2011 5:32 AM
  • SQL Server allows to one user to update specific row, so there will be no way to a;low more than one user, as sql; server obtains X exclusive lock on the row. 

    But are you talking about the logic when two users (A and B) opened the screen at the same time and perform some updating.but A user pressed the button before the B, so when the B pressed the button the previous data (updated by A) will be overwritten

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, June 9, 2011 5:41 AM
  • SQL Server allows to one user to update specific row, so there will be no way to a;low more than one user, as sql; server obtains X exclusive lock on the row. 

    But are you talking about the logic when two users (A and B) opened the screen at the same time and perform some updating.but A user pressed the button before the B, so when the B pressed the button the previous data (updated by A) will be overwritten

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Actually I want it if user A starts editing the order then no other user can edit that order until user A has finished editing it and leaves "edit mode" in the application..


    I also dont want the order to be sent off to the warehouse for processing while the user is editing it..

    Thursday, June 9, 2011 2:31 PM
  • Only problem I see is if there is a network connection issue when the user has the order in "locked" mode.. I would have to manually go in and "unlock" the record..


    Hi Waldo,

    You can use BEGIN TRAN block to mark the starting point of an explicit transaction. If the connection error occurs and the transaction is not completed, then the statements executed for the duration of it will not effect on the data. Also, use COMMIT TRAN block to complete the transaction or ROLLBACK block to roll back the transaction while any error occurs.
    BEGIN TRANSACTION 
    
    
    
    BEGIN TRY
    
    	INSERT INTO test.t1 SELECT 1,1
    
    		SELECT 1/0 --an error occurs, then it will go to CATCH block
    
    		COMMIT TRANSACTION 
    
    END TRY
    
    
    
    BEGIN CATCH
    
    	ROLLBACK		
    
    END CATCH
    
    
    
    


    Best Regards,
    Stephanie Lv



    This really doesnt solve the problem.. I am not "in the middle" of running a procedure.. The problem is if I put something into the header record that user A is editing the order and user A has the order up on their screen, then they lose connection and close the application before getting connectivity back, the order will stay as "being edited" until I manually go in and "unlock" it..
    Thursday, June 9, 2011 2:34 PM
  • >if there is a network connection issue when the user has the order in "locked" mode.. I would have to manually go in and "unlock" the record..

    That depends on the setup.

    A quick example (not necessarily a good one though, quickest thought on top of my head) would be to tag the record with a timestamp, and require a refresh of the timestamp every minute. If it is more than a minute old, the lock is stale and can be ignored.

    Report As AbuseBeside, if there is a network issue, at what point do you want to unlock the record? Come up with the criteria, and there will probably be an implementable solution.
    Friday, June 10, 2011 2:41 AM
    Answerer