How to work with lock in SQL records?

Posted on

Question :

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

What I mean is, I have several tables that can be accessed by multiple users simultaneously, but if one tries to edit the registry, for others this registry should be locked for editing.

I saw that some people type a semaphore in the table itself, put a column called editar (for example), and when the user clicks the edit button, it moves that column to 1, so when the other user tries edit we can make a rule if for that.

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

After all, is there a template that people use to perform this function? I think of this because I believe that all software that is for multiple users is indispensable something like this.


Answer :

If that’s really the case, it’s better to use LOCK . But this mechanism should very rarely be used.

Normal is not wanting to get caught. It can get stuck for hours, days. It’s not what you want.

In general, you do not have to do anything. If it depends on data edited in a simple way just let record. Whoever records last will have their information set. This would be the same if it were not a competitor but a sequential one. Obviously you can not 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 overlap with that changed by another user and is not what is wanted, because it will revert what the other user did without being the one desired.

There are cases where this is more complicated. If it is a quantity of inventory, for example you can not just about by the value because it may have been changed in a way that affects your change. In general this type of data is not simply edited, it is altered in a controlled way by the application. So at the time of recording you have to do a new reading of the data and write the update, this will probably happen very fast, maybe less than a millisecond and in a transactional (atomic) way. Already a lot of damage because the programmer does not do that. Then the person’s solution is to be pessimistic and to stop everything. There’s a lot of performance issues, that’s why.

There are cases that are a more complicated one yet. Other strategies may fit. One of them is to use LOCK , but very quickly, not during the entire editing process.

There’s even setting up the transactions with the degree of isolation you want . READ COMMITTED is the default and is usually ideal. Some people are tempted to use other levels to get more guaranteed, but this can slow down transactions longer than desired. After all this in the background is a kind of lock . Using this correctly will be safe.

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

Most people do not understand the operation of databases. Or they make many simple applications that are independent or have little competition (which does not even occur effectively), or people let problems occur, they are rare. Some even coincide.


Leave a Reply

Your email address will not be published. Required fields are marked *