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

How are pessimistic locks and optimistic locks used in Mysql? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Pessimistic lock

Pessimistic lock, think that the data is pessimistic. Add a lock when we query the data. Prevent other threads from tampering until the other party gets the lock.

For example, there is a table like this. Status=1 indicates that an order can be placed, while status=2 indicates that an order cannot be placed. If two users check status=1 at the same time in the process of concurrency, it is logically possible to add new orders, but it will cause goods to be oversold.

The following example

CREATE TABLE `goods` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (255) DEFAULT NULL, `status` tinyint (4) DEFAULT NULL, `version` int (11) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4INSERT INTO demo.goods (id, name, status, version) VALUES (1, 'test', 1,1)

Session1 execution

Set autocommit=0;begin;select * from goods where id=1 and goods.status=1 for update; update goods set status=2 where id=1

Session2 execution

Begin;select * from goods where id=1 for update

The session2 is blocked at this time, because the lock is still in the session1, so the lock has been waiting. If session1 does not submit all the time, session2 will disconnect after a certain period of time and report

(1205, 'Lock wait timeout exceeded; try restarting transaction') error

The specific lock waiting time can be controlled by setting the innodb_lock_wait_timeout parameter.

If the commit operation is performed in session1 at this time, session2 will get the query result and give the lock to session2.

We can also pass through

Show status like 'innodb_row_lock_%'

To further view the lock information.

Optimistic lock

Optimistic lock is different from pessimistic lock, optimistic lock is implemented through its own program, not mySql itself.

Optimistic lock query is not locked, only when updated to check the version number.

For example, if we query that the version in the goods table is 1, then when we update this table, the Sql will be

Select * from goods where id=1;update goods set status=2,version=version+1 where id=1 and version=1

The version here is the version number of the query, and each change will result in version+1. If the version number does not match, the update will not succeed.

The answers to the questions about how pessimistic locks and optimistic locks are used in Mysql are shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.