Database. Parallel Access Conflicts (Part 1 - Problem Search)
Dear colleagues, in this article we will not consider the types of locks in SQL, but the ways to solve problems when accessing the same data from different connections, and some updates may be lost. The article does not depend on a specific database and may be equally interesting to many.
There can always be such a situation when we read some records in one connection and then tried to update them. But for the moment when we edited them, and then tried to save, in another connection, the same records were already updated. In other words, the first process reads the data, after which the second process reads the same data, and the second process updates the same data before the first process can do it, a conflict will arise when the first process tries to update this data.
If the database is accessed from several connections and changes are made, then the occurrence of conflicts is only a matter of time and luck.
')
The application itself must decide what actions it needs to do to resolve this conflict. For example, the situation may be as follows: the site administrator has visited a page displaying data of a regular user (the administrator has the ability to update this data). If after the page of the administrator reads user data from the database, and the normal user access the page displaying his user data , and will make changes, then a conflict will arise when the administrator saves their changes. If the conflict does not arise - then the changes of the normal user will be blocked and lost. It may be otherwise - the administrator changes are lost. What kind of behavior should be true in each case - this is a difficult problem. The first step is to discover it. The second is to allow. There are two basic approaches to resolving concurrent access conflicts - optimistic and pessimistic.
The first part of this article is devoted to the problem itself and how to solve it. The second part of the article will describe how to resolve conflicts in LINQ to SQL. It may be the third part, if I manage to persuade a colleague to describe ways to resolve conflicts in Hibernate (but this will be known later). These articles will occupy much more space, so the first part is described separately, although it is rather short.
Optimistic solution
The optimistic way comes from the fact that in most cases conflicts do not occur. Therefore, no lock on the record is not installed. And when suddenly a conflict happens when you try to update the same data, then we will do it. Optimistic handling of concurrent access conflicts is more complicated than pessimistic, but it works more efficiently in modern applications with a large number of users. Imagine that in order to look at a product from a store, you would have to wait, because someone else is looking at it at the moment.
Conflict detection
If you have a special Version field (or for example, the last update date), then when prompted, you can simply check if the entry has changed since you read it. If you do not have this field, then you need to decide which fields are involved in conflict detection.
When you need to update a record, instead of specifying only the primary key in the WHERE clause , you specify with it all the columns involved in the update.
For example, suppose you want to update a Customer object and assign new values to the fields CompanyName, ContactTitle, ContactName . And let's say that you want the CompanyName (always), ContactName (only when updating) fields , and ContactTitle to participate in the search for a conflict. In this case, the request may be as follows:
In this example, the column values in the where clause are the original column values that were read from the database. As you can see, the ContactTitle field was not involved in the search for conflicts, because we decided that it was less important to us.
Instead of specifying columns, we can also use the Version ... field in which to store the version number, or the date of the last update. In this case, this field will be updated when the version is updated in any of the connections.
If someone updated the record after we read it, then our query will not change the records in the database. To do this, after the requests we will check @@ ROWCOUNT and find out if the record has been updated. And if not, then there was a concurrent access conflict.
After the conflict has been found - it is necessary to solve it. Conflict resolution can be different, but in the second part of the article we will look at how this is done in LINQ to SQL, and maybe my colleague will describe how it is done in Hibernate (for the third part of the article).
Pessimistic way to solve
As the name suggests, the pessimistic approach suggests the worst thing is that the post you are reading will cause a conflict at the time it is updated. Fortunately, this is easy to do; you just need to put the reading and update in the database inside a single transaction.
With a pessimistic approach to concurrency, there are no conflicts that need to be resolved because the database is locked by your transaction, so no one else can modify it behind you.
But as I said, this method is less suitable for applications that require the ability to work simultaneously with data from several connections, since When locks are set, nobody can update the data until the lock is released.
There can always be such a situation when we read some records in one connection and then tried to update them. But for the moment when we edited them, and then tried to save, in another connection, the same records were already updated. In other words, the first process reads the data, after which the second process reads the same data, and the second process updates the same data before the first process can do it, a conflict will arise when the first process tries to update this data.
If the database is accessed from several connections and changes are made, then the occurrence of conflicts is only a matter of time and luck.
')
The application itself must decide what actions it needs to do to resolve this conflict. For example, the situation may be as follows: the site administrator has visited a page displaying data of a regular user (the administrator has the ability to update this data). If after the page of the administrator reads user data from the database, and the normal user access the page displaying his user data , and will make changes, then a conflict will arise when the administrator saves their changes. If the conflict does not arise - then the changes of the normal user will be blocked and lost. It may be otherwise - the administrator changes are lost. What kind of behavior should be true in each case - this is a difficult problem. The first step is to discover it. The second is to allow. There are two basic approaches to resolving concurrent access conflicts - optimistic and pessimistic.
The first part of this article is devoted to the problem itself and how to solve it. The second part of the article will describe how to resolve conflicts in LINQ to SQL. It may be the third part, if I manage to persuade a colleague to describe ways to resolve conflicts in Hibernate (but this will be known later). These articles will occupy much more space, so the first part is described separately, although it is rather short.
Optimistic solution
The optimistic way comes from the fact that in most cases conflicts do not occur. Therefore, no lock on the record is not installed. And when suddenly a conflict happens when you try to update the same data, then we will do it. Optimistic handling of concurrent access conflicts is more complicated than pessimistic, but it works more efficiently in modern applications with a large number of users. Imagine that in order to look at a product from a store, you would have to wait, because someone else is looking at it at the moment.
Conflict detection
If you have a special Version field (or for example, the last update date), then when prompted, you can simply check if the entry has changed since you read it. If you do not have this field, then you need to decide which fields are involved in conflict detection.
When you need to update a record, instead of specifying only the primary key in the WHERE clause , you specify with it all the columns involved in the update.
For example, suppose you want to update a Customer object and assign new values to the fields CompanyName, ContactTitle, ContactName . And let's say that you want the CompanyName (always), ContactName (only when updating) fields , and ContactTitle to participate in the search for a conflict. In this case, the request may be as follows:
UPDATE Customers
SET CompanyName = 'Art Sanders Park',
ContactName = 'Samuel Arthur Sanders',
ContactTitle = 'President'
WHERE CompanyName = 'Lonesome Pine Restaurant' AND
ContactName = 'Fran Wilson' AND
CustomerID = 'LONEP'
In this example, the column values in the where clause are the original column values that were read from the database. As you can see, the ContactTitle field was not involved in the search for conflicts, because we decided that it was less important to us.
Instead of specifying columns, we can also use the Version ... field in which to store the version number, or the date of the last update. In this case, this field will be updated when the version is updated in any of the connections.
If someone updated the record after we read it, then our query will not change the records in the database. To do this, after the requests we will check @@ ROWCOUNT and find out if the record has been updated. And if not, then there was a concurrent access conflict.
After the conflict has been found - it is necessary to solve it. Conflict resolution can be different, but in the second part of the article we will look at how this is done in LINQ to SQL, and maybe my colleague will describe how it is done in Hibernate (for the third part of the article).
Pessimistic way to solve
As the name suggests, the pessimistic approach suggests the worst thing is that the post you are reading will cause a conflict at the time it is updated. Fortunately, this is easy to do; you just need to put the reading and update in the database inside a single transaction.
With a pessimistic approach to concurrency, there are no conflicts that need to be resolved because the database is locked by your transaction, so no one else can modify it behind you.
But as I said, this method is less suitable for applications that require the ability to work simultaneously with data from several connections, since When locks are set, nobody can update the data until the lock is released.
Source: https://habr.com/ru/post/86302/
All Articles