Common MySQL lock types

Common lock types in MySQL require specific code examples

Introduction:
In the database, when multiple clients read the same data at the same time Or when modifying, problems with concurrent operations may occur. In order to ensure the consistency and integrity of data, the database engine uses a lock mechanism to control access to shared data. As a commonly used relational database, MySQL also provides a variety of lock types. This article will introduce common lock types in MySQL and provide specific code examples.

1. Shared Lock
Shared lock (also called read lock) is a lock used for concurrent reading. Multiple clients can acquire shared locks at the same time without blocking each other. Shared locks can ensure the consistency of data read by multiple clients at the same time.

Sample code:

-- Connection 1:
START TRANSACTION;
SELECT * FROM table1 WHERE id = 1 LOCK IN SHARE MODE;
-- Connection 2: (Executed before connection 1 releases the shared lock)
START TRANSACTION;
SELECT * FROM table1 WHERE id = 1 LOCK IN SHARE MODE;

In the above code, both connection 1 and connection 2 After acquiring the shared lock, the data with id=1 in the table1 table can be read at the same time.

2. Exclusive Lock
Exclusive lock (also called write lock) is a lock used for concurrent writing or modification. When multiple clients write or modify the same data at the same time, the exclusive lock can ensure that only one client can write the data, thus ensuring the integrity of the data.

Sample code:

-- Connection 1:
START TRANSACTION;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
-- Connection 2:(In Executed before connection 1 releases the exclusive lock)
START TRANSACTION;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;

In the above code, both connection 1 and connection 2 acquire exclusive locks , only connection 1 can write the data with id=1 in the table1 table.

3. Intention Lock
Intention lock is a lock used to coordinate the relationship between shared locks and exclusive locks. When a transaction acquires a shared lock or exclusive lock on a table, the intention lock can provide a warning mechanism so that other transactions can know the status of the lock. Intention locks are divided into two types, namely intention shared locks (IS) and intention exclusive locks (IX).

Sample code:

-- Connection 1:
START TRANSACTION;
LOCK TABLES table1 READ;
-- Connection 2:(Intent share not released in connection 1 Execute before lock)
START TRANSACTION;
LOCK TABLES table1 READ;

In the above code, connection 1 obtains the intention shared lock, and connection 2 can also obtain the intention shared lock and read the table1 table The data.

4. Row Lock
Row lock is a lock at the data row level. When multiple transactions operate on different rows of the same table at the same time, row locks can ensure that only one transaction can modify a certain row, thereby improving the efficiency of concurrent access.

Sample code:

-- Connection 1:
START TRANSACTION;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
-- Connection 2:(In Executed before connection 1 has released the row lock)
START TRANSACTION;
SELECT * FROM table1 WHERE id = 2 FOR UPDATE;

In the above code, connection 1 acquires the row lock and modifies table1 For the data with id=1 in the table, connection 2 waits for connection 1 to release the lock before it can obtain and modify the data with id=2.

Conclusion:
This article introduces the common lock types in MySQL, including shared locks, exclusive locks, intention locks and row locks, and provides specific code examples. Understanding and mastering how locks are used is crucial to ensuring data consistency and integrity. In actual development, it is necessary to select the appropriate lock type based on specific business needs and concurrent access conditions, and use the lock mechanism rationally to ensure the correctness and efficiency of database operations.

The above is the detailed content of Common MySQL lock types. For more information, please follow other related articles on the PHP Chinese website!