Explanation of the Difference between MySQL Database Stored Procedures and Transactions

Transactions are atomic in nature that guarantee multiple SQL statements, that is, either one complete or one incomplete

Stored procedure is a batch of SQL statements pre-compiled and placed on the server, and then can be called remotely

Stored procedure:

1 set of SQL statements (or custom database operation command sets) to complete specific functions, According to the incoming parameters (or not), through a simple call, it completes more complex functions than a single SQL statement, which is stored in the database server, and only needs to be compiled once and then used again without compiling: it mainly controls the stored process.

Advantages:

1. Fast execution speed. Especially for complex logic, it reduces the consumption between network traffic. Another important point is that stored procedures are compiled only when they are created, and there is no need to recompile every time they are executed in the future. However, SQL statements are compiled once every time they are executed, so using stored procedures can improve the execution speed of databases. .

2. Improve work efficiency. Writing program is simple, using stored procedures to call classes, calling any stored procedures only need 1-2 lines of code.

3. Standardize program design, upgrade and maintain conveniently.

4. Improve system security. You can configure that only a user has access to the specified stored procedure.

Projects with a small amount of data or no money can work normally without stored procedures. The stored procedure of mysql has yet to be tested. If it is a formal project, it is recommended that you use sql, server or oracle stored procedures. When dealing with data, the process will come much faster than the program.

Disadvantages:

Some functions of the program are moved to the database, which destroys the design of CVM 3-tier structure

Matters:

1 series of data change operations. These operations include stored procedures, change statements, and other operations. 1 Once an operation contained in a transaction fails or the user aborts, the user can control to undo all operations in the transaction body and return to the state before the transaction started. The operation in a transaction is a whole, which is either completed as a whole or not done at all. Thereby ensuring the integrity of the data.

You can have stored procedures in a transaction or transactions in a stored procedure.

When is a good time to use stored procedures?

1. When one business processes multiple tables at the same time, it is more appropriate to use stored procedures.

2. Stored procedures are used for complex data processing, such as some report processing.

3. Multi-condition and multi-table joint query, and do paging processing.

When is a good time to use transactions?

Every time a transaction is used, it will consume 1 fixed overhead. In addition, transactions may lock rows of 1 table. Therefore, unnecessary transactions will lead to performance loss. There is a rule that transactions should only be used when required by operations. For example, if you only query a few records from the database, or if you execute a single query, you don't need an explicit transaction most of the time, because the declarations are encapsulated in implicit transactions. However, as mentioned earlier, it is important for multi-declaration updates because transactions can actually speed up operations. Similarly, if you need to choose between saving milliseconds or compromising data integrity, the correct answer is to keep the data clean and don't worry about the milliseconds.

Another thing to pay attention to before using transactions is to keep transactions as short as possible. Avoid using SELECT in a transaction to return data unless the statement depends on the returned data. If you use the SELECT statement, select only the rows you want, so do not lock out too many resources while maintaining the highest possible performance. In the case of schema word order, remove all SELECT statements from the transaction. This is done because the transaction locks all rows being manipulated during processing, which affects the execution of other concurrent sql statements.

Summarize