MySQL is a popular relational database management system widely used in various web applications and enterprise applications. In MySQL, a lock is a mechanism for controlling concurrent access, which can ensure data consistency and integrity. This article will introduce the locking mechanism and principle of MySQL, including the type, level and implementation principle of the lock, and attach corresponding code examples.
1. Types of locks
In MySQL, locks can be divided into two types: shared locks and exclusive locks.
Shared Lock: It is a lock that allows multiple transactions to read the same resource at the same time. When a transaction acquires a shared lock, other transactions can also acquire the same shared lock, but cannot acquire an exclusive lock. Under shared locks, read operations are allowed, but write operations are not.
Exclusive lock (Exclusive Lock): It is a lock that allows only one transaction to read and write resources. When one transaction acquires an exclusive lock, no other transaction can acquire the same lock, nor can it acquire a shared lock. Under an exclusive lock, both read and write operations are allowed.
Two, the lock level
In MySQL, lock levels can be divided into row-level locks, table-level locks, and database-level locks.
Row-Level Lock: It locks a row of data in the database, and only the transaction that acquires the lock can access and modify the row of data. Row-level locks can improve concurrency, but consume more system resources.
Table-Level Lock: It locks the entire table, and only transactions that acquire the lock can access and modify the data in the table. Table-level locks can reduce lock conflicts, but may reduce concurrency.
Database-Level Lock: It locks the entire database, and only transactions that acquire the lock can access and modify data in the database. Database-level locks can be used for backup and restore operations on the entire database.
3. Implementation principle of lock
The locking mechanism of MySQL is mainly realized through two aspects: the granularity of the lock and the implementation method of the lock.
The granularity of the lock
MySQL locks can be divided into two types: table-level locks and row-level locks. Table-level locks lock the entire table, while row-level locks lock a row of data in the table.
In MySQL’s InnoDB storage engine, row-level locks are implemented by setting locks on indexes. When a transaction wants to modify a row of data, it needs to obtain an exclusive lock on the row of data first, so that other transactions cannot modify the row of data. And when a transaction only needs to read a row of data, it can obtain the shared lock of the row of data, so that other transactions can also read the row of data, but cannot modify the row of data.
How to implement the lock
MySQL’s lock implementation methods can be divided into two types: table-based locks and row-based locks.
Based on table lock: table lock is the simplest lock mechanism, which locks the entire table. In MySQL, the MyISAM storage engine uses table-level locks by default. For a table being accessed, only one transaction can acquire the lock of the table, and other transactions need to wait for the transaction to release the lock before accessing the table. Table-level locks can ensure data consistency and integrity, but will reduce the concurrent performance of the system.
Based on row locks: Row locks lock data at the row level, which can improve the concurrency performance of the system. In MySQL, the InnoDB storage engine uses row-level locks by default, which can be achieved by setting locks on indexes. When a transaction wants to modify a row of data, it needs to obtain an exclusive lock on the row of data first, so that other transactions cannot modify the row of data. And when a transaction only needs to read a row of data, it can obtain the shared lock of the row of data, so that other transactions can also read the row of data, but cannot modify the row of data.
4. Lock code example
The following is a simple sample code implemented using MySQL to demonstrate the use of row-level locks in MySQL to implement multiple transactions concurrently accessing the same row of data:
-- create a test table
CREATE TABLE test (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20)
);
-- Insert some test data
INSERT INTO test (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
-- start transaction 1
START TRANSACTION;
-- Obtain an exclusive lock on the row data with id 1
SELECT * FROM test WHERE id = 1 FOR UPDATE;
-- Sleep for 5 seconds, simulate transaction 1 to process the row of data
SELECT SLEEP(5);
-- Commit transaction 1
COMMIT;
-- start transaction 2
START TRANSACTION;
-- Acquire the exclusive lock of the row data with id 1. Since transaction 1 already holds the lock of the row data, transaction 2 needs to wait
SELECT * FROM test WHERE id = 1 FOR UPDATE;
-- Modify the row data
UPDATE test SET name = 'David' WHERE id = 1;
-- Commit transaction 2
COMMIT;
In the above code example, a test table called test is first created and some test data is inserted. Then, two transactions are opened, and transaction 1 acquires the exclusive lock of the row data with id 1, and sleeps for 5 seconds, simulating that transaction 1 is processing the row data. Next, transaction 2 also requests to acquire the exclusive lock of the row data with id 1, but since transaction 1 already holds the lock of the row data, transaction 2 needs to wait for transaction 1 to release the lock before acquiring the lock of the row data. Finally, transaction 2 modifies the row of data and submits the transaction.
It can be seen from the above examples that MySQL’s row-level lock mechanism can ensure data consistency and integrity when multiple transactions concurrently access the same row of data. At the same time, MySQL’s locking mechanism can also balance the relationship between concurrency and data consistency by adjusting the granularity and implementation of locks.
Summary
MySQL’s lock mechanism is an important part of the database management system, which can ensure data consistency and integrity when multiple transactions access the database concurrently. MySQL locks can be divided into two types: shared locks and exclusive locks, and three levels of row-level locks, table-level locks, and database-level locks. MySQL’s lock implementation can be based on table locks or row locks, where row locks are implemented by setting locks on indexes. Row locks can improve the concurrency performance of the system, but consume more system resources.
In practical applications, it is necessary to select an appropriate lock granularity and implementation method according to specific requirements and system performance. If the system requires high concurrency performance, you can use row-level locks to improve the system’s concurrency performance; if you need to ensure data consistency and integrity, you can use table-level locks to ensure data consistency and integrity.
Finally, it should be noted that when using MySQL’s locking mechanism, deadlocks should be avoided. A deadlock is a situation in which two or more transactions are waiting for each other to release the lock, which prevents the system from continuing to execute. In order to avoid deadlocks, you can adopt appropriate lock granularity and transaction isolation level, and use mechanisms such as lock timeout to avoid deadlocks.
Regarding transaction isolation levels, MySQL provides four different isolation levels: read uncommitted, read committed, repeatable read, and serialized. By default, MySQL uses the Repeatable Read isolation level. Different isolation levels correspond to different lock granularities and lock types, and an appropriate isolation level can be selected according to specific requirements and system performance.
The following is a simple sample code implemented using MySQL to demonstrate the use of transaction isolation levels in MySQL to control the granularity and implementation of locks:
-- Set the transaction isolation level to read committed SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- start transaction 1 START TRANSACTION; -- Acquire the shared lock of the row data with id 1 SELECT * FROM test WHERE id = 1 FOR SHARE; -- Sleep for 5 seconds, simulate transaction 1 to process the row of data SELECT SLEEP(5); -- Commit transaction 1 COMMIT; -- start transaction 2 START TRANSACTION; -- Acquire the exclusive lock of the row data with id 1. Since transaction 1 already holds the shared lock of the row data, transaction 2 needs to wait SELECT * FROM test WHERE id = 1 FOR UPDATE; -- Modify the row data UPDATE test SET name = 'David' WHERE id = 1; -- Commit transaction 2 COMMIT;
In the above example, the transaction isolation level is first set to read committed. Then, two transactions are started, and transaction 1 acquires the shared lock of the row data with id 1, and sleeps for 5 seconds, simulating that transaction 1 is processing the row data. Next, transaction 2 also requests to acquire the exclusive lock of the row data with id 1, but since transaction 1 already holds the shared lock of the row data, transaction 2 needs to wait for transaction 1 to release the lock before acquiring the lock of the row data. Finally, transaction 2 modifies the row of data and submits the transaction.
It can be seen from the above examples that the transaction isolation level of MySQL can control the granularity and implementation of locks, thereby realizing the control of concurrent access. In this example, transaction 1 uses a shared lock to access the row data with id 1, which makes transaction 2 only use an exclusive lock to modify the row data. This lock control method can ensure the consistency and integrity of data, and at the same time improve the concurrency performance of the system.