Restrict an SQL record while editing

We have a VB.NET application that displays customer records from SQL 2016 database. We can edit or delete the customer records from there. About 50 users use that application. When a user has one customer record open, I do not want any other user to be able to access it. Instead a message should be displayed that another user is accessing this record.

What is the best way to do this? Does SQL have something on a locking record?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Parth MalhanPrincipal EngineerCommented:

Method 1:
You need to maintain state of the record that you opened in Database,
for that you need to create some table and insert row while it is opened in UI and you need to change the state in that table after it is closed.

So every UI Query, to access customer_table will first go to this state_table and check if that particular row is in use or not.

Cons: if connection between UI and server breaks, then that particular row will become invalid, you need to find that row in some
background process and clear that record from state_table.

Method 2:
you can handle that in UI as well,
you need to create a datatable on UI server and populate it same as above method.

hope it will help
ste5anSenior DeveloperCommented:
Please define "I do not want any other user to be able to access it."

Cause this includes imho also that other users may not select (see) it. In this case you need to access the table not directly. You need views or procedures to do this. You also require a stable user identification, e.g. Windows authentication or one SQL Server user per physical user.

Then you need a tracking table of "open" customers (containing the user id and the customer id and a time stamp, when it was last read). As connections can break for several reasons, your UI needs to touch this row periodically (every minute or so) to detect orphaned rows.

Now you use this table to exclude open rows in your view/procedure of available customers or mark them as opened by someone else.

And you run a SQL Server Agent job, which removes orphaned open customers every 2 or 3 minutes.

Important is also: any update or delete on the customer must be guarded. Thus either you do it only by a stored procedure or a trigger, which checks whether the modifying user has that lock.

Another option is using sp_getapplock() on session level, where you encode the customer id in the resource name. Then you can "query" the state before you open a customer for modification by trying to acquire a lock on the client side and you use it as guard in your DML stored procedure.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Does SQL have something on a locking record?

Yes, as ste5an mentioned, use sp_getapplock(). This would be very complex code to try to do yourself, so don't bother, use what's already available to you.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Add a column to the table (e.g "Editing") and when an user is editing the record set this flag to true and when exiting the edit screen set it back to false, so when somebody is trying to edit a record you just need to test if the flag is true or false and act according.
Angel02Author Commented:
Yes. I do not want want other users to even view the record that is locked.

Thanks for all the responses.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.