This article brings you relevant knowledge about shared locks and exclusive locks in mysql. I hope it will be helpful to everyone.
Shared Lock
Shared lock, S lock, read lock , are all his names.
And I like to call him Shared Read Lock.
A shared (S) lock permits the transaction that holds the lock to read.
#A shared lock allows the transaction holding the lock to read.
The sharing here is, Read and read sharing .
That is to say, whether it is row level or table level, if a shared read lock is placed on certain data, Other transactions can continue to read (that is, shared read locks are allowed to be held) , but cannot be written, that is, reading and writing are mutually exclusive.
By the way, let me introduce how to add a shared lock (shared read lock):
Upper table-level shared lock, that is, table-level shared read lock:
select * from table（表） lock in share mode ；
Copy after login
Upstream-level shared lock , that is, row-level shared read lock:
select * from table（表）where id = 10
lock in share mode
；
Copy after login
Let’s be a bit more verbose here. Note that under InnoDB, you don’t just use row locks if you want to use row locks. Let’s review the triggering conditions of row locks again ( Mentioned at the beginning):
Exclusive Lock
Exclusive lock, write lock, X lock , are all his names.
And I like to call him Exclusive write lock.
An exclusive (X) lock permits the transaction that holds the lock to update or delete.
Exclusive (X) locks allow the transaction holding the lock to update or delete.
Exclusive, this word. Have you ever played basketball? I didn’t know how to play basketball in junior high school. I would not pass the ball while I was holding it. My classmates said to me, you are so lonely.
Yes, I am very alone. Just like this exclusive write lock (exclusive lock), it is very unique.
When a transaction adds an exclusive write lock (exclusive lock) to certain data, only the current transaction can modify or delete the data.
Other transactions cannot be read or written. Because this lock is very unique, you must wait until this very unique lock is used up (released) before other transactions can take advantage of it.
So, the exclusive write lock (exclusive lock) is mutually exclusive for reading and writing, and mutually exclusive for writing and writing.
By the way, let me introduce how to add an exclusive lock (exclusive write lock):
Upper table-level exclusive lock, that is, table-level exclusive write lock:
select * from table
for update
；
Copy after login
Upstream-level exclusive lock , that is, row-level exclusive write lock:
select * from table where id =10
for update
；
Copy after login
Let me be a little more verbose here. Note that under InnoDB, you don’t just use row locks if you want to use row locks. We will again trigger the row lock conditions. To review (mentioned at the beginning):
The above sql can achieve an upstream-level exclusive lock because it hits the index, and id is the index.
Perhaps after seeing this, you are still vague about shared locks & exclusive locks. You roughly know what read-read sharing, read-write mutual exclusion, write-write mutual exclusion and so on.
So, we need to look at these two locks from God’s perspective again,
RedTransaction Operation 1
BlueTransaction Operation Two
##Shared lock (shared read lock)
Exclusive lock (exclusive write lock)
Shared lock (shared read lock)
Yes, compatible, read together
No, not compatible, you have to wait if you want to write The shared lock is gone
Exclusive lock (exclusive write lock)
No, it’s incompatible. If the exclusive lock is set, others can’t do anything. Move
Can’t move, it’s incompatible, it’s locked exclusively, no one else can move it
3. So if the exclusive write lock is never released, will other transactions keep waiting?
The same is true, it will wait for the timeout to return the query failure:
Add a little practice:
1. Still the same, first upload a certain data Exclusive write lock, no COMMIT:
2. Execute ordinary query, select:
You can see, Ordinary select statements can be obtained normally, why? Because we mentioned it earlier:
So I have to elaborate again. The so-called read-read sharing, read-write mutual exclusion, and write-write mutual exclusion are all for lock resources. That said, if you don't compete for lock resources, then there must be no mutual exclusion or mutual exclusion.
The above is the detailed content of Examples to explain shared locks and exclusive locks under MySQL and InnoDB. For more information, please follow other related articles on the PHP Chinese website!
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
MySQL is suitable for beginners because it is simple to install, powerful and easy to manage data. 1. Simple installation and configuration, suitable for a variety of operating systems. 2. Support basic operations such as creating databases and tables, inserting, querying, updating and deleting data. 3. Provide advanced functions such as JOIN operations and subqueries. 4. Performance can be improved through indexing, query optimization and table partitioning. 5. Support backup, recovery and security measures to ensure data security and consistency.
Navicat itself does not store the database password, and can only retrieve the encrypted password. Solution: 1. Check the password manager; 2. Check Navicat's "Remember Password" function; 3. Reset the database password; 4. Contact the database administrator.
Create a database using Navicat Premium: Connect to the database server and enter the connection parameters. Right-click on the server and select Create Database. Enter the name of the new database and the specified character set and collation. Connect to the new database and create the table in the Object Browser. Right-click on the table and select Insert Data to insert the data.
Copying a table in MySQL requires creating new tables, inserting data, setting foreign keys, copying indexes, triggers, stored procedures, and functions. The specific steps include: creating a new table with the same structure. Insert data from the original table into a new table. Set the same foreign key constraint (if the original table has one). Create the same index. Create the same trigger (if the original table has one). Create the same stored procedure or function (if the original table is used).
Navicat for MariaDB cannot view the database password directly because the password is stored in encrypted form. To ensure the database security, there are three ways to reset your password: reset your password through Navicat and set a complex password. View the configuration file (not recommended, high risk). Use system command line tools (not recommended, you need to be proficient in command line tools).
MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.
Common reasons why Navicat cannot connect to the database and its solutions: 1. Check the server's running status; 2. Check the connection information; 3. Adjust the firewall settings; 4. Configure remote access; 5. Troubleshoot network problems; 6. Check permissions; 7. Ensure version compatibility; 8. Troubleshoot other possibilities.
Steps to perform SQL in Navicat: Connect to the database. Create a SQL Editor window. Write SQL queries or scripts. Click the Run button to execute a query or script. View the results (if the query is executed).
Database
