Row-level locks, table-level locks, page-level locks in Mysql _mysql

In computer science, a lock is a synchronization mechanism used to forcibly restrict access to resources in the execution of 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

Row-level locks are the most granular lock in MySQL, indicating that only the rows of the current operation are locked. Row-level locks can significantly reduce database operation conflicts. The lock granularity is the smallest, but the lock is also the most expensive. Row-level locks are divided into shared and exclusive locks.

Characteristics

High overhead, slow lock, deadlock, minimum locking granularity, lowest probability of lock conflict and highest concurrency.

Second, table-level lock

Table-level locks are the largest locks in MySQL, indicating that the entire table for the current operation is locked, it is simple to implement, less resource consumption and is supported by most MySQL engines. Table-level locking is supported for most commonly used MyISAM and InnoDB. 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, locking granularity, the highest probability of lock conflict, the least degree of concurrency.

Third, page-level lock

A table-level lock is a lock in MySQL in which the lock granularity is between row-level and table-level locks. Table-level locks are fast, but there are many conflicts and there are fewer row-level conflicts, but they are slow. So take the eclectic page level and lock the next set of records one at a time. BDB supports page-level locks

Characteristics

Overhead and lock time bounded between table and row locks, deadlocks occur, locking granularity bounded between table locks and row locks, and concurrency is generally

The lock mechanism of MySQL common storage engine

• MyISAM and memory using table-level locks (table-level locking)
• BdB with page locks (page-level locking) or table-level locks, default to page locks
• InnoDB supports row-level locks (row-level locking) and table-level locks, which default to row-level locks

Iv. row and table locks in the InnoDB

As mentioned earlier, the InnoDB engine supports both row and table locks, so when will the entire table be locked, and when or only one row is locked?

The InnoDB row lock is implemented by locking the index entry on the index, which is different from Oracle, which is achieved by locking the corresponding data row in a block of data. InnoDB this type of line lock implementation characteristics means: Only through index conditions to retrieve data, InnoDB use row-level locks, otherwise, InnoDB will use table locks!

In practice, pay special attention to this characteristic of InnoDB row lock, otherwise, can cause a lot of lock conflict, thus affect concurrency performance.

Row-level locks are indexed, and table-level locks are used if an SQL statement does not use an index and is not used for row-level locks. The disadvantage of row-level locks is that because of the need to request a large number of lock resources, so slow, memory consumption is large.

Five, row-level lock and deadlock

MyISAM is not a deadlock, because MyISAM always get all the required locks at once, either all satisfied or all waiting. And in the InnoDB, the lock is obtained gradually, it creates the possibility of deadlock.

In MySQL, row-level locks are not direct lock records, but lock indexes. The index is divided into two types, primary key and non primary key index, if a SQL statement operates a primary key index, MySQL locks the primary key index, and if a statement operates a Non-key key index, MySQL locks the Non-key key index before locking the associated primary key index. In an update, delete operation, MySQL not only locks all index records that the where condition scans, but also locks adjacent key values, known as Next-key locking.

When two transactions were executed at the same time, one locked the gradual index waiting for other related indexes, one locked the Non-key index, and waited for the primary key index. This will cause deadlocks to occur.

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

There are a number of ways to avoid deadlocks, and here are only three common types, as follows

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

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

3, for very easy to create a deadlock business section, you can try to use the upgrade locking particle size, through the table-level locking to reduce the probability of deadlock;

The above is the entire content of this article, I hope to help you learn.