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

Source: reprint code as follows: copy code root@host# mysql-u root-p password

Enter password:*

Mysql > use TUTORIALS

Database changed

Mysql > 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_1

ROLLBACK 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!

Under MYSQL5.0 WINXP, the test passed ~ ^ _ ^

The code copies the code as follows

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.01sec)

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 > 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.

The code copies the code as follows

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.