How to use MySQL table locks, row locks, exclusive locks and shared locks
1. Selection of transaction isolation mechanism
If we don’t care at all, use uncommitted read Transaction isolation mechanism, if these threads are allowed to operate the database concurrently, dirty reads (uncommitted data is read), non-repeatable reads (two query values are different), phantom reads (two query data volumes are different), etc. Problem, data has the lowest security, the advantage is that the concurrency efficiency is very high, generally it will not be used
If we serialize (rely on Lock implementation), all transactions are sorted through locks. Although data security is improved, the concurrency efficiency is too low, and it is generally not used
So we generally use Committed Read and Repeatable Read These two isolation levels balance the security, consistency and concurrency efficiency of data, and are controlled by MVCC multi-version concurrency Implemented (MVCC is the principle of committed read and repeatable read, and lock is the principle of serialization)
2. Table-level lock & row-level lock
Table-level lock: lock the entire table. The overhead is small (because you don’t need to find the record of a certain row in the table to lock it. If you want to modify this table, you directly apply for the lock of this table), the lock is fast, and there will be no deadlock; the lock granularity is large, and lock conflicts will occur. The probability is high and the concurrency is low
Row-level lock: Lock a row record. It is expensive (you need to find the corresponding record in the table, and there is a process of searching the table and index), locking is slow, and deadlock will occur; the locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is high
InnoDB storage engine supports transaction processing, the table supports row-level locking, and the concurrency capability is better
InnoDB row lock is through the index on the index It is implemented by locking items instead of locking row records in the table. This means that InnoDB will only use row-level locks to retrieve data through index conditions. Otherwise InnoDB will use table locks
Since InnoDB's row lock implementation is a lock added for index fields, not for row records, although different rows of the table under the InnoDB engine are accessed, if the same index field is used As a filter condition, lock conflicts will still occur, and can only be performed serially, not concurrently
Even if indexes are used in SQL, after passing through the MySQL optimizer,if It is considered that full table scanning is more efficient than using indexes. At this time, the use of indexes will be abandoned. Therefore, row locks will not be used
, but table locks will be used. For example, for some small tables , MySQL will not use the index
3. Exclusive lock (Exclusive) and shared lock (Shared)
Exclusive lock, Also known as X lock, write lock
Shared lock, also known as S lock, read lock
Read (SS) It is compatible, but reading and writing (SX, SX) and writing (XX) are mutually exclusive.
There is the following relationship between adding X and S locks to transactions:
A transaction adds S lock to data object A. It can perform read operations on A but cannot perform update operations. During the locking period, other transactions can add S locks to A but cannot add X locks
If a transaction adds an X lock to data object A, it can read and update A. During the locking period, other transactions cannot add any locks to A
Display locking: select … lock in share mode to force shared lock acquisition, select … for update to acquire exclusive lock
1. Test exclusive locks and shared locks between different transactions Compatibility
Let’s first check the SQL and content of the table
Check the isolation level:
the execution lock is added to the index tree
Use the non-indexed fields of the table as filter conditions
Transaction 2 now also wants to get the ranking of this record It locks, and it predictably fails; now transaction 2 obtains the exclusive lock of chenwei's record, try to see if it can succeed
InnoDB supports row locks , when using the primary key id as the filtering condition, transaction 1 and transaction 2 can successfully acquire locks for different rows. However, now we find that we cannot obtain the exclusive lock named chenwei. Why is this? Let’s explain:
InnoDB’s row lock is implemented by locking index entries, rather than locking table row records
And we Using name as a filter condition does not use an index, so naturally row locks will not be used, but table locks will be used. This means that InnoDB uses row-level locks only when data is retrieved through the index, otherwise InnoDB will use table locks!!!
We add an index to the name field
We found that after adding an index to name, two transactions can obtain exclusive locks (for update) on different rows, once again proving that InnoDB's row locks are The
added to the index item is because the name now goes through the index. Through zhangsan, we find the id of the row record where it is located on the auxiliary index tree, which is 7, and then Go to the primary key index tree and obtain the exclusive lock of the corresponding row record (My personal guess is that the corresponding records in the auxiliary index tree and primary key index tree are locked)
4. Serialization isolation level test
(All transactions use exclusive locks or shared locks, no user is required to manually lock)
Set the serialization isolation level
Two transactions can acquire shared locks at the same time (SS coexistence)
Now let transaction 2 insert data
At this time, insert needs to add an exclusive lock, but since transaction 1 has added a shared lock to the entire table, transaction 2 can no longer successfully lock the table (SX does not coexist)
rollback
Because we added an index to name, the above select is equivalent to adding a row shared lock to the data with name zhangsan
Transaction 2 update
Transaction 2 cannot update because the entire table has been locked by the shared lock of transaction 1 at this time
Transaction 2 looks for zhangsan on the auxiliary index tree , find the corresponding primary key value, and then go to the primary key index tree to find the corresponding record, but find that this row of records has been locked by a shared lock. Transaction 2 can obtain the shared lock, but cannot obtain the exclusive lock
Let’s try using the primary key index id to see if we can update
It’s still blocked, although the field behind where we now use id instead of name, but name also finds the corresponding primary key through the auxiliary index tree, and then finds the corresponding record on the primary key index tree, and the records on the primary key index tree are locked (My personal guess is that the auxiliary index tree and The data corresponding to the primary key index tree is locked)
We updated the data with id=8 successfully. Only row locks are added to the row data with id 7, so we can successfully operate the data with id 8
If there is an index, row locks are used; if there is no index, Then use table lock.
Table-level locks or row-level locks refer to the granularity of the lock. Shared locks and exclusive locks refer to the nature of the lock. Whether it is a table lock or a row lock, there is a distinction between shared locks and exclusive locks
The above is the detailed content of How to use MySQL table locks, row locks, exclusive locks and shared locks. For more information, please follow other related articles on the PHP Chinese website!

MySQL processes data replication through three modes: asynchronous, semi-synchronous and group replication. 1) Asynchronous replication performance is high but data may be lost. 2) Semi-synchronous replication improves data security but increases latency. 3) Group replication supports multi-master replication and failover, suitable for high availability requirements.

The EXPLAIN statement can be used to analyze and improve SQL query performance. 1. Execute the EXPLAIN statement to view the query plan. 2. Analyze the output results, pay attention to access type, index usage and JOIN order. 3. Create or adjust indexes based on the analysis results, optimize JOIN operations, and avoid full table scanning to improve query efficiency.

Using mysqldump for logical backup and MySQLEnterpriseBackup for hot backup are effective ways to back up MySQL databases. 1. Use mysqldump to back up the database: mysqldump-uroot-pmydatabase>mydatabase_backup.sql. 2. Use MySQLEnterpriseBackup for hot backup: mysqlbackup--user=root-password=password--backup-dir=/path/to/backupbackup. When recovering, use the corresponding life

The main reasons for slow MySQL query include missing or improper use of indexes, query complexity, excessive data volume and insufficient hardware resources. Optimization suggestions include: 1. Create appropriate indexes; 2. Optimize query statements; 3. Use table partitioning technology; 4. Appropriately upgrade hardware.

MySQL view is a virtual table based on SQL query results and does not store data. 1) Views simplify complex queries, 2) Enhance data security, and 3) Maintain data consistency. Views are stored queries in databases that can be used like tables, but data is generated dynamically.

MySQLdiffersfromotherSQLdialectsinsyntaxforLIMIT,auto-increment,stringcomparison,subqueries,andperformanceanalysis.1)MySQLusesLIMIT,whileSQLServerusesTOPandOracleusesROWNUM.2)MySQL'sAUTO_INCREMENTcontrastswithPostgreSQL'sSERIALandOracle'ssequenceandt

MySQL partitioning improves performance and simplifies maintenance. 1) Divide large tables into small pieces by specific criteria (such as date ranges), 2) physically divide data into independent files, 3) MySQL can focus on related partitions when querying, 4) Query optimizer can skip unrelated partitions, 5) Choosing the right partition strategy and maintaining it regularly is key.

How to grant and revoke permissions in MySQL? 1. Use the GRANT statement to grant permissions, such as GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'; 2. Use the REVOKE statement to revoke permissions, such as REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host' to ensure timely communication of permission changes.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

SublimeText3 Chinese version
Chinese version, very easy to use

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

SublimeText3 Linux new version
SublimeText3 Linux latest version

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
