On line Problem Troubleshooting and Solving in python Programming Project

Catalog Problem Description Problem Analysis Problem Solving Summary

Varnish light

Source: Python Technology "ID: pythonall"

Recently, I encountered a small problem in my development, because there are problems in my business design, which leads to the database table being locked all the time, and it is irregularly locked, which leads to the abnormal operation of the server. Finally, after investigation, the reason is that multiple threads update the same table at the same time, which leads to the problem. Today, let's talk about how to avoid this problem.

Problem description

Recently, because of the company's business needs, the product has designed a business system, which is said to be used by many internal and external personnel. Getting the system shows that our R&D department is desperate to work overtime and rush time. After two months, the system is finally put into operation.

At the beginning, there were few people who used it, and there was no problem. I felt that the system was still very stable. With more and more people who used it later, the system began to have some inexplicable problems, among which some business information always reported errors when updating. When checking the log, it was found that the table records were locked and the update failed.

After finding the error problem, we started to turn over the log over and over again, and analyzed all kinds of reasons to find out what caused the table records to be locked. Finally, it is found that the status field of this table has multiple interface methods updated at the same time, and often operates at the same time. That is, there are multiple sessions in the database that operate on the same 1 row of records in the same 1 table at the same time, resulting in the table records being locked.

Problem analysis

How to solve the problem? Here we first understand two nouns:

Pessimistic lock (Pessimistic Lock): The simple explanation is very pessimistic. Every time you go to get the data, you think that others will modify it, so you will lock it every time you modify the data, so that others will wait for 1 until it can get the lock.

Optimistic lock (Optimistic Lock): This is just the opposite, that is, it is very optimistic. Every time you modify the data, you think that others will not modify it, so you will not lock it. However, when you submit the update, you will judge whether others have updated the data during this period. Optimistic locking is suitable for application scenarios with more reads and less writes, which can improve throughput.

The problem can be solved in these two ways, but which one is better, let's briefly analyze it:

Pessimistic locking is implemented by "select … for update", that is, locking this record before updating the table, and then executing the update statement below. However, this method is too heavy. After all, locking still takes a lot of time and cost, which does not meet the needs of business. pass is dropped directly. Optimistic locking is relatively much lighter, and its main implementation is to add one more record version field in the table, such as version. Then every time the query record is updated, where is followed by version =? In this way, when you get version, if other sessions update this field, this version will be different from what you get now, which will invalidate your update this time. You need to get the latest version again and execute the update statement again.

Problem solving

Ok, after the above analysis, there is a clear solution, and the rest is the code code:

    /**
     *  Optimistic lock update 
     * @param id
     * @return
     */
    public boolean update(int id){
        int cnt = 0;
        while (cnt == 0) {
            USER user = query("SELECT * FROM table_user WHERE id = #{id}", id);
            cnt = update("UPDATE table_user SET version=version + 1, status = 2 WHERE id=#{id} AND version=#{version}", id, user.version());
            if(cnt > 0){
               //  Return Update Success 
                return true;
            }
        }
        return false;
    }
 

Summarize

This is only based on the characteristics of Mysql. Of course, there are many other ways to solve this problem, such as Redis or MQ message queue. If you are interested, we can introduce it later.

For more information about troubleshooting and solving problems on python, please pay attention to other related articles on this site!