Wpf C# SQL server general question

Question

0.00/5 (No votes)

See more:

Hello Fellow Programmers,

I wanted to see what the community has to say about record locking... See below:

Question: What is the best way to "lock" a record so the application knows that the specific record you are editing is LOCKED and doesn't allow another user at another terminal to edit the same record in the SQL database?

Example: Customer Sales System / I'm updating customer X with a new address. Another person at another location using the same database/application tries to "edit" the address record for customer X.

Goal: Using the example above, the "other user" would receive a messagebox or alert that says record already locked.

I'd love a way to avoid using the database as the deciding factor, but I understand if there's no other way to do it.

What I have tried:

A table in the database to collect record locks passed from the "update" button click in the application, but I don't like it.

Posted 21-May-16 18:05pm

Derek Kennard

Updated 22-May-16 12:19pm

Add a Solution

3 solutions

Solution 2

See the chapter "Working with Lock Partitioning" in this article:
SQL Server Transaction Locking and Row Versioning Guide[^]

Example A:

BEGIN TRANSACTION
    -- This SELECT statement will acquire an IS lock on the table.
    SELECT col1
        FROM TestTable
        WITH (HOLDLOCK);

And chapter "Customizing Locking and Row Versioning":

Quote:
In SQL Server, use the sys.dm_os_waiting_tasks dynamic management view to determine whether a process is being blocked and who is blocking it. In earlier versions of SQL Server, use the sp_who system stored procedure.

Posted 21-May-16 23:38pm

RickZeeland

Updated 21-May-16 23:43pm

v2

Solution 3

I want to thank both of you for your comments. I have reviewed them both and I have decided to go with a variation of what Garth posted. I have created an SP that uses the table name as a variable and updates a new column that indicates the record is "locked" or "unlocked"

Posted 22-May-16 12:19pm

Derek Kennard

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Garth J Lancaster · Accepted Answer · 2016-05-21T21:43:00

Quote:
I'd love a way to avoid using the database as the deciding factor, but I understand if there's no other way to do it.

ok .. but thats what databases are good at. You could write a similar scheme yourself - but at the end of the day, I doubt you would end up with something comparable to what the DB offers (that's not being nasty or disrespectful to you (*) - thats acknowledging the shear amount of work that has gone into Oracle, MSSQL etc)

Lets say every row in your customer record has a 'id' field that
- is blank if the record isnt being edited by someone else
- has the terminal/user id of the terminal/user editing the record

It's still possible to get the equivalent of a 'race condition' in threading terms between when you set the 'id' field to the terminal/user to say 'this terminal wants to update the record' and when another request might say 'I want to do something to that record'. If you're using stored procedures, auditing and logging, then should an inconsistency arise then you can resolve it, but I still think 'let the db do what the db does' should be the rule of thumb - unless of course, the db doesnt offer any form of row locking ...

The other issue is under duress, I think I read even MSSQL might not honour the request.

my 0.02c worth

(*) unless you work/worked for any of the aforementioned companies