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

Transactions are guaranteed to be atomic in multiple SQL statements, that is, they are either completed or not completed together.

The stored procedure is to precompile a batch of SQL statements on the server, and then you can call the

Stored procedure:

A set of SQL statements in order to complete a specific function (or custom database operation command set), according to the passed parameters (or no), through a simple call to complete a more complex function than a single SQL statement, stored in the database server, only need to compile once and then use again do not need to compile: mainly to control the stored process.

Advantages:

1. The execution speed is fast. Especially for the more complex logic, reduce the consumption between network traffic, another more important point is that the stored procedure is only compiled when it is created, and there is no need to recompile every time the stored procedure is executed, while the general SQL statement is compiled every time it is executed, so the use of stored procedure can improve the execution speed of the database.

2. Improve work efficiency. It is easy to write the program, using the stored procedure call class, and calling any stored procedure is only 1-2 lines of code.

3. Standardize the program design, upgrade and maintain conveniently.

4. Improve the security of the system. It can be set that only a user has the right to use the specified stored procedure.

Projects that have a small amount of data or have nothing to do with money can work without stored procedures. The mysql stored procedure has yet to be tested in practice. 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.

Disadvantages:

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

Transaction:

A series of data change operations are formed as a whole. These operations include stored procedures, change statements, and other operations. Once an operation contained in the 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, either completed as a whole or not done at all. So as to ensure the integrity of the data.

There can be a stored procedure or a transaction in a transaction.

When is the right time to use stored procedures?

1. When a 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 paging processing.

When is the right time to use transactions?

Each time a transaction is used, it takes up a certain amount of overhead. In addition, transactions may lock some table rows. Therefore, unnecessary transactions can lead to performance loss. There is a rule that transactions are used only when the operation requires it. For example, if you just query some records from the database, or execute a single query, you don't need an explicit transaction most of the time, because the declaration is already encapsulated in an implicit transaction. However, as mentioned earlier, it is important to declare multiple updates because transactions can actually speed up operations. Similarly, if you need to make a choice between saving milliseconds and compromising data integrity, the right answer is to keep the data clean and don't worry about milliseconds.

Another thing to note before using a transaction is to keep the transaction as short as possible. Avoid using SELECT in a transaction to return data unless the statement depends on the returned data. If you use a select statement, select only the rows you want, so don't lock too many resources while maintaining the highest possible performance. Remove all SELECT statements from the transaction when the word order is structured. This is done because the transaction locks all rows of data being manipulated during processing, which affects the execution of other concurrent sql statements.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

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.