Shulou(Shulou.com)06/02 Report--

This article introduces the knowledge of "the reason why there are fewer deadlocks in MYSQL than other databases". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

There are several main reasons.

1 the design of the traditional system is basically completed around the query and DML operation of one or more core tables, while the general traditional system may be designed at the beginning because of the size and volume of business, the original intention of developing and designing the core table is relatively simple, but with the complexity of the business, or the increase of business volume, the design of the original core table will produce bottlenecks. Imagine if it is the core staff of a company. The principle is only responsible for A business, and later, because business expansion B, C, D business all need him to copy, and there are other businesses that need him to coordinate, or ask him about his development, he is good at it, and can only handle one event at the same time. Other events are in the state of wait, and if two or more tasks need him at the same time, these transactions will fight, and in the end, who is more important will occupy the core personnel.

So in the design of the system, with the expansion of the business, we may need to find a helper for the core personnel, or directly decompose his functions, we must be able to give a core staff to find a helper.

It is easier said than done. The first problem we are faced with is that if the functions of the core table overlap, the fields also overlap. How to put part of the same data into two or more tables at one input? this is a question, whether to do it through a program, or to pass it through other ways. If it is done through a program, it is good to turn it into a transaction. It is better to pass messages like MQ, or to replicate with third-party database tables.

So as a system becomes more and more complex, there are more things to test table designers, or BDER. At this time, if DBER still focuses on basic database knowledge, and there is no business or other broader knowledge, then you can only listen to others, not play the role you should have.

Conversely, why does the title mention the lack of deadlocks in MYSQL?

Generally speaking, most of the enterprises that use MYSQL are Internet enterprises, while the business of Internet enterprises is relatively simple, and the level of technical personnel of Internet enterprises is higher than that of traditional enterprises.

2 part of the core business of the enterprise that uses MYSQL is in sub-database, or sub-table. Through sub-database and sub-table, this kind of problem can be resolved to a certain extent, and the coupling degree of table in providing information can be reduced. In fact, it is the same sentence, space changes time.

(3) most systems that use MYSQL also use read-write separation, which also helps to resolve the problem between query and DML operation, and most of the problems of deadlock are caused by this aspect.

So this is the main reason why people in some of the above groups mentioned why MYSQL has fewer deadlocks than other database systems.

In fact, there is another reason. The DML operation of some Internet enterprises is not carried out through the DML of simple conditions, but the DML operation is carried out according to the primary key. It is necessary to obtain the primary key that requires DML operation before the DML operation, while the primary key operation in MYSQL is very fast. The specific principle will no longer be mentioned here to avoid motherhood.

And whether other database systems can also be used, the answer is yes.

SQL SERVER Always on can separate reading and writing, and PG is born with such genes. There are all kinds of data replication technologies. Basically, the technology used on MYSQL to separate reading and writing on PG is OK.

The reason for not mentioning ORACLE here is that 2, 1 ORACLE is different from other databases in buffer memory design, 2 table designers using ORACLE database are more traditional, the design mode of deadlock above is related to the traditional three paradigms and traditional table design, and the separation of read and write and primary key operation are not realistic on ORACLE. Because the design idea of ORACLE database itself is the idea of single capacity expansion, which itself is the cause of the bottleneck.

In fact, when it comes to table design, it is generally not particularly thoughtful at the initial stage. 1 the volume of business and the clarity of the business may not be able to meet the requirements that need to be considered at the beginning of the design. 2 the database functions that are not MYSQL itself can accommodate some unreasonable designs and queries.

Because of this, other databases are more likely to have problems with the passage of time and the expansion of business than those using MYSQL.

In the end, if the use of MYSQL database is chaotic and unreasonable, the database will not work long before the deadlock breaks out.

This is the end of the content of "Why there are fewer deadlocks in MYSQL than other databases". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.