What is mysql lock granularity?

Mysql lock granularity is what we usually call the lock level. The database engine has multi-granularity locking, allowing one transaction to lock different types of resources. MySQL database has three lock levels: page-level locks, table-level locks and row-level locks.

Lock granularity

(Recommended tutorial: mysql tutorial)

Lock granularity is what we usually call lock level.

The database engine has multi-granularity locking, allowing one transaction to lock different types of resources. To minimize locking overhead, the database engine automatically locks resources at a level appropriate for the task.

Locking at a smaller granularity (such as rows) can improve concurrency, but the overhead is higher because if many rows are locked, more locks need to be held. Locking at a larger granularity (such as a table) reduces concurrency because locking the entire table restricts other transactions from accessing any part of the table. But its overhead is lower because fewer locks need to be maintained.

Lock Hierarchy

Database engines often must acquire locks at multiple levels of granularity to fully protect resources. This set of locks at multiple levels of granularity is called a lock hierarchy. For example, to fully protect reads from an index, a Database Engine instance might have to acquire shared locks on rows and intent shared locks on pages and tables.

MySQL has three lock levels: page level, table level, row level

MyISAM and MEMORY storage engines use table-level locking (table-level locking) ;

The BDB storage engine uses page-level locking, but also supports table-level locking;

The InnoDB storage engine supports both row-level locking (row-level locking), Table-level locking is also supported, but row-level locking is used by default.

The characteristics of MySQL locks can be roughly summarized as follows:

Table-level locks: low overhead, fast locking; no deadlocks Lock; the locking granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest.

Row-level locks: high overhead, slow locking; deadlocks may occur; the locking granularity is the smallest, the probability of lock conflicts is the lowest, and the concurrency is the highest.

Page lock: The cost and locking time are between table locks and row locks; deadlocks will occur; the locking granularity is between table locks and row locks, and the concurrency is average.

The above is the detailed content of What is mysql lock granularity?. For more information, please follow other related articles on the PHP Chinese website!