Why is the table locked in MYSQL?

The reasons why MySQL table is locked: 1. Transaction processing is a set of SQL statements that must be all executed or not executed at all. If a transaction contains multiple SQL statements that modify the same table , the table will be locked; 2. Index operation, when creating or deleting an index on a table, the table will be locked; 3. Concurrent operation, while inserting a large amount of data into a table, try to perform a query operation. The table will be locked and so on.

The operating system of this tutorial: Windows 10 system, mysql version 8.0, Dell G3 computer.

MySQL is an open source relational database management system and one of the most widely used databases. When using MySQL, we often encounter situations where tables are locked. There are many reasons why a table is locked. Here are some common reasons.

1. Transaction processing

A transaction refers to a set of SQL statements that are either fully executed or not executed at all. If a transaction contains multiple pairs of the same If a SQL statement is used to modify a table, the table will be locked. This is because MySQL uses a row locking mechanism. When two or more transactions modify a table at the same time, MySQL will place the modification request of one of the transactions in the waiting queue until the table is unlocked.

2. Index operation

Performing an index operation on a table will also cause the table to be locked. For example, when we create a new index or delete an index on a large table, the table will be locked at this time. The time it takes for an index operation to lock a table is related to the size and structure of the table.

3. Concurrent operations

In MySQL, concurrent operations will cause the table to be locked. For example, inserting a large amount of data into a table and trying to perform query operations at the same time will cause the table to be locked. This is because in MySQL, a query statement must first obtain the read lock of the table before performing a query operation, and the insert statement must first obtain the write lock of the table before performing a data insertion operation.

4. Locking operation

In MySQL, locking operation will also cause the table to be locked. For example, if you use the SELECT ... FOR UPDATE statement to perform a row-level locking operation, MySQL will add a write lock to the row, causing the entire table to be locked. At this point, other operations must wait for the lock to be released before proceeding.

In short, table being locked is one of the problems often encountered in MySQL. We need to understand various lock types and their causes, so as to avoid the table being locked as much as possible and improve the concurrency of the system.

MySQL supports multiple types of table locks, including shared locks (S locks) and exclusive locks (X locks). Shared locks allow multiple transactions to access the table concurrently, but they cannot modify the data. An exclusive lock prohibits other transactions from accessing the table until the lock is released.

Normally, MySQL locks the table when performing the following operations:

When querying data using the SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE statement.

When using INSERT, UPDATE or DELETE statements to modify data.

When using the ALTER TABLE statement to modify the table structure.

When using the TRUNCATE TABLE statement to clear table data.

In addition, MySQL also supports manual locking. You can use the GET_LOCK() and RELEASE_LOCK() functions to obtain and release table locks.

Common table lock strategies include:

Row lock: lock when modifying row data, and lock specific rows.

Page lock: Lock when modifying page data, locking a specific page.

Table lock: under repair

The above is the detailed content of Why is the table locked in MYSQL?. For more information, please follow other related articles on the PHP Chinese website!