MySQL-----Lock (Row lock, table lock, page lock, optimistic lock, pessimistic lock)

Review:

ACID: In the process of writing or updating data, the DBMS has four features for ensuring that transactions are correct and reliable: atomicity (indivisibility), consistency, isolation (independence), persistence

a transaction : A complete logical process consisting of a series of database operations

atomicity : All operations in a transaction, either complete or not complete, rollback (RollBack) If an error occurs

consistency : The integrity of the database does not break before the transaction starts and after the transaction ends

Isolation : The ability of a database to read and write and modify its data at the same time for multiple concurrent transactions, which prevents inconsistencies in data resulting from cross-execution when multiple transactions are executing concurrently. Transaction isolation is divided into distinct levels : READ UNCOMMITTED, read commit, repeatable read, serialization

Persistence : After the transaction is finished, changes to the data are permanent and the system fails to change

lock : a mechanism that coordinates the concurrent access of multiple processes or threads to a resource. Lock conflict is an important factor that affects the performance of concurrent access to a database.

The storage engine supports different locking mechanisms

	Row lock	Table lock	Page lock
MyISAM		Support
BDB		Support	Support
InnoDB	Support	Support

Overhead, lock speed, deadlock, granularity, concurrency performance

• row lock : overhead, locking slow, deadlock, lock granularity is small, lock collision probability is low, concurrency is high
• table lock : Low overhead, lock fast, no deadlock, locking granularity, high lock collision probability, the lowest degree of concurrency
• page lock : The cost and lock speed between the row and table locks, a deadlock occurs, the lock granularity between the table and row locks, the degree of concurrency is generally

# # #根据具体应用决定适合那种锁, only from the angle of the lock: table locks are suitable for query-based, table locks are suitable for concurrent updating of small amounts of different data by a large number of index conditions, and the application of concurrent queries

MyISAM table lock

MyISAM only supports table locks, and as applications improve transactional integrity and concurrency, MySQL begins to develop a transaction-based storage engine that comes out of InnoDB row locks and bdb page locks, InnoDB by Oracle Acquisition

This is recorded first, and subsequent updates

MySQL-----Lock (Row lock, table lock, page lock, optimistic lock, pessimistic lock)