July 23, 2009 at 7:46 am
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 <
Please guide.
Thanks in advance.
July 23, 2009 at 8:51 am
Hi,
I will not say it's the best way for your needs, but let me explain how we work here:
We use 2 fields in every table that's possible to lock:
CREATE TABLE Customer (
ID INT IDENTITY ( 1 , 1 ),
name VARCHAR(20) NOT NULL,
JobName VARCHAR(20) NULL,
LockedBy VARCHAR(20) NULL,
LockTS DATETIME NULL)
Those 2 fields are always at the end of the tables definitions (Lockedby and LockTS). They are updated by the client application, and we put in the userName + the TimeStamp of every locking action.
Everytime we open a screen that needs modifications privileges, (in edit mode), we check for the lock fields, of this particular record, and if it's already locked, we tell the user he cannot edit it, because it's locked. When it's not locked, we lock the record, then give the edit rights.
Every table is accessed by the application (be it web, or a windows based client).
We have another type of access blocking, that is more like an "Action Blocking". When a user tries to, let's say access an option that processes the invoices for an "end of day", we insert in a "ActionLocking" table the tag itself, saying "End of day in process", with the timestamp, and the information, and every screen that could access options that would need that right, just check that table to see it's actually running, and if yes, the option is then denied to the user, to fix the concurrency issues we could have.
This is how we handle Locks in the application.
Hope that helps, or at least, give you a lead on what you will implement in your system.
Cheers,
J-F
July 23, 2009 at 4:38 pm
I suggest you read up on Locking and Isolation Levels in SQL Server. Here is a link for the locking entry in BOL, http://msdn.microsoft.com/en-us/library/ms190615.aspx and here's one for Isolation Levels:
http://msdn.microsoft.com/en-us/library/ms189122.aspx.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply