How to use table locks and row locks for concurrency control in MySQL?

How to use table locks and row locks for concurrency control in MySQL?

In the database, it is often encountered that multiple users operate on the same data at the same time. In this case, concurrency control is needed to ensure data consistency. MySQL provides two mechanisms: table locks and row locks to achieve concurrency control. This article will focus on how to use table locks and row locks for concurrency control in MySQL, and give corresponding code examples.

1. Table lock

Table lock is the most basic locking mechanism in MySQL. It locks the entire table. When a user updates a table, a write lock is automatically added to the table, and other users' read and write operations on the table need to wait for the lock to be released. The following is a sample code for using table locks:

-- 锁定表
LOCK TABLES table_name WRITE;

-- 进行写操作
UPDATE table_name SET column_name = value WHERE condition;

-- 解锁表
UNLOCK TABLES;

In the above code, when a user executes the LOCK TABLES table_name WRITE command, writes will be added to the table_name table Lock. If other users need to access the table_name table when performing read or write operations, they need to wait for the lock to be released. After completing the operation, you can use the UNLOCK TABLES command to release the lock.

It should be noted that when using table locks, all tables that need to be operated need to be locked, which will cause concurrency performance to decrease.

2. Row lock

Compared with table lock, row lock gives more fine-grained control of concurrency control. For locked rows, other users can continue to read and write other rows. The following is an example code for using row locks:

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 开启事务
START TRANSACTION;

-- 对行加锁
SELECT column_name FROM table_name WHERE condition FOR UPDATE;

-- 进行写操作
UPDATE table_name SET column_name = value WHERE condition;

-- 提交事务
COMMIT;

In the above code, we first set the transaction isolation level to SERIALIZABLE, which is the highest level of transaction isolation level and can ensure the highest degree of data consistency. Then use the SELECT...FOR UPDATE statement to lock the rows that need to be operated. Other users need to wait for the lock to be released when they need to access the locked row.

It should be noted that using row locks may cause deadlock. Therefore, the granularity and order of locks need to be carefully considered when using row locks to avoid deadlocks.

To sum up, MySQL provides two concurrency control mechanisms: table locks and row locks. Table locks are suitable for operations on the entire table, while row locks are suitable for operations on partial rows. Choosing an appropriate lock mechanism based on actual needs can improve the concurrency performance and data consistency of the database.

Reference materials:

1. MySQL official documentation: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

The above is the detailed content of How to use table locks and row locks for concurrency control in MySQL?. For more information, please follow other related articles on the PHP Chinese website!