Shulou(Shulou.com)05/31 Report--

This article shows you what the role of SQL trigger is, the content is concise and easy to understand, can definitely brighten your eyes, through the detailed introduction of this article, I hope you can get something.

The function of trigger

1. Security, which can give users some right to operate the database based on the value of the database.

2. Audit, which can track the user's operation to the database.

3. Realize the complex non-standard database-related integrity rules, and the trigger can update the related tables in the database.

4. Triggers can reject or roll back changes that undermine the integrity of the relevant changes and cancel transactions that attempt to update data. This trigger works when a foreign key that does not match its primary key is inserted.

5. Copy the data in the table synchronously and in real time

6. Automatically calculate the data value, and carry out specific processing if the data value meets certain requirements.

Deadlock in mysql: refers to the phenomenon that two or more processes wait for each other because of competing for resources during execution. If there is no external force, they will not be able to push forward. At this point, it is said that the system is in a deadlock state or the system has a deadlock, and these processes that are always waiting for each other are called deadlock processes. Table-level locks do not produce deadlocks. So solving deadlocks is mainly aimed at the most commonly used InnoDB.

The key to deadlocks is that two (or more) Session locks are not in the same order.

Then the corresponding key to solve the deadlock problem is to make different session locks in order.

Deadlock cases in mysql

Demand: divide the investment money into several pieces and randomly distribute it to the borrower.

At first, the business program thought like this:

Investors invest, the amount will be randomly divided into several, and then randomly selected from the borrower table, and then through a select for update to update the balance in the borrower table and so on.

For example, if two users invest at the same time, the amount of user An is randomly divided into two parts and distributed to the borrower.

B the amount of user is randomly divided into two parts, which are distributed to the borrower.

Deadlocks occur very quickly, of course, because of the different order of locking.

The improvement to this problem is as simple as locking up all assigned borrowers at once.

Select * from xxx where id in (xx,xx,xx) for update

The list value mysql in in is automatically sorted from small to large, and locks are also added from small to large.

The above is what the function of SQL trigger is, have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.