MySQL transaction processing and locking

MySQL Transaction processing and lock Transaction Processing (Transaction) are a mechanism to execute multiple update commands as a whole to ensure data integration. The Storage Engine (www.2cto.com) MySQL has an important feature: the function of the Storage Engine Architecture (Pluggable Storage Engine Architecture) MySQL is divided into two parts, the outer part: connects to the client and checks SQL statements in advance. Inner layer: the storage engine is responsible for receiving data operation instructions from the outer layer to complete actual data input/output and file operations. MySQL provides a variety of storage engines, allowing users to freely choose (or for different tables, choose different storage engines) users can choose any storage engine, which is unique to MySQL. MyISAM: the default high-speed engine, does not support transaction processing InnoDB: supports row locking and transaction processing, which is a little slower than MyISAM. ISAM: The predecessor of MyISAM engine. After MySQL 5.0, MERGE is no longer installed as a standard. This engine processes multiple MyISAM tables as one table. HEAP: only saves data in MEMORY. Falcon: A new storage engine that supports transaction processing. ARCHIVE: compress and save data (only INSERT/SELECT operations can be performed). CSV: save data in CSV format (used for cross-platform data exchange. in ini, set: default-storage-engine = INNODB to use the InnoDB engine to support transaction processing [SQL] SHOW CREATE TABLE customer; show create table customer \ G [SQL] ALTER TABLE customer ENGINE = MyISAM; why transaction processing? For example, a transfers 10 thousand yuan to B. Two operations are required. A's account is reduced by 10 thousand, and B's account is increased by 10 thousand. If, after account A is reduced by 10 thousand, Account B does not increase by 10 thousand, an error occurs. To avoid this situation, two operations are processed as one transaction. If both operations are successful, the transaction ends and is committed ). If one of the operations fails, the initial state is forcibly returned, that is, ROLLBACK. This ensures that either two operations are successful or both operations fail. To execute ROLLBACK and other transaction operations, make sure that the current database engine supports transaction operations. For example, InnoDB [SQL] BEGIN; -- or START TRANSACTION DELETE FROM customer; SELECT * FROM customer; ROLLBACK; the automatic submission function is enabled by default. [SQL] SELECT @ AUTOCOMMIT; SET AUTOCOMMIT = 0; -- disable the automatic submission function SELECT * FROM customer; DELETE FROM customer; ROLLBACK; -- disable automatic submission without the need for the BEGIN operation, you can also use ROLLBACK/*. If you do not perform the COMMIT operation, all updates will not be reflected in the database */partial ROLLBACK [SQL] BEGIN; insert into customer VALUES ('t0001 ', 'wang 2', '2017-10-21', 1); SAVEPOINT sp; insert into customer VALUES ('t0002', 'ada 3 ', '1970-10-21 ', 1); SELECT * FROM customer; rollback to savepoint sp; SELECT * FROM customer; ROLLBACK; SELECT * FROM customer; the following SQL command is automatically submitted after execution. They are not within the control of transaction processing. [SQL] DROP DATABASE; DROP TABLE; DROP; ALTER TABLE; multiple users can use Lock and Unlock to prevent conflicts when reading the DATABASE at the same time: shared Lock and eXclusive Lock are also known as read locks. When you reference data, the object data is read-only. Exclusive lock, also known as write lock or exclusive lock. Use this lock when executing INSERT, UPDATE, DELETE, and other operations. Other processes and transactions cannot be read, updated, or written. Lock granularity: records (rows), tables, and databases. Lock-up mechanism: When row-level unit-level locking occurs in large quantities, the database automatically increases the lock-level (for example, it increases to table-level locking or database-level locking: there are four levels of separation between transaction processing (mechanisms that affect each other at the same time): read uncommitted: supported, non-committed read, non-repeated READ, phantom read committed: supported, non-repeatable read, phantom Read repeatable read: supported, phantom Read SERIALIZABLE: Not support non-committed READ (Dirty Read Dirty READ): can be processed from other transactions, READ the UNCOMMITTED update data only at the read uncommitted split level. [SQL] SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is not recommended; Non-Repeatable Read is not allowed. In a TRANSACTION, it is caused by the update operations of other transactions, multiple reads of the same data have different results. Split horizontal Phantom READ (Phantom Read) below read commited: when a transaction reads the same data multiple times, due to the insert or delete operations processed by other transactions, in this case, data that does not exist during the first read or disappears during the first read. A deadlock Lock is a state in which two different transactions are processed, waiting for each other to release and locking, and it is never possible to unlock the Lock. The transaction processing mechanism, simply put, is to leave the Update log. Transaction processing-related logs are divided into two types: UNDO logs and REDO logs UNDO logs, also known as Rollback Segment REDO logs: transaction processing logs, or logs.