Shulou(Shulou.com)06/01 Report--

The following is about the main categories of locks in MYSQL. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on the main categories of locks in MYSQL.

As described in the database locking mechanism, in DBMS, 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.

Row level lock

Row-level locks are the finest-grained locks in Mysql, indicating that locks are only applied 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.

Characteristics

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

Table level lock

Table-level lock is the largest lock granularity in MySQL, which means locking the whole table of the current operation. It is easy to implement, low resource consumption, 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).

Characteristics

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

Page level lock

Page-level lock is a lock whose 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. BDB supports page-level locks

Characteristics

The overhead and locking time are between table lock and row lock; deadlocks occur; lock granularity is between table lock and row lock, and the concurrency is average.

Locking Mechanism of commonly used Storage engine in MySQL

MyISAM and MEMORY use table-level locks (table-level locking)

BDB uses page lock (page-level locking) or table level lock. The default is page lock.

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

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 and when or only one row will be locked?

InnoDB row locking is achieved by locking the index items on the index, unlike Oracle, which is achieved by locking the corresponding data rows in the data block. The row lock implementation feature of InnoDB means that InnoDB uses row-level locks only if the data is retrieved by index conditions, otherwise InnoDB will use table locks!

In practical applications, special attention should be paid to this feature of InnoDB row locks, otherwise it may lead to a large number of lock conflicts, thus affecting concurrency performance.

When querying without index conditions, InnoDB does use table locks, not row locks.

Because the row lock of MySQL is a lock for index, not a lock for records, although it accesses records of different rows, lock conflicts will occur if the same index key is used. You should pay attention to this when applying design.

When a table has multiple indexes, different transactions can use different indexes to lock different rows, and InnoDB uses row locks to lock the data, whether using primary key indexes, unique indexes, or normal indexes.

Even if index fields are used in the condition, whether or not to use indexes to retrieve data is determined by MySQL by determining the cost of different execution plans. If MySQL thinks that full table scanning is more efficient, for example, for small tables, it will not use indexes, in which case InnoDB will use table locks instead of row locks. Therefore, when analyzing lock conflicts, don't forget to check the execution plan of SQL to confirm that indexes are actually used.

Row-level lock and deadlock

Deadlocks do not occur in MyISAM, because MyISAM always gets all the locks needed at once, either satisfying them all or waiting for them all. In InnoDB, the lock is acquired step by step, resulting in the possibility of deadlock.

In MySQL, row-level locks are not direct lock records, but lock indexes. Indexes are divided into primary key index and non-primary key index. If a sql statement operates on a primary key index, MySQL will lock the primary key index; if a statement operates on a non-primary key index, MySQL will lock the non-primary key index first, and then lock the relevant primary key index. During UPDATE and DELETE operations, MySQL locks not only all index records scanned by WHERE conditions, but also adjacent key values, called next-key locking.

When two transactions execute at the same time, one locks the primary key index and waits for other related indexes. The other locks the non-primary key index and is waiting for the primary key index. This will lead to a deadlock.

After a deadlock occurs, InnoDB can generally detect and cause one transaction to release the lock and roll back, while the other acquires the lock to complete the transaction.

There are several ways to avoid deadlocks. Here are only three common ones.

1. If different programs access multiple tables concurrently, try to agree to access the tables in the same order, which can greatly reduce the chance of deadlock.

2. In the same transaction, try to lock all the resources needed at once to reduce the probability of deadlock.

3. For business parts that are very prone to deadlocks, you can try to use upgrade locking granularity to reduce the probability of deadlocks through table-level locking.

Row-level lock is the most fine-grained lock in Mysql. Row-level lock can greatly reduce the conflict of database operations. Row-level locks are divided into shared locks and exclusive locks. This article will introduce the concept, usage and matters needing attention of shared locks and exclusive locks in detail.

Shared lock (Share Lock)

A shared lock, also known as a read lock, is a lock created by a read operation. Other users can read data concurrently, but no transaction can modify the data (acquire exclusive locks on the data) until all shared locks have been released.

If transaction T adds a shared lock to data A, other transactions can only add a shared lock to A, not an exclusive lock. Transactions that are allowed to share locks can only read data and cannot modify it.

Usage

SELECT... LOCK IN SHARE MODE

Adding LOCK IN SHARE MODE,Mysql after the query statement adds a shared lock to every row in the query result. When no other thread uses an exclusive lock on any row in the query result set, you can successfully apply for a shared lock, otherwise it will be blocked. Other threads can also read tables that use shared locks, and these threads read the same version of data.

Exclusive lock (eXclusive Lock)

An exclusive lock is also called a write lock. If transaction T adds an exclusive lock to data A, other transactions can no longer add any kind of lock to A. Transactions that are granted an exclusive lock can both read and modify data.

Usage

SELECT... FOR UPDATE

Adding FOR UPDATE,Mysql after the query statement adds an exclusive lock to every row in the query result. When no other thread uses an exclusive lock on any row in the query result set, you can successfully apply for an exclusive lock, otherwise it will be blocked.

Intention lock

Intention locks are table-level locks that are designed to reveal the type of lock that the next row will be requested in a transaction. Two table locks in InnoDB:

Intention shared lock (IS): indicates that the transaction is ready to add a shared lock to a data row, that is, a data row must acquire the IS lock of the table before adding a shared lock

Intention exclusive lock (IX): similar to the above, it indicates that the transaction is ready to add an exclusive lock to the data row, indicating that the transaction must acquire the IX lock of the table before adding the exclusive lock to the data row.

The intention lock is automatically added by InnoDB and does not require user intervention.

For insert, update, and delete,InnoDB, exclusive locks are automatically added to the data involved; for general Select statements, InnoDB does not add any locks, and transactions can add shared or exclusive locks to the display through the following statements.

Shared lock: SELECT... LOCK IN SHARE MODE

Exclusive lock: SELECT. FOR UPDATE

What are the main categories of locks in the above MYSQL? is there anything else you don't understand? Or if you want to know more about it, you can continue to follow our industry information section.

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.