How to work with lock in SQL records?

Asked

Viewed 907 times

4

I’m studying best practice to work with lock of records.

What I mean is, I have several tables that can be accessed by several users simultaneously, but if any attempt is made to edit the record, for others this record should be blocked for editing.

I saw that some people do kind of a semaphore on the table itself, put a column called editar (for example) and when the user clicks the edit button, he arrow that column to 1, so when the other user is trying to edit we can make a rule if for such.

However, also, I saw that there are lock’s in SQL Server, it would not be better to work with this type of lock?

After all, is there a model that people use to perform this function? I think about it because I believe that all software that is for multiple users is essential something like this.

sql sql-server competition

2 answers

6


If that’s what it is, you’d better use it LOCK. But this mechanism very rarely should be used.

The normal thing is not to want it to be stuck. It can be stuck for hours, days, it is not what you want.

In general you don’t need to do anything. If it depends on data edited in a simple way just let it be recorded. Whoever saves last will have their information fixed. This would happen the same if it was not a competitor, but sequential. Obviously you cannot record what has not been changed. This is a basic care that many people do not, but should do. If this fails the unchanged data will override the changed data by another user and is not what is desired, because it will reverse what the other user did, without being the desired.

There are cases where this is more complicated. If it is a stock quantity for example, it cannot only override the value, because it may have been altered in a way that affected its change. In general this type of data is not simply edited, it is modified in a controlled way by the application. So at the time of recording you have to do a new reading of the data and record the update, it will probably occur very fast, in general below a millisecond and transactional (atomic) form. I’ve seen a lot of damage because the programmer doesn’t do it. Then the solution is to be pessimistic and stop everything. Too much performance problem is because of this.

There are cases that are even more complicated. Other strategies may fit. One of them is to use the LOCK, but very quickly, not throughout the editing process.

There’s even a way configure transactions with the degree of isolation you want. READ COMMITTED is the standard and is usually ideal. Some people are tempted to use other levels to be more secure, but this can lock down transactions longer than desired. After all, it’s a kind of lock. Using it correctly will be safe.

It’s easy for people to use these things incorrectly and create a deadlock.

Most people don’t understand the workings of databases. Either they make very simple applications that are independent or have little competition (that doesn’t even occur effectively), or people let problems occur, they are rare. Some even hit by coincidence.

  • perfect, but for example, if you want to prevent a record being edited from being presented to some other user, then the use of lock would be justified? the use of a column only to represent whether it is in editing or not, is it out of the question? I say this would be a gambiarra?

  • 2

    In this rare case, it would be yes. Particularly I would not use another mechanism. But it depends on the case. It may have a justification to do. In everything can be useful if it is well justified. The problem is to do because another has done before and do not understand the reason. Everything you knew why you’re doing, mastering all nuances can hardly be considered gambiarra. Of course, the reason I can’t "I can’t do it any other way", "I don’t have time to do it right", etc. :) It’s possible that a field like this is useful. It is possible that causes problems that the person did not even notice, such as lack of liberation. The lock it’s well thought out

2

Using lock is asking to bother. The really decent solution will require collaboration between database and application. One idea:

1) There must be a timestamp column that is updated in Insert and in every update. At least Mysql has TIMESTAMP column that can be configured to update itself at each update, so so far it’s zero effort on the application side.

2) When you start editing the line, this timestamp is read (along with the other data).

3) Before recording the line, the timestamp is read again from the bank. If it does not match the timestamp read in step 2, it does not record and warn the user of the conflict.

It could even show which columns were changed by someone else, offer conflict resolution, merge the changes if they happen in different columns, etc.

4) The second and final write should occur within the same transaction, in case of (unlikely) a race condition, two try to record at exactly the same time.

5) The application must deal with transaction failure, either by aborting the edit, or by going back to step 3.

Browser other questions tagged sql sql-server competition

You are not signed in. Login or sign up in order to post.