The difference between MySQL database stored procedures and transactions

Transactions ensure the atomicity of multiple SQL statements, that is, they are either completed together or not completed together.

A stored procedure is a batch of SQL statements that are precompiled and placed on the server, and can then be called remotely.

Stored Procedure:

A set of SQL statements (or custom database operation command sets) to complete specific functions. Based on the parameters passed in (or not), through simple calls, it can complete more complex functions than a single SQL statement. It is stored on the database server and only needs to be compiled once and does not need to be compiled again for reuse: it mainly controls stored procedures.

advantage:

1. Fast execution speed. Especially for more complex logic, it reduces the consumption of network traffic. Another important point is that the stored procedure is only compiled when it is created, and there is no need to recompile each time the stored procedure is executed. Generally, SQL statements are compiled once each time they are executed, so using stored procedures can increase the execution speed of the database. .

2. Improve work efficiency. Writing programs is simple. Using the stored procedure calling class, calling any stored procedure only requires 1-2 lines of code.

3. Standardized program design, easy to upgrade and maintain.

4. Improve system security. You can set up a system to allow only certain users to have access to a specified stored procedure.

Projects with small amounts of data or that are not related to money can operate normally without stored procedures. The mysql stored procedure has yet to be actually 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 be much faster than the program.

shortcoming:

Some functions of the program were moved to the database, destroying the three-tier structure design of CVM

Transactions:

A series of data change operations. These operations include stored procedures, change statements, and other operations. Once an operation contained in a transaction fails or is terminated by the user, the user can control the undoing of all operations in the transaction body and return to the state before the transaction started. The operations in a transaction are a whole, either completed as a whole or not done at all. This ensures the integrity of the data.

A transaction can contain a stored procedure and a stored procedure can contain a transaction.

When is it appropriate to use a stored procedure?

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

2. Use stored procedures for complex data processing, such as some report processing.

3. Joint query of multiple conditions and multiple tables, and paging processing.

When is it appropriate to use transactions?

Every time you use a transaction, it takes up a certain amount of overhead. Additionally, transactions may lock rows of some tables. Therefore, unnecessary transactions can lead to performance loss. As a rule, use transactions only when the operation requires it. For example, if you are just retrieving some records from a database, or executing a single query, you don't need an explicit transaction most of the time, because the statements are already encapsulated in an implicit transaction. However, as mentioned above, it is very important when doing multi-statement updates, because transactions can actually speed up the operation. Likewise, if the choice is between saving a few milliseconds and compromising data integrity, the right answer is to keep the data clean and not worry about the few milliseconds.

Another thing to note before using transactions is: keep transactions as short as possible. Avoid using SELECT statements that return data within a transaction unless the statement depends on the returned data. If you use a SELECT statement, select only the rows you need, thus not locking too many resources while keeping performance as high as possible. In case of architectural order, remove all SELECT statements from the transaction. This is done because the transaction will lock all the data rows being operated on during the processing, which will affect the execution of other concurrent SQL statements.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links