Concurrent database operations

? Database concurrent operations bring about different data problems, including three types of 2 lost modifications when a transaction modifies the data, and such modifications have not yet been committed to the database, another transaction modified the same data and committed the modification to the database. In this way, there is no knot in the database for the first transaction to modify data.

? Database concurrent operations bring about different data problems, including three types of 2 lost modifications when a transaction modifies the data, and such modifications have not yet been committed to the database, another transaction modified the same data and committed the modification to the database. In this way, there is no knot in the database for the first transaction to modify data.

? Problems caused by concurrent database operations

Data is different, including three types

2. Lost changes

When a transaction modifies data and the modification has not been committed to the database, another transaction modifies the same data, and submit the modification to the database. In this way, there is no result of modifying the data of the first transaction in the database, as if this data modification was lost.

2 dirty reads

When a transaction is accessing the data and modifying the data, and the modification has not been committed to the database, another transaction also accesses the data and then uses the data. Because the data has not been committed, the data read by another transaction is dirty data, and the operations performed based on the dirty data may be incorrect.

2. Repeated read is not allowed.

Read the same data multiple times in a transaction. When the transaction is not completed, another transaction also accesses the same data. Therefore, due to the modification of the second transaction, the data read by the first transaction may be different.

? How can this problem be solved?

The method and technology to avoid inconsistency is concurrency control. The most common concurrency control technology is blocking technology.

2 concurrency control reason

Databases share resources, and many transactions are usually running at the same time.

When multiple transactions concurrently access the database, the same data will be read and/or modified at the same time. If concurrent operations are not controlled, incorrect data may be accessed and stored, compromising Database Consistency. Therefore, the database management system must provide a concurrency control mechanism.

Solution

2. Prohibit data from being inconsistent-blocking technology l what is blocking?

Blocking means that transaction T sends a request to the system before performing operations on a data object, such as a table or record, to lock it. After the lock, transaction T has some control over the data object. Before transaction T releases its lock, other transactions cannot update the data object. Blocking is a very important technology for implementing concurrency control.

L blocking type

1. Exclusive Locks (X lock)

2. Share Locks ).

N exclusive lock

Exclusive locks are also called write locks. If transaction T adds an X lock to Data Object a, only T is allowed to read and modify A. No other transaction can add any type of lock to, wait until T releases the lock on. This ensures that other transactions cannot read or modify A before T releases the lock on.

N shared lock

A shared lock is also called a read lock. If transaction T adds the S lock to Data Object A, transaction T can read A but cannot modify A. Other transactions can only add the S lock to transaction A instead of the X lock, until T releases the S lock on. This ensures that other transactions can read A, but cannot modify A before T releases the S lock on.

2. Data may be inconsistent, but data update requires consistency check.

1) read a batch of initial data from the database and perform a series of operations based on the data. When the operation results are updated to the database, first, check whether the initial data of the "same batch" in the database has changed. If yes, perform some processing based on the business needs. This method features high concurrency, flexibility, and no lock, which avoids a lot of trouble and is easier to implement.

2) When checking data consistency, you can check whether the data values in the database are the same as those in the memory. However, if the data volume is large, this check may be inefficient. In fact, the consistency check should consider the granularity issue and select the appropriate granularity based on the actual situation. Checking whether the value of the specific data changes is the finest granularity. You can use data version control to perform more coarse-grained checks. Version Management is performed based on data records. You can add an integer field to the data table as the version. When you insert a data entry, set an initial version value. When updating data, first check whether the database version value has changed. If there is no change, execute update and add the updated data version value to 1. According to the business logic, sometimes a group of data needs to be managed using one version (with a coarse granularity), which can be achieved using a table with a dedicated storage version. Note that consistency check and data update must be performed in the same system transaction to ensure consistency.

3) at the same time, this method also has some problems, that is, the lag of the problem found. The error is usually reported after the submission, but it will not be reported before, in this way, the work done between users may be useless. One way to alleviate this problem is to check the consistency in the final submission, in addition to checking the consistency during the business transaction process, check it from time to time. The sooner you find the inconsistency, the sooner you can handle it. This is only a relief and cannot be fundamentally solved.

? Summary

There are two advantages and disadvantages of transactions. The advantage of data concurrency control is that databases share resources and many transactions are usually running at the same time; one disadvantage is the problems it brings. There is a trade-off between correctness and concurrency. These two aspects are sometimes contradictory and cannot be solved perfectly. The two sides can only achieve a balance, and none of them is the best, it depends on the user's need for that aspect.