Row-level locks, table-level locks, page-level locks in MySQL

　　

In computer science, a lock is a synchronization mechanism used to forcibly restrict access to resources when executing multiple threads, which is used to guarantee the satisfaction of mutex requirements in concurrency control.

In the DBMS, database locks can be divided into row-level locks (InnoDB engines), table-level locks (MyISAM engines), and page-level locks (BDB engines) according to the granularity of the locks.

First, row-level lock

A row-level lock is the most granular lock in MySQL that locks only on the row of the current operation. Row-level locks can greatly reduce the conflict of database operations. The lock granularity is minimal, but the cost of locking is the largest. Row-level locks are divided into shared and exclusive locks.

Characteristics

The cost is big, locking is slow; there is a deadlock, the lock granularity is minimal, the probability of lock conflict is the lowest, and the concurrency is the highest.

Second, table-level lock

Table-level lock is the largest lock in MySQL, which indicates that the whole table of the current operation is locked, it is simple, the resource consumption is low, and is supported by most MySQL engines. The most commonly used MyISAM and INNODB support table-level locking. Table-level locking is divided into table-shared read locks (shared locks) and table exclusive write locks (exclusive locks).

Characteristics

The cost is small, lock fast, no deadlock, lock granularity is high, the probability of lock conflict is highest, and the concurrency is the lowest.

Third, page-level lock

A table-level lock is a lock in MySQL that is locked in the middle of a row-level lock and a table-level lock. Table-level locks are fast, but conflict-less, row-level conflicts are low, but slower. So we took a compromised page level and locked a contiguous set of records at once. BDB support page-level locks

Characteristics

Overhead and lock times are bounded between table and row locks, deadlock occurs, lock granularity bounds between table and row locks, and concurrency is common

The lock mechanism of MySQL common storage engine

· MyISAM and memory with table-level lock (Table-level locking)

· BDB with page lock (page-level locking) or table-level lock, default to page lock

· InnoDB supports row-level locks (row-level locking) and table-level locks, which are row-level locks by default

Four, row lock and table lock in InnoDB

As mentioned earlier, both row and table locks are supported in the InnoDB engine, so when will the entire table be locked, or only one line locked?

InnoDB row locks are implemented by locking the index entries on the index, which is different from Oracle, which is achieved by locking the corresponding data rows in the data block. InnoDB This type of row lock implementation is characterized by the fact that InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise INNODB will use a table lock!

In practice, it is important to pay special attention to this feature of the InnoDB row lock, otherwise, it may lead to a lot of lock conflicts, which can affect the concurrency performance.

Row-level locks are index-based, and table-level locks are used if an SQL statement does not use a row-level lock for the index. The disadvantage of row-level locks is that they require a large number of lock resources to be requested, so they are slow and memory consuming.

Five, row-level lock and deadlock

There is no deadlock in MyISAM because MyISAM always gets all the locks needed, either all or all. In the InnoDB, the lock is gradually obtained, resulting in the possibility of deadlock.

In MySQL, a row-level lock is not a direct lock record, but a lock index. Indexes are primary key index and non-primary key index Two, if a SQL statement operation primary Key index, MySQL will lock the primary key index, if a statement operation non-primary key index, MySQL will first lock the non-primary key index, and then lock the relevant primary key index. In the update, delete operation, MySQL not only locks all index records scanned by the Where condition, but also locks adjacent key values, known as Next-key locking.

When two transactions are executed at the same time, a lock is gradually indexed while waiting for other related indexes, one locking the non-primary key index, and waiting for the primary key index. This will cause a deadlock to occur.

After a deadlock occurs, InnoDB can generally detect and cause one transaction to release the lock fallback, and the other to acquire the lock to complete the transaction.

There are a number of ways to avoid deadlocks, here are just a few common three kinds, as follows

1, if different programs will access multiple tables concurrently, as far as possible to agree to access the table in the same order, can greatly reduce the deadlock opportunity.

2, in the same transaction, as far as possible to lock all the resources needed to reduce the deadlock generation probability;

3, for very easy to generate deadlock in the business part, you can try to use the upgrade lock granularity, through table-level locking to reduce the probability of deadlock;

、

?

Row-level locks, table-level locks, page-level locks in MySQL