Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the concept of lock in MySQL, which may not be well understood by many people. In order to make you understand better, the editor summarized the following content for you. I hope you can get something according to this article.

Optimistic lock

Optimistic locks are mostly implemented based on the data version recording mechanism, which usually adds a "version" field to the database table. When reading the data, read out the version number together, and then add one to the version number when it is updated. At this time, the version data of the submitted data is compared with the current version information recorded in the corresponding database table, and if the submitted data version number is greater than the current version number of the database table, it will be updated, otherwise it is regarded as out-of-date data.

For example, to place an order:

Find out the commodity information.

Select (quantity, version) from t_goodswhere id = # {id}

Generate orders according to product information.

Reduce the quantity of goods by 1.

Update t_goodsset quantity = quantity-1where id = # {id} and version = # {version}

Pessimistic lock

Pessimistic locking depends on the locking mechanism provided by the database. Both shared and exclusive locks in MySQL are pessimistic. Database addition, deletion and modification operations will add exclusive locks by default, while queries will not add any locks.

Shared lock (read lock)

A shared lock refers to sharing the same lock for a resource for multiple different transactions. Add a shared lock to a resource, and you can read the resource yourself, and others can also read the resource (or you can add a shared lock, that is, a shared lock shares multiple memories), but you cannot modify it. If you want to modify it, you must wait until all the shared locks have been released. Syntax: select * from table lock in share mode;.

For example:

Window 1, which adds a shared lock to a piece of data in an unfinished transaction.

BEGIN;SELECT * FROM t_red_packet WHERE id = 1 LOCK IN SHARE MODE

Window 2, add a shared lock to the same data, and the lock is successful.

SELECT * FROM t_red_packet WHERE id = 1 LOCK IN SHARE MODE

Windows 1 and 2, update the row data and prompt [Err] 1205-Lock wait timeout exceeded; try restarting transaction. You need to wait until all shared locks are released before you can perform update operations.

UPDATE t_red_packetSET user_id = 2

Exclusive lock (write lock)

Exclusive locking means that there can be only one lock for the same resource for multiple different transactions. If you add an exclusive lock to a resource, you can add, delete, change and check it, but others can not add, delete or change it. Syntax: select * from table for update.

Window 1, which adds an exclusive lock to a piece of data in an unfinished transaction.

BEGIN;SELECT * FROM t_red_packet WHERE id = 1 FOR UPDATE

Window 1, update the row of data, successful.

UPDATE t_red_packetSET user_id = 2

Window 2, query this line of data, you can query.

SELECT * FROM t_red_packet WHERE id = 1

Window 2 locks the data and prompts [Err] 1205-Lock wait timeout exceeded; try restarting transaction.

SELECT * FROM t_red_packet WHERE id = 1 FOR UPDATE

To sum up, the shared lock is for everyone to read and share the lock together, but no one should modify the locked data. The exclusive lock is that I just want to modify it, and you can read it, but you can't get to the lock. You can't modify the data.

Row lock

A row lock is to lock a row of data.

Watch lock

A table lock is to lock a table.

After reading the above, do you have any further understanding of the concept of lock in MySQL? If you want to know more knowledge or related content, 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.