Mysql transactions and lock mechanisms

The characteristic ACID of a transaction, that is, atomicity, consistency, isolation, and durability. Atomicity ensures that a transaction is a minimum unit and cannot be split internally. Consistency ensures that each operation thread in the transaction cannot be submitted separately. If the operation is successful, the transaction is committed together. If the operation fails, the transaction is rolled back; isolation ensures that the data views seen by different transactions are independent and isolated from each other (isolation level can be set). Persistence ensures that the data will be permanently saved after the transaction is committed; the isolation level of transactions defined in the SQL specification: 1. read uncommitted (read uncommitted content) all transactions can see the execution results of UNCOMMITTED transactions. This isolation level is rarely used in actual applications to read uncommitted data, it is also called "Dirty read ". 2. read committed (read committed content) the default isolation level of most databases is this level, but not the default level of mysql. At the beginning of a transaction, you can only see the changes made by the committed transaction. Any changes made to a transaction from the beginning to before the commit are invisible unless committed. This isolation level is also known as non-repeated read. 3. repeatable read (repeatable read) This isolation level is designed to solve the problem caused by the repeatable read isolation level. That is, when multiple instances of a transaction READ data concurrently, different results are displayed. This isolation level does not see the results after other transactions are committed, that is, even if the transaction is committed, I cannot see it. This level is also known as "Phantom read ". 4. SERIALIZABLE (SERIALIZABLE) SERIALIZABLE is the highest isolation level. It forces transaction sorting to make it unrepeatable, solving the phantom read problem. This isolation level adds a shared lock to each read data row. Using this isolation level produces a lot of timeout, which is not used in actual development. Mysql locking mechanism: it can be divided into two types: shared lock and exclusive lock, read lock and write lock ). Table sharding locks and row locks are divided according to the granularity. Table locks are implemented by the database server, and row locks are implemented by the storage engine. Mysql provides three transaction-type storage engines: InnDB, NDB Cluster, and Falcon. Locks can be obtained in any process of a transaction execution, but these locks are released only when the transaction is committed or rolled back. These are both implicit locks or explicit locks. InnoDB supports explicit locks, such as SELECT .... lock in share mode (with a shared LOCK) SELECT ..... for update (exclusive lock) Multi-version concurrency control (important): Mysql's transaction storage engine is not a simple and practical row locking mechanism, but also called multi-version concurrency control (MVCC) technology, it is useful to associate with the row locking mechanism. In order to cope with higher concurrency, of course, the cost of performance consumption. Each storage engine has different implementation methods for MVCC. The simple implementation of the InnoDB engine is as follows: InnoDB is implemented by adding two implicit values for each data flight. The two implicit values record the row creation time and expiration time. The system version number when each row stores events. Each time a new transaction starts, the version number is automatically added with 1, and each transaction stores the version number at the start. Each query returns the query result based on the transaction version number. Author lhc1986