What is the proper way to lock rows in SQL while a user works with the data?

Question

I am currently working on some kind of ERP like WPF application with SQL Server as the database.

Up to now, I had to work only with small tasks that does not need row locking on the server side. So the basic was "Create SQLConnection-> Select Data in the DataTable -> close connection".

Now I would like to create the functionality to work on orders.

How could I Lock the records that has been selected till the user finishes the work so no other user can read that rows?

I think I should use transactions, but I am not sure how to keep the transaction alive until the next statement, because I am closing the connection after each command.

Answer 1

Locking data like that is a bad practice. A transaction is intended to ensure that your data is completely saved or not at all. They are not intended to lock the data for the reason specified in your question.

It sounds like the data being entered could be a lot so you don't want a user spending time entering data to only be met with an error because someone else changed the data. You could have a locked_by column that you set when a user is editing the data and simply not allow anyone else to edit the data if that column is not NULL. You could still allow reads of the data or exclude locked data from view with queries depending on your need.

You may also want to include a locked_time column so you know when it was locked. You could then clear the lock if it's stale, or at least query how long it's been locked allowing for an admin user to look for lengthy locks so they can contact that user or clear the lock.

The query could look like this:

UPDATE Table SET locked_by = @lockedByUser, locked_time = @lockedTime 
WHERE Id = @fetchId and locked_by IS NULL

SELECT * FROM Table WHERE locked_by = @lockedByUser

If no data is returned, the lock failed or the id doesn't exist. Either way, the data isn't available. You could retrieve the records updated count, to also verify if the lock was successful or not.

Answer 2

Don't close the connection

• open transaction
• on the select use an uplock so record(s) are locked
• perform updates
• commit or rollback the transaction

Put some type of timer on it.