MySQL transaction, concurrency problem, lock mechanism

What is a transaction (transaction)

Ensure that the batch operation is either fully executed or not executed at all, maintaining the integrity of the data. That means either success or failure.
Transactions can be n-SQL statements (n>=0)
Not all database engine support transactions, InnoDB engine supports transaction processing

Four major features of transactions

Atomicity (Atomic): All operations of a transaction are either fully executed or not executed at all. If an error occurs midway, it does not stop, but rolls back to the state before the transaction
Consistency (consistency): If a transaction is consistent before execution, it is consistent after execution, does not break the integrity of the relational data and the consistency of the business logic, and the transaction is implemented as expected.
Isolation (Isolation): Isolation prevents data inconsistencies when multiple transactions are concurrent
Persistence (Durability): Modifications to the database are permanent after the transaction executes successfully

Transaction concurrency without transaction isolation

Dirty read: Transaction a reads data that is not committed by transaction B, and if transaction B fails to roll back at this point, then transaction A is reading dirty data. For example, transaction a modifies money, and transaction B reads the updated result of transaction A, but if transaction A is rolled back, then transaction B reads dirty data.
Non-repeatable READ: In the same transaction, the result of reading the same data is inconsistent. Transaction a reads from transaction B before the data is updated, and transaction B updates the commit, and transaction a reads again, at which time the data is read differently two times.
Phantom read: In the same transaction, the same query returns multiple times in different results. Transaction B queries the number of records in the table, then transaction a inserts a record into the table, and transaction B then queries the discovery record number differently.

Difference

Dirty reads and non-repeatable reads: Dirty reads are updates that the transaction has read that has not yet committed the transaction. Non-repeatable reads are different from the data that is read several times in the same transaction.
The difference between non-repeatable reads and Phantom reads: Both are in the same transaction, the former is different from reading the data several times, the latter is the whole difference of reading the data several times.

Isolation level

Isolation level	Role
Serializable (serialization)	Avoid dirty reading, non-repeatable reading, Phantom reading
Repeatable (repeatable reading)	Avoid dirty reads and non-repeatable reads
Read Committed (Reading submitted)	Avoid dirty reading
READ UNCOMMITTED (not submitted)	None

MySQL supports the above 4 isolation levels, the default is repeatable read

Use of transactions

 from　　 user;　　   Rollback rollback to transaction start savepoint Dele;　　　　SavePoint retain rollback Position * * *rollback DELE; rollback point_name rollback to the set location

Use note points

If SQL is running correctly in a transaction and there is no commit after it, the result is not updated to the database, so you need to add the commit manually.
If there is an error in a part of the SQL statement in the transaction, the error statement does not follow. We may think that the correct operation will rollback the undo, but in fact it does not undo the correct operation, and if there is no error in the case of a commit, the previous correct operation will take effect, the database will be updated.

Locking mechanism of the INNODB engine

(The reason why InnoDB is mainly about locks is because InnoDB supports transactions, supports row and table locks, MyISAM does not support transactions, only table locks are supported)
Shared Lock (S): Allows a transaction to read one line, preventing other transactions from acquiring an exclusive lock on the same data set.
Exclusive Lock (X): A transaction that allows an exclusive lock to update data, preventing other transactions from acquiring shared read and exclusive write locks of the same data set.
Intent shared Lock (IS): The transaction intends to add a row of shared locks to the data row, and the transaction must obtain the IS lock of the table before sharing it with a data row.
Intent exclusive Lock (ix): The transaction intends to add an exclusive lock to the data row, and the transaction must obtain an IX lock on the table before adding an exclusive lock to the data row.
Description
1) shared and exclusive locks are row locks, intent locks are table locks, in the application we will only use the shared and exclusive locks, intent lock is MySQL internal use, do not require user intervention.
2) for update, Delete, and INSERT statements, InnoDB automatically adds an exclusive lock (X) to the data set involved, and for the normal SELECT statement, InnoDB does not add any locks, and the transaction can be displayed to the recordset with shared or exclusive locks.
Shared Lock (S): SELECT * FROM table_name WHERE ... LOCK in SHARE MODE.
Exclusive Lock (X): SELECT * FROM table_name WHERE ... For UPDATE.
3) InnoDB row locks are implemented by locking the index entries on the index, so innodb this type of row lock implementation means that only the data is retrieved by indexing criteria, InnoDB uses row-level locks, otherwise innodb will use a table lock!.

Source: www.cnblogs.com/-new/p/7239456.html

Source: www.cnblogs.com/fidelQuan/p/4549068.html

MySQL transaction, concurrency problem, lock mechanism