What can mysql database locks be divided into according to granularity?

Can be divided into according to the granularity: 1. Row-level lock, the finest lock granularity in MySQL, which means that only the row of the current operation is locked; 2. Table-level lock, the locking granularity in MySQL The largest kind of lock means locking the entire table of the current operation; 3. Page-level lock, a lock in MySQL with a locking granularity between row-level locks and table-level locks.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In relational databases, database locks can be divided into row-level locks (INNODB engine), table-level locks (MYISAM engine) and page-level locks (BDB engine) according to the lock granularity.

Locks used by MyISAM and InnoDB storage engines:

• MyISAM uses table-level locking.

• InnoDB supports row-level locking (row-level locking) and table-level locking. The default is row-level locking.

Comparison of row-level locks, table-level locks and page-level locks

• Row-level locks: locking granularity in MySQL The thinnest kind of lock, which means that only the row of the current operation is locked. Row-level locks can greatly reduce conflicts in database operations. Its locking granularity is the smallest, but the locking overhead is also the largest. Row-level locks are divided into shared locks and exclusive locks.

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

• Table-level lock: The lock with the largest locking granularity in MySQL, which means locking the entire table of the current operation. It is simple to implement, consumes less resources, and is used by most MySQL Engine support. The most commonly used MyISAM and InnoDB support table-level locking. Table-level locks are divided into table shared read locks (shared locks) and table exclusive write locks (exclusive locks).

  Features: low overhead, fast locking; no deadlock; large locking granularity, the highest probability of lock conflicts, and the lowest concurrency.

• Page-level lock: It is a lock in MySQL whose locking granularity is between row-level locks and table-level locks. Table-level locks are fast but have many conflicts. Row-level locks have few conflicts but are slow. So a compromised page level was adopted, locking a group of adjacent records at a time.

  Features: The overhead 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

Related free learning recommendations:mysql video tutorial

The above is the detailed content of What can mysql database locks be divided into according to granularity?. For more information, please follow other related articles on the PHP Chinese website!