Mysql lock mechanism is divided into table level lock and row level lock, this article and you share I mysql row level lock in the shared lock and exclusive lock exchange.
Shared lock is also called read lock, or S lock for short. As the name implies, shared lock means that multiple transactions can share a lock for the same data, and all of them can access the data, but they can only read and cannot modify the data.
Exclusive lock, also known as write locks, referred to as “X lock, as the name implies, exclusive lock is cannot coexist with other, such as a transaction to obtain a data row exclusive lock, and other transactions can’t obtain the bank’s other locks, including Shared and exclusive locks, lock but the transaction to get exclusive lock is to read and modify data.
A shared lock is a simple way to understand that multiple transactions can only read data and cannot modify data. A shared lock is a simple way to understand an exclusive lock. I made the mistake of thinking that once a row is locked, other transactions cannot read or modify the row. An exclusive lock means that after a transaction has an exclusive lock on a row of data, other transactions cannot have another lock on it. Mysql InnoDB engine default to update data,delete data,insert data automatically associated with the exclusive lock type, the default select statement does not add any lock type. For update statement, add shared lock, select… Lock in share mode statement. SQL > select * from ‘for UPDATE’ and ‘lock in share mode’; SQL > select * from ‘for update’; from… Query data because normal queries do not have any locking mechanism.
Having said that, let’s take a look at the following simple examples:
We have the following test data
Now let’s do an exclusive query on the row with id=1. Begin will be used to open the transaction and I won’t be seen closing the transaction. This is for testing because committing or rolling back the transaction will release the lock.
Open a query window
Will query a data, now open another query window, on the same data using exclusive query and shared lock query two ways of query
Exclusive to check
Check the Shared
We see that both the exclusive lock query and the shared lock query are blocked because the data with id=1 is already locked.
What if we just used the following query
We saw that it was possible to query data.
Let’s look again at a transaction that acquires a shared lock. Other queries can only be unlocked or shared.
We can see that it is possible to query data, but with exclusive locks cannot be found, because exclusive locks and shared locks cannot exist on the same data.
Finally, we verify the above said mysql InnoDb engine update, delete, insert statements automatically add exclusive lock problem,
At this time, the shared query is blocked, waiting for the release of the exclusive lock, but the data can be found with the ordinary query, because the locking mechanism is not mutually exclusive with the exclusive lock, but the data to be found is the old data before modifying the data.
Then we commit the data, release the exclusive lock look at the modified data, this time can be exclusive query, shared query and ordinary query, because the transaction commits the row data release the exclusive lock, below only show ordinary query, other students to verify.
You can see that the results are as expected.
The above is my personal understanding of mysql shared lock and exclusive lock, there are incorrect places also hope you correct.