Concurrency Issue - Restict Select query ( view) till another user modifies it.
-
How do I check that or restrict the row to be selected if same row is modified by other user. For eg. I have created following table. Consider below tables; 1. Bills Table Fields Description BillNo - Bill No Unique (Primary) - No duplicates allowed. Customer - Customer Name -Non Blank BillAmt - Bill Total -Not Empty & Only Positive Values Collected - Collection Done against this bill No Negative Values & shud never be more than BillAmt 2. Collection Table Fields Description RcptNo - Receipt No. Unique (Primary) - No duplicates allowed. RcptAmt - Receipt Total - Cheque / Collection Value. 3. Col_Ref Table - Collection Reference, A Child Table for Collection to store its referred (multiple) bills. Fields Description RcptNo - Receipt No. Unique (Primary) - No duplicates allowed. Ref_BillNo - Referred Bill No. Ref_Billamt - Referred Bill Amount I have some records. for eg. 1) I have Bill no. 00001 with amount 10,000/- in Bills Table. At the same time another user is accessing Collection Table and referring the same Bill no. and accepts receipts and updating amount in Collection table in Bills table. If user is accessing Bill number he will not get actual amount of collection so i want to restict user for viewing the Bill number which is getting modified by another user using Receipt option in web application. The back end will sql server 2005. How do I do that ? Before allowing the Edit, Check if the record this invoice is not locked, only then lock the same & allow editing of this entry only for the Invoice Amount & later on <> / <> release the lock. Otherwise Pop message "Update is in progress for this entry, try again later..." Please guide. Thanks in advance.
-
How do I check that or restrict the row to be selected if same row is modified by other user. For eg. I have created following table. Consider below tables; 1. Bills Table Fields Description BillNo - Bill No Unique (Primary) - No duplicates allowed. Customer - Customer Name -Non Blank BillAmt - Bill Total -Not Empty & Only Positive Values Collected - Collection Done against this bill No Negative Values & shud never be more than BillAmt 2. Collection Table Fields Description RcptNo - Receipt No. Unique (Primary) - No duplicates allowed. RcptAmt - Receipt Total - Cheque / Collection Value. 3. Col_Ref Table - Collection Reference, A Child Table for Collection to store its referred (multiple) bills. Fields Description RcptNo - Receipt No. Unique (Primary) - No duplicates allowed. Ref_BillNo - Referred Bill No. Ref_Billamt - Referred Bill Amount I have some records. for eg. 1) I have Bill no. 00001 with amount 10,000/- in Bills Table. At the same time another user is accessing Collection Table and referring the same Bill no. and accepts receipts and updating amount in Collection table in Bills table. If user is accessing Bill number he will not get actual amount of collection so i want to restict user for viewing the Bill number which is getting modified by another user using Receipt option in web application. The back end will sql server 2005. How do I do that ? Before allowing the Edit, Check if the record this invoice is not locked, only then lock the same & allow editing of this entry only for the Invoice Amount & later on <> / <> release the lock. Otherwise Pop message "Update is in progress for this entry, try again later..." Please guide. Thanks in advance.
You may want to consider using your own locking logic with the help of "sp_getapplock". http://msdn.microsoft.com/en-us/library/ms189823.aspx[^] Be very careful when implementing a locking strategy you may bring your entire application to a screeching halt if locks are not released in a timely manner. You may want to use the sp_getapplock as a method to warn users that a particular account is being updated and not place any hard locks. You could also use this to indicate who has the "lock". Good luck.