Transaction mechanism and lock mechanism

the need for business

To avoid data inconsistency problems, you need to introduce the concept of the transaction into the stored procedure, tying the update statements together to make them an "atomic" Operation: The update statements are either executed or not executed. turn off automatic submission of MySQL display turn off autocommit

Set autocommit=0;

Implicitly turn off autocommit

Start transaction

It is generally recommended that you use an implicit commit method, because you will not modify to the AUTOCOMMIT system variable. after automatic commit is turned off, the method for submitting the UPDATE statement has

Corresponding, also divides into the display and the implicit type submits the way:
1. Submitted by display:

Commit

2. Implicit submission includes begin, set Autocommit=1, start transaction, rename table, truncate
table and other statements; Data definition (Create, alter, drop) function, ALTER procedure, DROP database, drop
Table, drop function, DROP index, drop procedure, rights management and account management statements (grant, REVOKE, set password, create user, drop user, rename
User) lock statement (lock tables, unlock tables)
It is more recommended to use the display submission method. The transaction usage method uses rollback in the process of handling error code. Use the start transaction to implicitly turn off autocommit at the place where there is an atomic operation, and commit at the end by using the commit display. How to use transaction save points

Create a savepoint in a transaction using the SavePoint save name format to implement a "partial" or "partial" revocation of the transaction (rollback to savepoint Save the name).
A savepoint is a "temporary state" that can either be rolled back to the state before the transaction started, or the next state of the transaction, which is a temporary state in the state raised by the two autocommit statement. the necessity of the lock mechanism

The data in memory is not synchronized with the data in the external storage, and there is a "synchronization delay" between the table records. syntax format for table-level locks imposed by MyISAM tables

Lock tables Table 1 [as Alias] read  [local]
[, table 2 [as Alias 2][low_priority] write] ...

The difference between the read local and read options is read local allows the execution of a non-conflicting INSERT statement (inserted simultaneously) when the lock is persisted. The granularity of the lock, the implicit lock and display lock, the type of lock, the lock's key, the lock's lifecycle Lock's granularity refers to the scope of the lock multiple MySQL clients concurrent access to the same data, to ensure data consistency, The database management system automatically lock and unlocks the data, which is an implicit lock. Occasionally, implicit locks cannot achieve consistent access requirements for data (for example, contention for critical resources) and need to be manually locked and unlocked, which is called a display lock. The types of locks are divided into read locks (shared locks) and write locks (exclusive or exclusive) lock keys: When multiple MySQL clients access the same data concurrently, if MySQL client a succeeds in locking the data, only MySQL client A has the key to the lock. Life cycle of Locks: the time interval between locking data to unlock within the same MySQL session. The longer the life cycle of the lock, the lower the concurrent access performance, the shorter the life cycle of the lock, and the higher the concurrent access performance. InnoDB The syntax format for row-level locks imposed by the table shared lock select * FROM table where conditional statement lock in share mode; Exclusive LOCK SELECT * FROM table where conditional statement for update; Clearance lock, Record lock

in InnoDB

When the retrieval condition satisfies an interval range, but the record does not exist in the table, there are also shared or exclusive locks, and the lock locks the adjacent keys, which is the clearance lock (Next-key lock).
The transaction isolation level is set to repeatable read, at which point a row-level lock is applied to the InnoDB table, an interval lock is used by default (requires an index), and when the transaction's isolation level is set to read uncommited or read commited, the InnoDB table imposes row-level locks. The record lock is used by default.
Unlike a gap lock, a record lock imposes a shared or exclusive lock only on records that satisfy the scope of the query. lock wait and deadlock waiting is to ensure that the transaction can run normally concurrently, lock wait does not necessarily lead to deadlock problems occur. And the deadlock problem must be accompanied by the lock waiting phenomenon. The transaction isolation level supported by MySQL

4 kinds: Read uncommited–> reading "Dirty" data phenomenon read commited–> unreadable phenomenon repeatable read (mysql default) –> the phenomenon of phantom reading serializable