Shulou(Shulou.com)06/02 Report--

Mysql in how to achieve shared lock and exclusive lock, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Shared lock (Share Lock)

Shared lock, also known as read lock, or S lock for short: a transaction acquires a shared lock of a data row, and other transactions can acquire a shared lock corresponding to that row, but not an exclusive lock, that is, when a transaction reads a data row, other transactions can read data concurrently, but cannot add or delete the data row until all shared locks have been released.

If transaction T adds a shared lock to data A, other transactions can only add a shared lock to A, not an exclusive lock. Transactions that acquire shared locks can only read data and cannot modify data

Adding LOCK IN SHARE MODE,MySQL after the query statement adds a shared lock to every row in the query result. When no other thread uses an exclusive lock on any row in the query result set, you can successfully apply for a shared lock, otherwise it will be blocked. Other threads can also read tables that use shared locks, and these threads read the same version of data

Exclusive lock (eXclusive Lock)

Exclusive lock, also known as write lock, or X lock for short: if a transaction acquires an exclusive lock of a data row, other transactions can no longer acquire other locks of the row (exclusive lock or shared lock), that is, when a transaction reads a data row, other transactions cannot add, delete, modify or check the data row.

If transaction T adds an exclusive lock to data A, other transactions can no longer impose any type of blockade on A. A transaction that acquires an exclusive lock can both read and modify data

Adding FOR UPDATE,MySQL after the query statement adds an exclusive lock to every row in the query result. When no other thread uses an exclusive lock on any row in the query result set, you can successfully apply for an exclusive lock, otherwise it will be blocked.

Intention lock

Intention locks are table-level locks that are designed to reveal the type of lock that the next row will be requested in a transaction. Two table locks in InnoDB:

Intention shared lock (IS): indicates that the transaction is ready to add a shared lock to a data row, that is, a data row must acquire the IS lock of the table before adding a shared lock. If you need to add a shared lock to record A, then InnoDB will find the table first, and then add a shared lock to record An after adding an intentional shared lock to the table.

Intention exclusive lock (IX): similar to the above, it indicates that the transaction is ready to add an exclusive lock to the data row, that is, the transaction must acquire the IX lock of the table before adding an exclusive lock to the data row. If you need to add an exclusive lock to record A, then InnoDB will first find the table and add an exclusive lock to record An after adding an intentional exclusive lock to the table.

The intention lock is automatically added by InnoDB without user intervention.

Pay attention

For insert, update and delete,InnoDB, exclusive locks are automatically added to the data involved. Only query select requires us to manually set exclusive locks.

For general select statements, InnoDB does not add any locks, that is, multiple concurrent select operations can be carried out without any lock conflicts, because there are no locks at all

How to set a shared lock and an exclusive lock?

Shared lock: SELECT... LOCK IN SHARE MODE

Exclusive lock: SELECT. FOR UPDATE

Summary

Shared lock and exclusive lock, the system will automatically add shared lock or exclusive lock under certain conditions, or you can add shared lock or exclusive lock manually.

Intention sharing lock and intention exclusive lock are automatically added and released by the system, and the whole process does not need human intervention.

Both the shared lock and the exclusive lock are the row records of the lock, and the intended shared lock and the intended exclusive lock the table.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.