CODEWITHSUNDEEP
c#.netsql-server
josef_skywalker
josef_skywalker

Reputation: 1107

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

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.

Upvotes: 7

Views: 7700

Answers (4)

Jim Berg
Jim Berg

Reputation: 659

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.

Upvotes: 7

glenebob
glenebob

Reputation: 1973

I will describe the mechanism that I have used with success in the past.

1) Create a document ID table. In this table, each record represents a document type and an ID which can be incremented whenever a new document is created. The importance of this table is really as a root lock; the document ID is not strictly needed.

2) Create a lock table. In this table, each record represents a lock which includes a reference to a document record, a reference to the lock owner, and some additional data such as when the lock was created, when it was last acted upon, its status, or anything else you find useful. Each record means "user A holds a lock on document type X, document ID Y".

3) When locking a document (fetch + lock), lock (SELECT/UPDATE) the relevant record in the document ID table. Then, check the lock table for an existing lock record, and INSERT a new one as appropriate. At this point you may choose to over-write an existing lock, or return an error to the user.

4) When updating a document, again lock (SELECT/UPDATE) the relevant record in the document ID table. Then verify the user holds a lock, and if so do the actual update, and then DELETE the lock record. If the user does not hold a lock, you may choose to allow the update if no other user holds a lock, or return an error.

With this mechanism, a user goes through a open/lock operation, and a save/unlock, or discard/unlock operation. Additionally, locks can be removed by a cron job or by an administrator, in case users fail to update or discard (which they will).

This approach avoids holding record locks and transactions open for long periods of time, which can cause concurrency issues. It also allows locks to survive software crashes. It also allows all kinds of flexibility; for example, my implementation allowed a lock to be "demoted" after some period of time, and once a lock was demoted, it could be over-written by an ordinary user, while still allowing the owner to perform an update as long as the lock remained.

Upvotes: 0

Ben Krueger
Ben Krueger

Reputation: 1536

One way to handle concurrency via application is implement some kind of "LastServerUpdateDateTime" column on the table you are working on.

When User A pulls the data for a row the ViewModel will have that LastServerUpdateDateTime value saved. Your User A does their updates and then try to save back to the DB. If the LastServerUpdateDateTime value is the same, then that means there was no updates while you were working and you are good to save (and LastServerUpdateDateTime is also updated). If at any point while User A is working on a set of data on the application side, and User B comes in makes their changes and saves, then when User A eventually saves the LastServerUpdateDateTime will be different than what they initially pulled down and save will be rejected. Yes User A then has to redo their changes, but it shouldn't happen often (depending on your application of course) and you don't have to deal with direct DB locking or anything like that.

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

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.

Upvotes: 1

Related Questions