Inconsistency between concurrent operations and data

In the recent elective course system, we need to consider the problem of database data inconsistency caused by concurrent database operations-because it is a school-wide Elective Course, a large number of students choose courses at the same time, therefore, it is inevitable that multiple students operate on the same data at the same time. If such concurrent operations are not controlled, data inconsistency will occur.

 

I always knew that there was such a problem, and I knew the solution to this problem-locking. But some of the things I did not know very well before, so I studied it for a day and thought it was okay to understand it, summary.

 

1. What are concurrent operations?

An important feature of a database is data sharing. That is to say, multiple user programs are allowed to access data in the database in parallel. Therefore, multiple users or multiple transactions may simultaneously operate on the same data, this becomes a concurrent operation.

 

2. What is the possible impact of concurrent operations?

If concurrent operations are not controlled, incorrect data will be accessed, undermining data integrity. -- Why? The following describes

 

Before that, let's talk about the concept of transactions.

3. What is a transaction?

For a complete task, either complete or not. For example, when selecting a course, you must add the course information to the course list and update the remaining course amount in the course list, if it is interrupted (the margin cannot be updated), the transaction will be rolled back, that is, the data will be rolled back before the course is selected.

Begin Transaction

Add course selection information to the course selection table;

Update the course allowance in the curriculum;

Commit

RollBac

 

SQL statement of the transaction:

Begin Transaction start Transaction

Commit a transaction

RollBack

(Note:Multiple SQL statements in a transaction are also executed one by one. After all statements are executed, the transaction is committed. If one of the statements is interrupted, the transaction rolls back all the operations and returns to the statement before execution)

Transaction concurrency is mainly used to improve efficiency, but it also brings some problems

 

4. What are the problems caused by concurrent operations?

Concurrent operations-operations on the same data at the same time by multiple users or transactions;

Because the statements in the transaction are also executed in one row, multiple users operate on the same data simultaneously in multiple transactions;

Concurrent operations:

(1) lost changes

(2) Dirty read

(3) Non-repeated read

 

4.1 lost Modification

When two or more transactions (or two or more users) Select the same row and update the row based on the originally selected value, the update will be lost. Every transaction does not know the existence of other transactions (or each user does not consider whether other users perform the same operation at the same time ). The last update will overwrite the updates made by other firms, which will lead to data loss.

 

Example 1: Transaction T1, transaction T2, database data R = 1000

A. At the time of t1, transaction T1 reads R = 1000;

B. After a while at T2. transaction t2 reads R = 1000;

C. t3 moment, transaction T1 modify R = R-200 (then R = 800) write to the database (at this time, the database R = 800 );

D. after a while at the t4 moment, transaction T2 modifies R = R-100 (because the data read at the t2 moment is R = 1000, then the modified R = 900) writes to the database (at this time, R = 900 in the database)

 

In the end, R = 900 in the database; is the data correct? Of course not. Because T1 and T2 perform the-200-100 operation on R respectively, the final data should be R = 700. The modification of transaction T2 overwrites the modification of T1.

 

 

 

Example 2: Course Selection: Student A, student B, and course 1 margin = 20

 

A. When student A chooses course 1, he first reads the course balance of 20

B. HoweverAt the same time(But at different times, as long as the studentABefore updating data)Student B also read the 20 margin of course 1,

C. If student A chooses this course, course 1 margin-1 is written into the database, and course 1 margin = 19;

D. student B chooses this course. The margin of course 1 is-1 (because the margin of course 1 is 20, and the margin of course 1 is 19), it is written into the database. At this time, the margin of course 1 is 19;

Course allowance = 19 is the data correct? Incorrect. The actual remaining amount of the course should be updated to 18; -- B's modification overwrites A's modification -- loss of modification issues.

 

 

Solution: Lock, only one update transaction can be concurrently allowed.

 

4.2 dirty reads

When a transaction is accessing data and modifying the data, the modification has not been committed to the database,

Another transaction also accesses the data and then uses the data. Because this data is not submitted, then another

The data read by the transaction is dirty data, and operations performed based on dirty data may be incorrect.

 

Example 1: Transaction T1, transaction T2, data in the database R = 1000;

T1:

Begin Transaction start Transaction T1

ReadR = 1000; (1)

R = R-200; (2) at this time R = 800

R = R + 100; (3) R = 900

Commit commits transaction T1

 

A. After transaction T1 reaches (2), that is, data in the database R = 800;

B. The value of R when transaction T2 starts to read. The read value is R = 800. However, transaction T1 is not complete yet and has not yet committed the transaction;

C. After transaction T1, perform step (3) R = R + 100. At this time, R = 900 transaction T1 is committed, and R = 900 in the database;

 

Before transaction T1 is committed, the data read by transaction T2 (R = 800) is dirty data;

 

Example 2:

A. Michael's salary is 2000. The Yuan boss changed Michael's salary to 8000 yuan (but did not submit the transaction)

2. Michael Jacob checked his salary and found his salary changed to 8000 yuan.

3. Then the boss found that he had corrected the mistake and rolled back the transaction. Michael's salary changed back to 2000 yuan.

Then, Michael's salary of 8000 yuan is dirty data.

 

 

Solution: if any other transaction cannot read its modified value before the first transaction is committed, this problem can be avoided.

 

 

4.3 non-repeated read

When the second transaction accesses the same row multiple times and reads different data each time, an inconsistent analysis problem occurs. The inconsistent analysis is similar to the unconfirmed correlation because other transactions are also changing the data being read by the second transaction. However, in an inconsistent analysis, the data read by the second transaction is committed by a transaction that has been changed. Furthermore, the inconsistent analysis involves reading the same row multiple times (twice or more) and the information is changed by other transactions each time. Therefore, the row is read non-repeatedly.

 

The result of two reads before and after a transaction is inconsistent, resulting in non-repeated reads.

 

 

Example 1: Transaction T1, transaction T2, and Michael Jacob's salary = 1000

 

A. in transaction T1, Michael Jacob reads his salary of 1000 yuan, and the operation is not completed.

 

B. At this time (transaction T1 reads Michael's salary of 1000 RMB) in transaction 2, Michael's salary of 2000 RMB was modified and the transaction was submitted.

 

C. at this time (transaction T1 reads Michael Jacob's salary of 1000 yuan, transaction T2 changes Michael's salary of 2000 yuan) in transaction 1, when Michael Jacob reads his salary again, his salary is changed to 2000

 

In this case, the data read before and after the same transaction is inconsistent-the data cannot be read repeatedly.

 

Solution: this problem can be avoided if the data can be read only after the transaction is completely committed.

 

 

Summary:

(1) Transaction concurrency mainly aims to improve efficiency, but it also brings about some problems-loss of modification, dirty data reading, and non-repeated reads.

(2) understand the problems of loss and modification, dirty data reading, and non-repeated reading in combination with actual examples.

 

Personal Understanding: if there is any misunderstanding, forget to correct it!

 

The next blog will summarize the concept of "Lock" and explain how "Lock" solves the data inconsistency caused by concurrency.