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

How to handle transactions in mysql? I believe that many inexperienced people are at a loss about this, so this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

The characteristics of the transaction:

Transactions have the following four standard properties abbreviated ACID, commonly referred to as:

Atomicity: ensure that all operations in the unit of work are completed successfully, otherwise the transaction will be aborted at the point of failure, and previous operations will be rolled back to the previous state.

Consistency: transactions that ensure that the database commits successfully after the database changes state correctly.

Isolation: make transaction operations independent and transparent.

Persistence: it still exists when the system that ensures the result or effect of the committed transaction fails.

In MySQL, transactions start and end with COMMIT or ROLLBACK statements. A large number of transactions are formed between the SQL commands of the start and end statements.

COMMIT & ROLLBACK:

These two keywords commit and rollback are mainly used for MySQL transactions.

When a successful transaction is completed, issuing the COMMIT command should make the changes to all participating tables take effect.

If a failure occurs, you should issue a ROLLBACK command to return each table referenced in the transaction to its previous state.

The transaction behavior that can be controlled is called the AUTOCOMMIT setting session variable. If AUTOCOMMIT is set to 1 (the default), then each SQL statement (in transaction or not) is considered a complete transaction and promises by default when it is completed. When AUTOCOMMIT is set to 0, the command SET AUTOCOMMIT = 0 is issued, and the subsequent series of statements act like a transaction until there is no active commit when there is an explicit COMMIT statement.

You can execute these SQL commands in PHP by using the mysql_query () function.

General transaction example

This series of events is independent of the programming language used and can be established in any language used to create the logical path of the application.

You can execute these SQL commands in PHP by using the mysql_query () function.

BEGIN WORK starts a transaction and issues a SQL command

Issue one or more SQL commands, such as SELECT,INSERT,UPDATE or DELETE

Check to see if there are any mistakes, everything is based on the needs.

If there are any errors, then issue the ROLLBACK command, otherwise issue the COMMIT command.

Transaction security table type in MySQL:

If you plan to use MySQL transaction programming, you need a special way to create tables. There are many transactions supported but the most popular is the InnoDB table type.

When compiling MySQL from source code, InnoDB table support requires specific compilation parameters. If the MySQL version does not support InnoDB, ask your Internet service provider to establish a version of MySQL that supports InnoDB table types, or download and install Windows or Linux/UNIX 's MySQL-Max binary distribution and use the table types in the development environment.

If the MySQL installation supports InnoDB tables, simply add a TYPE=InnoDB definition table creation statement. For example, the following code creates the InnoDB table tcount_tbl:

Root@host# mysql-u root-p password;Enter password:*mysql > use TUTORIALS;Database changedmysql > create table tcount_tbl-> (- > tutorial_author varchar (40) NOT NULL,-> tutorial_count INT->) TYPE=InnoDB;Query OK, 0 rows affected (0.05 sec)

You can use other GEMINI or BDB table types, but it depends on your installation if it supports both types.

As the project design involves the transfer of money, it is necessary to use the transaction processing of MYSQL to ensure the correctness of a set of processing results. With transactions, it is inevitable to sacrifice part of the speed to ensure the correctness of the data.

Only InnoDB supports transactions

Transaction ACID Atomicity (atomicity), Consistency (stability), Isolation (isolation), Durability (reliability)

1. Atomicity of transactions

A set of transactions that either succeed or withdraw.

2. Stability

There is illegal data (foreign key constraints and so on) and the transaction is withdrawn.

3. Isolation

Transactions run independently.

If the result of one transaction affects other transactions, the other transactions will be withdrawn.

100% isolation of transactions requires the sacrifice of speed.

4. Reliability

After the software and hardware crash, the InnoDB data table driver will make use of log files to reconstruct and modify.

You can't have both reliability and high speed, and the innodb_flush_log_at_trx_commit option determines when to save the transaction to the log.

Open a transaction

START TRANSACTION or BEGIN

Commit transaction (close transaction)

COMMIT

Abandon transaction (close transaction)

ROLLBACK

Return point

SAVEPOINT adqoo_1ROLLBACK TO SAVEPOINT adqoo_1

Transactions that occur before the return point adqoo_1 are committed and later are ignored

Termination of transaction

Set the autosubmit mode

SET AUTOCOMMIT = 0

Each SQL is a different command of the same transaction, separated by COMMIT or ROLLBACK

After being dropped, all transactions without COMMIT are abandoned.

Transaction locking mode

System default: do not need to wait for the end of a transaction, you can directly query the results, but can no longer be modified or deleted.

Disadvantages: the results of the query may be out of date.

Advantages: do not need to wait for the end of a transaction, you can directly query the results.

The lock mode needs to be set with the following modes

1. SELECT. LOCK IN SHARE MODE (shared lock)

The data queried is the data of the database at this moment (the results of other commit transactions have been reflected here)

SELECT must wait for a transaction to finish before it can execute

2. SELECT. FOR UPDATE (exclusive lock)

For example, records of SELECT * FROM tablename WHERE id200 cannot be inserted.

5. Deadlock

Automatic identification of deadlock

The first incoming process is executed, and the subsequent process receives an error message and rolls back in ROLLBACK mode

Innodb_lock_wait_timeout = n to set the maximum waiting time. Default is 50 seconds.

Transaction isolation mode

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL

READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE

1. SET command without SESSION or GLOBAL

Valid only for the next transaction

2 、 SET SESSION

Sets the isolation mode for the current session

3 、 SET GLOBAL

Set isolation mode for all new MYSQL connections in the future (current connections are not included)

Isolation mode

READ UNCOMMITTED

Do not isolate SELECT

Changes that are not completed in other transactions (not COMMIT), whose results are also taken into account

READ COMMITTED

Take into account the COMMIT modifications of other transactions

The same SELECT may return different results in the same transaction

REPEATABLE READ (default)

Changes to other transactions are not taken into account, regardless of whether other transactions have been committed with the COMMIT command

In the same transaction, the same SELECT returns the same result (provided that the transaction is not modified)

SERIALIZABLE

Similar to REPEATABLE READ, shared locks are added to all SELECT

Error handling

According to the error message, execute the corresponding processing

Mysql transaction processing example

There are two main methods of transaction processing in MYSQL.

1. Using begin,rollback,commit to realize

Begin starts a transaction

Rollback transaction rollback

Commit transaction confirmation

two。 Directly use set to change the auto-commit mode of mysql

Mysql is automatically submitted by default, that is, if you submit a query, it will be executed directly! Can be passed through

Set autocommit = 0 forbids automatic submission

Set autocommit = 1 to enable auto-commit

To achieve transaction processing.

Note, however, that when you use set autocommit = 0, all your future sql will be processed as transactions until you confirm with commit or rollback, and note that when you end the transaction, you also open a new transaction! Follow the first method to only make the current as a transaction!

MYSQL supports transactions only for datasheets of type INNODB and BDB, and other types are not supported!

Mysql > use test; Database changed mysql > CREATE TABLE `dbtest` (- > id int (4)->) TYPE=INNODB;Query OK, 0 rows affected, 1 warning (0.05 sec) mysql > select * from dbtest->; Empty set (0.01 sec) mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > insert into dbtest values (5); Query OK, 1 row affected (0.00 sec) mysql > insert into dbtest value (6); Query OK, 1 row affected (0.00 sec) mysql > commit Query OK, 0 rows affected (0.00 sec) mysql > select * from dbtest;+-+ | id | +-+ | 5 | 6 | +-+ 2 rows in set (0.00 sec) mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > insert into dbtest values (7); Query OK, 1 row affected (0.00 sec) mysql > rollback;Query OK, 0 rows affected (0.00 sec) mysql > select * from dbtest +-+ | id | +-+ | 5 | | 6 | +-+ 2 rows in set (0.00 sec)

Mysql transaction processing

The handling of transactions implemented by php code can be achieved through the following methods of the PHP predefined class mysqli.

Autocommit (boolean): this method is used to limit whether query results are automatically submitted, if the parameter of the method is true, or off if the parameter is false. The MySQL database defaults to autocommit.

Rollback (): using this method in the mysqli class, transaction rollback can be implemented.

Commit (): all queries can be submitted using this method.

After reading the above, have you mastered how to handle transactions in mysql? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.