What are transactions and stored procedures in mysql

In mysql, a transaction is a user-defined data operation sequence, including a set of database operation commands; a transaction submits or revokes operation requests to the system as a whole, that is, this set of databases Either all commands are executed or none are executed, so a transaction is an indivisible logical unit of work. A stored procedure is a set of SQL statements designed to accomplish a specific function; a stored procedure is a programmable function that is created and saved in the database and generally consists of SQL statements and some special control structures.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Transaction in mysql

Database transaction (Transaction) is a mechanism and a user-defined sequence of data operations, including A set of database operation commands. A transaction submits or revokes an operation request to the system together with all commands as a whole, that is, this set of database commands are either executed or not executed, so the transaction is an indivisible logical unit of work.

When performing concurrent operations on a database system, transactions are used as the smallest control unit, which is especially suitable for database systems operated by multiple users at the same time. For example, airline booking systems, banks, insurance companies, and securities trading systems.

MySQL transactions are mainly used to process data with large operations and high complexity. For example, in the personnel management system, if you delete a person, you need to delete not only the basic information of the person, but also the information related to the person, such as mailbox, articles, etc. In this way, these database operation statements constitute a transaction !

• In MySQL, only databases or tables using the Innodb database engine support transactions.

• Transaction processing can be used to maintain the integrity of the database and ensure that batches of SQL statements are either all executed or not executed at all.

• Transactions are used to manage insert, update, and delete statements

Generally speaking, transactions must meet four conditions (ACID):: Atomicity, or indivisibility, consistency, isolation, independence, and durability.

• Atomicity: All operations in a transaction will either be completed or not completed, and will not end in any intermediate link. If an error occurs during the execution of the transaction, it will be rolled back to the state before the transaction started, as if the transaction had never been executed.

• Consistency: The integrity of the database is not destroyed before the transaction starts and after the transaction ends. This means that the data written must fully comply with all preset rules, including the accuracy and concatenation of the data, and that the subsequent database can spontaneously complete the predetermined work.

• Isolation: The database allows multiple concurrent transactions to read, write and modify its data at the same time. Isolation can prevent data corruption due to cross execution when multiple transactions are executed concurrently. Inconsistent. Transaction isolation is divided into different levels, including read uncommitted, read committed, repeatable read and serializable.

• Persistence: After the transaction is completed, the modification to the data is permanent and will not be lost even if the system fails.

Stored procedures in mysql

Stored procedures are a group of stored procedures stored in the database directory to complete specific functions A collection of SQL statements. The purpose of using stored procedures is to pre-write common or complex work with SQL statements and store them with a specified name. This procedure is compiled and optimized and stored in the database server, so it is called a stored procedure. When you need the database to provide the same service as the defined stored procedure in the future, you only need to call "CALL stored procedure name" to automatically complete it.

SQL statements commonly used to operate the database need to be compiled first and then executed when executed. Stored procedures take another approach to executing SQL statements.

A stored procedure is a programmable function that is created and saved in the database. It generally consists of SQL statements and some special control structures. Stored procedures are particularly suitable when you want to perform the same specific function on different applications or platforms.

MySQL 5.0 version did not support stored procedures before, which greatly reduced the application of MySQL. MySQL has supported stored procedures since version 5.0, which not only improves the processing speed of the database, but also improves the flexibility of database programming.

Stored procedures are an important function in the database. Stored procedures can be used to convert data, It is similar to a programming language for data migration and report making. Once executed successfully, it can be called at any time to complete specified functional operations.

Using stored procedures can not only improve the efficiency of database access, but also improve the security of database use.

For the caller, the stored procedure encapsulates the SQL statement, and the caller does not need to consider the specific implementation process of the logical function. Just a simple call, it can be called by triggers, other stored procedures, and applications such as Java, Python, PHP, etc.

Advantages of MySQL stored procedures

• Usually stored procedures help improve application performance. Once created, the stored procedure is compiled and stored in the database. However, MySQL implements stored procedures slightly differently. MySQL stored procedures are compiled on demand. After compiling the stored procedure, MySQL puts it into cache and maintains its own cache of stored procedures for each connection. If the application uses the stored procedure multiple times in a single connection, use the compiled version, otherwise the stored procedure works like a query.

• Stored procedures help reduce traffic between the application and the database server because the application must send only the name and parameters of the stored procedure instead of sending multiple lengthy SQL statements .

• Stored procedures are reusable and transparent to any application. Stored procedures expose the database interface to all applications so that developers do not have to develop functionality that is already supported in stored procedures.

• Stored procedures are safe. The database administrator can grant appropriate permissions to applications that access stored procedures in the database without providing any permissions to the underlying database tables.

[Related recommendations: mysql video tutorial]

The above is the detailed content of What are transactions and stored procedures in mysql. For more information, please follow other related articles on the PHP Chinese website!