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

This article mainly introduces the relevant knowledge of mysql transaction and storage engine case analysis, the content is detailed and easy to understand, the operation is simple and fast, and has a certain reference value. I believe you will gain something after reading this mysql transaction and storage engine case analysis article. Let's take a look at it.

1. MySQL transaction 1. The concept of transaction

(1) A transaction is a mechanism, an operation sequence, which contains a set of database operation commands, and all commands are submitted or revoked to the system as a whole, that is, this set of database commands are either executed or not executed.

(2) transaction is an indivisible working logic unit, and transaction is the smallest control unit when performing concurrent operations on the database system.

(3) transactions are suitable for database systems operated by multiple users at the same time, such as banks, insurance companies, securities trading systems and so on. (4) transactions ensure the consistency of data through the integrity of transactions.

2. ACID characteristics of transactions.

Note: ACID refers to the four characteristics that a transaction (transaction) should have in a reliable database management system (DBMS): Atomicity, Consistency, Isolation and Durability. These are several features that a reliable database should have.

(1) atomicity: a transaction is an irrevocable unit of work, in which either all or none of the operations in the transaction occur. A, the transaction is a complete operation, and the elements of the transaction are inseparable.

B. all elements in the transaction must be committed or rolled back as a whole.

C. if any element in the transaction fails, the entire transaction will fail.

(2) consistency: before the transaction starts and after the transaction ends, the integrity constraints of the database are not broken.

A. When the transaction is completed, the data must be in a consistent state.

B. the data stored in the database is in a consistent state before the transaction begins.

C. in an ongoing transaction, the data may be in an inconsistent state.

When the transaction completes successfully, the data must return to the known consistent state again.

(3) isolation: in a concurrent environment, when different transactions manipulate the same data at the same time, each transaction has its own complete data space. All concurrent transactions that modify data are isolated from each other, indicating that the transaction must be independent and should not depend on or affect other transactions in any way. A transaction that modifies data can access the data before another transaction that uses the same data starts, or after the end of another transaction that uses the same data.

(4) persistence: after the transaction is completed, the changes made by the transaction to the database are persisted in the database and will not be rolled back.

A. it means that the result of the transaction is permanent regardless of whether the system fails or not.

Once the transaction is committed, the effect of the transaction is permanently retained in the database.

Conclusion: in transaction management, atomicity is the foundation, isolation is the means, consistency is the end, and persistence is the result.

3. The interaction between things

(1) dirty reading: one transaction reads uncommitted data from another transaction, and this data is likely to be rolled back.

(2) unrepeatable: two identical queries in a transaction return different data. This is caused by the commit of other transaction modifications in the system when querying.

(3) Phantom reading: a transaction modifies the data in a table, which involves all the data rows in the table. At the same time, another transaction modifies the data in the table by inserting a row of new data into the table. In that case, users who operate on the previous transaction will find that there are unmodified rows of data in the table, as if they were hallucinating.

(4) missing update: two transactions read the same record at the same time, A modifies the record first, and B also modifies the record (B does not know that A has modified it). After B submits the data, the modification result of B overwrites the modification result of A.

II. Mysql and transaction isolation level

(1), read uncommitted: read the data that has not been submitted, and do not solve dirty reading

(2), read committed: read the submitted data to solve dirty reading

(3), repeatable read: reread read, can solve dirty read and non-repeatable read-mysql default

(4), serializable: serialization, which can solve dirty reads, unrepeatable reads and virtual reads-equivalent to locking tables Note: the default transaction level for mysql is repeatable read, while Oracle and SQL Server are read committed

1. Query the global transaction isolation level show global variables like'% isolation%'; or select @ @ global.tx_isolation

2. Query the session transaction isolation level show session variables like'% isolation%';SELECT @ @ session.tx_isolation; SELECT @ @ tx_isolation

3. Set the global transaction isolation level set global transaction isolation level read committed;show global variables like'% isolation%'

4. Set the session transaction isolation level set session transaction isolation level read committed;show session variables like'% isolation%'

Transaction control statements 1. Related statements

Begin; starts a transaction

Commit; commits the transaction, making all changes made to the database permanent

Rollback; rolls back the transaction, undoing all uncommitted changes in progress

Savepoint S1; establish a rollback point. S1 is the name of the rollback point. There can be multiple rollback points in a transaction.

Rollback to S1; rollback to S1 rollback point

2. Case

①, create tables

Create database school;use school;create table Fmoney (id int (10) primary key not null, name varchar (20), money decimal (5dag2)); insert into Fmoney values ('1pr 1); insert into Fmoney values (' 2c)); select * from Fmoney

②, test commit transaction

Begin;update Fmoney set money= money-100where name='srs2';commit;quitmysql-u root-puse school;select * from Fmoney

③, test rollback transactions

Begin;update Fmoney set money= money + 100where name='srs2';select * from Fmoney;rollback;select * from Fmoney

④, test multipoint rollback

Begin;update Fmoney set money= money + 100where name='srs2';select * from Fmoney;savepoint * update Fmoney set money= money + 100where name='srs1';select * from Fmoney;savepoint * insert into Fmoney values

3. Use set to set the control transaction SET AUTOCOMMIT=0; # disable autocommit SET AUTOCOMMIT=1; # enable autocommit. MySQL defaults to 1SHOW VARIABLES LIKE 'AUTOCOMMIT'; # to view the AUTOCOMIT value in Mysql

If autocommit is not enabled, all operations of the mysql connected to the current session will be treated as a transaction until you type rollback | commit; the current transaction ends. The operation results of any current session cannot be read when a new mysql connection is made before the end of the current transaction.

If autocommit is turned on, mysql treats each sql statement as a transaction and then automatically commit.

Of course, begin; commit | rollback; is a separate transaction whether it is enabled or not.

4. MySQL storage engine 1. Introduction to the concept of storage engine

(1) the data in MySQL is stored in files with different technologies, each of which uses different storage mechanisms, indexing techniques, locking levels, and finally provides different functions and capabilities. These different technologies and supporting functions are called storage engines in MySQL.

(2) Storage engine is the storage mode or format in which MySQL stores data in the file system.

(3) the storage engines commonly used in MySQL are: a, MylSAM b, InnoDB

(4) the component in the MySQL database that is responsible for performing the actual data Icano operation.

(5) in MySQL system, the storage engine is in the file system. The data is transferred to the storage engine before it is saved to the data file, and then stored according to the storage format of each storage engine.

2. View the storage engine show engines supported by the system

3. Storage engine used to view the table (1) method 1: directly view the show table status from library name where name=' table name'\ G; example: show table status from school where name='class'\ G; (2) method 2: enter the database to view the use library name; show create table table name\ G; example: use school;show create table class\ G

4. Modify storage engine (1) method 1: modify use library name through alter table; alter table table name engine=MyISAM; example: use school;alter table class engine=MYISAM; (2) method 2: specify the default storage engine and restart the service by modifying / etc/my.cnf configuration file Note: this method is only valid for newly created tables after modifying the configuration file and restarting the mysql service, and the existing tables will not be changed. Vim / etc/ my.cnf.[ mysqld] .default-storage-engine=INNODBsystemctl restart mysql.service (3) method 3: specify the storage engine use library name when creating the table through create table; create table table name (field 1 data type,...) Engine=MyISAM; example: mysql-u root-puse school;create table test7 (id int (10) not null,name varchar (20) not null) engine=MyISAM

This is the end of the article on "case Analysis of mysql transaction and Storage engine". Thank you for reading! I believe you all have a certain understanding of the knowledge of "mysql transaction and Storage engine instance Analysis". If you want to learn more, you are welcome to follow the industry information channel.

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.