Locks in MySQL can be classified from multiple dimensions, which can be divided intoShared Lock (S Lock, S Lock) and Exclusive Lock (X Lock).
Shared Lock (S Lock)
Shared locks, also known as Read locks, ensure the shared read characteristics of data.
- Allow multiple transactions to read the same data resource at the same time, that is, multiple transactions can hold the same shared lock at the same time and read the data concurrently.
- Blocking exclusive locks, that is, if one transaction already holds a shared lock, other transactions need to wait for the shared lock to be released if they want to acquire the exclusive lock. In fact, no transaction is allowed to modify the data (because you need to add an exclusive lock to modify the data first)
How to lock a shared lock
SELECT ... LOCK IN SHARE MODE SELECT ... FOR SHARE（MySQL 8.0 Versions above）
Exclusive Lock (X Lock)
- Only one transaction holds an exclusive lock, and other transactions cannot acquire the exclusive lock on the same record. That is, only one transaction is allowed to modify or delete a certain line of data
- Blocking exclusive locks and shared locks, that is, if a transaction already holds an exclusive lock and other transactions cannot acquire a shared lock or an exclusive lock, it is actually not allowed to lock and read or modify the data on the line (note that it is just that the shared lock or exclusive lock cannot be read, but normal query is snapshot reading, which is OK)
Exclusive lock lock method
SELECT ... FOR UPDATE UPDATEStatement DELETE Statement
This is the end of this article about shared locks and exclusive locks in MySQL. For more related contents of shared locks and exclusive locks in mysql, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!