Common concurrency problems in web systems

Talk about the test system in some of the concurrency problems, these days focused on solving the problem on the side of the concurrency problems.

In fact, the concurrency problem is simply:

1. When a question is inserted, the result of the course to which the topic belongs is deleted;

2. When a course is deleted, the questions under this course are not used and can be deleted, and a question is used before the DELETE statement is going to be executed;

For the first problem, it can be solved with a foreign key. But the company seems to have rules that cannot use foreign keys. The main reason is that foreign keys are more serious for database performance, especially when the amount of data is large.

In fact, the foreign key for our exam system is also not good, because the system is deleted is a tombstone, just is_delete to 1. What if the foreign key is not good? MySQL seems to write check constraints also do not execute, how to do?

I started with a trigger method. When inserting a question, check the validity of the CourseID in course. But this workaround is not good because even if the CourseID is found to be invalid and cannot break the INSERT statement in the trigger, what I do is to set the Is_delete field of the inserted new question to 1. That is, an invalid question is inserted.

Insert Trigger

CREATE TRIGGER Checkcourse_beforeinsert before INSERT on question for each        ROW             BEGIN                if EXISTS (    WHERE course.id = new.course_id and course.is_delete = 1) then                    SET new.is_delete = 1;                END IF;             END;

After communicating with DBA Lugo this morning, it suggested that I make a transaction of select and insert, that is, to check if the course is effective before continuing with the insertion. But select is not locking the table, so the select here is SELECT * * * * for UPDATE. It is able to lock this line of the course ID of the query. Ensure that the line is not modified within the entire transaction.

Select for Update

SELECT Course.is_delete as Isdelete from course WHERE course.id = #{courseid} for UPDATE;

For the second problem, the idea of optimistic locking is generally adopted. My implementation is to query how many bars will be deleted before execution, then execute the DELETE statement, return the number of rows actually deleted, and throw runtimeexception to rollback when inconsistencies are found.

In fact, these concurrency problems can be said that any web system will encounter problems. The best solution should be the version number mechanism.

The version number mechanism is to make a version of each record of the database, each modified the record, the version number plus one, each time the result of the query carries a version number. In the second example, the ID of all the topics to be deleted is first queried, and the version number is carried on, and when the deletion is found to be inconsistent, it indicates that the record has been modified and processed, such as throwing an exception rollback. In the database of this exam system, each table has a updatetime field that can be used as the version number.

The way in which @trancational annotations are tagged in ps:spring is not related to the same class as whether it is the same thread when nested calls to each other. As long as it is the Trancational method, it determines whether to create a new transaction or join a transaction that already exists, depending on the value of the propagation property.

Common concurrency problems in web systems