Database. Concurrent Access Conflicts (Part 1 - Finding a Problem)

    Dear colleagues, in this article we will consider not types of locks in SQL, but methods of solving problems when we access the same data from different connections, and some of the 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 while we edited them, and then tried to save, in the same connection these 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, then a conflict will arise when the first process tries to update this data.

    If you access the database from several connections and make changes, the occurrence of conflicts is only a matter of time and luck.

    The application itself must decide what actions it needs to take to resolve this conflict. For example, the situation may be as follows: the site administrator went to a page that displays the data of a regular user (the administrator has the ability to update this data). If, after the admin page reads user data from the database, and a regular user accesses a page that displays his user data , and makes changes, there will be a conflict when the administrator saves his changes. If the conflict does not arise, then the changes of the ordinary user will be blocked and lost. It may be otherwise - administrator changes are lost. What behavior should be true in each case - this is a complex problem. The first step is to find it. The second is to allow.

    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. Perhaps the third part will be if I manage to persuade a colleague to describe ways to resolve conflicts in Hibernate (but this will be known later). These articles will take up much more space, so the first part is described separately, although it is quite brief.

    Optimistic solution

    The optimistic way is based on the fact that in most cases there are no conflicts. Therefore, no write locks are set. And when a conflict suddenly occurs when you try to update the same data, then we will do it. The optimistic processing of concurrent access conflicts is more complicated than the 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 viewing it at the moment.

    Conflict detection

    If you have a special Version field (or for example, the date of the last update), then when prompted, you can simply check if the record has changed after you read it. If you do not have this field, then you need to decide which fields are involved in the detection of conflicts.

    When you want to update a record, instead of specifying only the primary key in the WHERE clause , you specify along with it all the columns involved in the update.

    For example, suppose you want to update the Customer object and assign new values ​​to the CompanyName, ContactTitle, ContactName fields . And suppose you want the fields CompanyName (always), ContactName to participate in the search for conflict(only during the upgrade), but ContactTitle did not participate. In this case, the query may be as follows: In this example, the column values ​​in the where clause are the initial values ​​of the columns that were read from the database. As you can see, the ContactTitle field did not participate in the conflict search, as 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 updating the version in any of the connections. If someone updated the record after we read it, then our request will not change the records in the database. For this, after requests, we will check

    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'






    @@ ROWCOUNT and find out if the record has been updated. And if not, then there was a parallel access conflict.

    After the conflict has been found - it is necessary to resolve it. Conflict resolution can be different, but in the second part of the article we will consider in detail how this is done in LINQ to SQL, and maybe my colleague will describe how this is done in Hibernate (for the third part of the article).

    A pessimistic way of solving

    As the name suggests, a pessimistic approach suggests the worst that the record you are reading will cause a conflict at the time it is updated. Fortunately, this is not difficult to do, just put the reading and updating in the database inside one transaction.

    With a pessimistic approach to parallelism, there are no conflicts that need to be resolved, because the database is blocked by your transaction, so no one else can modify it behind you.

    But as I already said, this method is less suitable for applications in which the ability to simultaneously work with data from multiple connections is required, because when setting locks, no one can update the data until the lock is released.

    Also popular now: