Shulou(Shulou.com)05/31 Report--

This article mainly introduces the difference between row-level lock, table-level lock and page-level lock in mysql database, which can be used for reference. Let's take a look at it with me.

According to the granularity, it can be divided into: 1, row-level lock, the finest lock in MySQL, which means locking only the rows of the current operation; 2, table-level lock, the lock with the largest granularity in MySQL, which means locking the entire table of the current operation; and 3, page-level lock, a lock whose granularity is between row-level lock and table-level lock in MySQL.

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 granularity of locks.

Locks used by the MyISAM and InnoDB storage engines:

MyISAM uses table-level locks (table-level locking).

InnoDB supports row-level locks (row-level locking) and table-level locks, which default to row-level locks.

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

Row-level lock: the finest-grained lock in MySQL, indicating that locks are applied only to the rows of the current operation. Row-level locks can greatly reduce conflicts in database operations. The 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 cost, slow locking, deadlock will occur; locking granularity is the smallest, the probability of lock conflict is the lowest, and the degree of concurrency is the highest.

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

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

Page-level lock: a lock whose locking granularity is between row-level lock and table-level lock in MySQL. The speed of table-level lock is fast, but the conflict is more, the row-level conflict is less, but the speed is slow. So take the eclectic page level and lock the adjacent set of records one at a time.

Features: overhead and locking time are between table lock and row lock; deadlock occurs; lock granularity is between table lock and row lock, and the concurrency is general.

These are the details of the difference between row-level locks, table-level locks and page-level locks in the mysql database, and do you have anything to gain after reading them? If you want to know more about it, welcome to the industry information!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.